Change cells based on count


,
I am making a demo database and I have some really dirty data that I need to clean up. In the GEO dim I want to change all the Zipcodes with the count of one lead to reflect a zipcode that I can use in a map dashboard. Currently most counts of one contain --=-== and other completely usless informaiton. I think best way is to create a temp view but I can't figure out the code I should use to change the count (1) to whatever zipcode I need. Any ideas would be much appreciated.
Posted On: Monday 31st of December 2012 01:15:24 AM Total Views:  219
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Track what has been changed to data? SQL 2008

, I have this application that is making changes to my db. I have no idea what it's inserting or updating. I've tried using the profiler but didnt tell me anything. I want to do is take a small snap shot, something short like 30 secs, to see what is going on to my db. I would like to know things like what tables are effected, if it was an insert or update. Seeing the code is nice but not required. Anybody got any tips
VIEWS ON THIS POST

115

Posted on:

Thursday 25th October 2012
View Replies!

change of machine name on which MS SQL is installed

I have a query . There is a intranet application which is a JSP/MS SQL application.Tomcat and MSSQL has been installed on one machine and the other machines are accessing the application from this machine. Now the machine name and IP address needs to be changed . So how will the application be affected .. If any one has an idea that what all changes would be reqd to be done please let me know
VIEWS ON THIS POST

122

Posted on:

Friday 26th October 2012
View Replies!

need to change stored proc. a bit

ok here's the thing : CREATE PROCEDURE Split ( @OrderList varchar(500) ) AS BEGIN DECLARE @OrderID varchar(10),@Pos int SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1) IF REPLACE(@OrderList, ',', '') '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID '' BEGIN INSERT INTO Testowa(ide) VALUES ( CAST (@OrderID AS int ) ) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1) END END RETURN END GO what it basically does is - it takes a string like '34,2,3,5' and takes ',' as a separator to retrieve values that it should insert into a table 'Testowa' what i'd like to have is : take string like '1,32,4,5' and recognize that it should insert values like this : table Testowa ------------- id | num ------------ 1 32 4 5 can someone rearrange this procedure for me so it'd do this
VIEWS ON THIS POST

106

Posted on:

Friday 26th October 2012
View Replies!

sql INSERT help: how to INSERT from a variable?

i have a .html file shown below. I just want to get the value from the variable and insert that to by access database. Can you please tell me how to get the value in the variable 'nam' in dbcheck() method. i feel the problems are in the red lines highlighted below in my code. the html code: test jscript
VIEWS ON THIS POST

179

Posted on:

Wednesday 7th November 2012
View Replies!

DELETE multiple records

I want to delete multiple records using a series of checkboxes and I'm using the following code: myids = Request("checkmessages") Set objRS = objConn.Execute("UPDATE tblMemMessage SET delmess = '1' WHERE messid IN = (" + myids + ") ") When I run the code I get the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [MySQL][ODBC 3.51 Driver][mysqld-5.0.77-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= (30, 26)' at line 1 Is there anyone could tell me what I'm doing wrong
VIEWS ON THIS POST

144

Posted on:

Saturday 10th November 2012
View Replies!

Selecting only date from datetime field

hey all, here's a problem that's had me stumped for hours now. got a table called s_freq with the following fields: startdate as datetime enddate as datetime freq as int id as int (primary key) okay. what i want is a sql query that can select entries from a specified date in the startdate field, e.g. in the ISO date format, like 2008-07-08. problem is i can only make it select the date AND time that is in the startfield, so if i give it just a date, it will say there's no results because the time doesnt match. argh! something like this: Code: IF exists (SELECT * FROM s_freq WHERE SELECT CONVERT(VARCHAR(10),startdate,111) = '2008-07-08' print 'exists' ELSE print 'does not exists' only that obv does not work. any suggestions thx in advance, dave.
VIEWS ON THIS POST

93

Posted on:

Saturday 10th November 2012
View Replies!

Length of Data types??

Hi frends i wanted to know what exactly does the length of a data type in DB means! For example: length of data type "int" is 4. Does that mean that it can have a int of length 4 digits(example: 9999) or does it mean 2^4(2 raise to power 4) = 16\t thanx
VIEWS ON THIS POST

74

Posted on:

Saturday 10th November 2012
View Replies!

SQL Sub query

I would like to have these two queries in a sub query that gives me the result of query 2 divided by the result of query 1 into a percentage. Basically its a query on the number of tickets total divided by the number of tickets closed. Problem is when I run a sub query, since I am using the count function it causes some errors, if I include the "hst_name" field in the 2nd query, it gives the same result as the first one. If anyone can get something like this to work, or know a way with ASP to just calculate the two results outside of SQL, that would work too. SELECT distinct count(hlp_id) AS Count, hst_name, b.emp_name AS rep_name FROM dbo.helpdesk_requests LEFT JOIN dbo.helpdesk_status ON hlp_hst_id = hst_id LEFT JOIN dbo.employees b ON b.emp_id = hlp_representative_emp_id WHERE hst_name = 'Closed' GROUP BY hst_name, b.emp_name ORDER BY b.emp_name SELECT distinct (select count(hlp_id)), b.emp_name AS rep_name FROM dbo.helpdesk_requests LEFT JOIN dbo.helpdesk_status ON hlp_hst_id = hst_id LEFT JOIN dbo.employees b ON b.emp_id = hlp_representative_emp_id GROUP BY b.emp_name ORDER BY b.emp_name
VIEWS ON THIS POST

134

Posted on:

Saturday 10th November 2012
View Replies!

Indexing the char field as integer

I have some character data stored in the database but when i am trying to index its not providing me results as required for example after indexing the data i can see in the order as 1 111 1111 12 2 21 but i need in the following order 1 2 12 21 111 1111 i have used the statement below Code: select col_1 from orderTable order by convert(int, col_1) but i am wondering if i can do the same and create index based on converting the filed into int is it possible , kindly share any example code.
VIEWS ON THIS POST

81

Posted on:

Sunday 11th November 2012
View Replies!

Error converting data type nvarchar to uniqueidentifier

Hi all, when I runn my VB.NET application, I got "Error converting data type nvarchar to uniqueidentifier" The procedure is: CREATE PROCEDURE SessionsUpdate @newID uniqueidentifier AS UPDATE Sessions set time_stamp = getdate() where newID = @newID GO Any one can give me some idea
VIEWS ON THIS POST

166

Posted on:

Sunday 11th November 2012
View Replies!

Schedule Jobs Sql Server 2000 >> 2008

Let me explain my question in steps: My hosting server: I have sql server 2008 installed. My local web server: I have sql server 2000 installed. Q1: Is it possible to setup a Job in Sql 2000 that can be than somehow imported to sql 2008 Q2: Is it possible with sql 2008 to schedule an execution of an ASP or PHP or ASP.net file Actually I want my system to send an automated email to subscriber a day before his account expire. So I need to know how can I do it.
VIEWS ON THIS POST

49

Posted on:

Monday 12th November 2012
View Replies!

Problem with Stored Procedure

Hi , I have a stored procedure which runs fine on my server and the SQL syntax all checks out ok. However when I try to run the SP via an ASP page I get the following error: Code: Microsoft OLE DB Provider for SQL Server error '80040e37' Invalid object name 'cbc_transactions'. upload_transactions_save.asp, line 136 My SP looks like this: Code: CREATE PROCEDURE dbo.MERGE_CBC_TRANS ( @UploadTag varchar(20) ) AS BEGIN Insert Into cbc_transactions ("authorid", "retailerid", "transdate", "transqty", "cash", "confirmed", "transtype", "comment", "dateadded", "uploadtag") SELECT tblAuthor.Author_ID as authorid, cbc_retailers.id as retailerid, dummy_transactions_table.datestr as transdate, dummy_transactions_table.noclick as transqty, cbc_retailers.rewardvalue as cash, 'n' as confirmed, 'Deposit' as transtype, '' as comment, Getdate() as dateaadded, @UploadTag as uploadtag FROM dummy_transactions_table, cbc_retailers, tblAuthor WHERE (cbc_retailers.retailer = dummy_transactions_table.retailer) AND (tblAuthor.Username = dummy_transactions_table.username) delete from dummy_transactions_table END GO And the ASP code I use to run it looks like this: Code: Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") Dim UploadTag UploadTag = Request.QueryString("uploadtag") Dim Connection_String Connection_String = "Provider=SQLOLEDB.1;" & _ "Persist Security Info=False;" & _ "Network Library=DBMSSOCN;" & _ "Data Source=" & serverIP & "," & serverPORT & ";" & _ "Initial Catalog=" & serverDB & ";" & _ "User ID=" & serverLOGIN & ";" & _ "Password=" & serverPASSWORD & ";" & _ "Connect Timeout=5;Pooling=False" objConn.Open Connection_String 'Call the stored procedure to increment a counter on the page objConn.Execute "exec MERGE_CBC_TRANS" & UploadTag & "" Set Connection_string = nothing Set objConn = nothing Line 136 the line that errors is the: objConn.Execute "exec... line I'm pretty certain from the error that it's an SQL problem hence my post being in this forum, but not sure what the problme actually is.
VIEWS ON THIS POST

81

Posted on:

Thursday 15th November 2012
View Replies!

Out Of Range Datetime Value

I need to be able to run a query to select the birthdays that will occurr in the next 7 days. I keep getting an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I get this error after I run the following:- go select birthdate from vfuturecontactaddress where datediff (dd,convert (datetime, '1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111) ,convert (datetime, '1900/'+cast(month(birthdate) as varchar)+'/'+cast (day(birthdate) as varchar),111) ) between 0 and 7 go
VIEWS ON THIS POST

51

Posted on:

Thursday 15th November 2012
View Replies!

Recursive Queries

I'm trying to create a tree of objects that are related to them selves, to keep this simple I shall give the example of a folder view in windows explorer. I imagine a record for each folder would look something like this: FolderID ParentID Name where parentID is the folder Id of the folder immediatly above the current folder. So if I wanted to write a query to represent this as a tree, how would I do this the issue is that each record is related to its self, and thus there are an infinite number or recursions. This is causing me serious problems any light shed would be greatly appreciated. I tried to accomplish this by creating a very complex function in ASP, but am convinced that a sql approach would be much better. To give an example of exactly what I want the table to look like: Code: level 1 Level2 Level 3 FolderID root 1 root folderx 2 root folderx folderY 3 help please, I'm really pulling my hair out with this... reputation will be rewarded for helpful suggestions.
VIEWS ON THIS POST

40

Posted on:

Thursday 15th November 2012
View Replies!

Cannot open database

i tried to open a database on my MS SQL Server 2000, but appears the following message; -Error 946:Cannot open database "MyDatabase" version 515. Upgrade the database to the latest version.- how can i upgrade "MyDatabase" to MS SQL Server 2000
VIEWS ON THIS POST

154

Posted on:

Thursday 15th November 2012
View Replies!

Failed to generate a user instance

Not sure if this is the right part of the site to post this but I hope it is. I'm writing an ASP2 webapp and i'm using integrated security i'm trying to use an application pool with a specific identity and not network services which i default when I do this i get the error Code: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. Source Error: Line 25: Line 26: //Create Roles Line 27: if (!Roles.RoleExists("SuperAdmin")) Line 28: Roles.CreateRole("SuperAdmin"); Line 29: Source File: c:\Inetpub\wwwroot\WSIHarvester\test.aspx.cs Line: 27 Stack Trace: [SqlException (0x80131904): Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlRoleProvider.RoleExists(String roleName) +482 System.Web.Security.Roles.RoleExists(String roleName) +242 test.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\WSIHarvester\test.aspx.cs:27 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
VIEWS ON THIS POST

90

Posted on:

Tuesday 20th November 2012
View Replies!

Oracle guides for exams

hi people, I am very much interested to Oracle. I decided to write one exam soon and I am refering guides from **************. It seems good. Any of you know any good books than this !
VIEWS ON THIS POST

116

Posted on:

Tuesday 20th November 2012
View Replies!

Sloppy query works, but would like to know a better way...

The goal of this code is to examine all transactions earlier than a specified date and find out what visit they came from. If the patient is covered by medicare and the visit does not have a specific transaction code in the group of transaction codes that corellate to that visit, then output the patient's name and the visit date that lacks the correct transaction code. It's sloppy and takes about 20 minutes to run, but I couldn't think of any other way to do it! Code: /* A script to scan transactions for medicare recipients who do not have one of two transaction codes before a specific date. */ declare medicare_cursor scroll cursor for select distinct t.source_id from transactions t inner join visits v on t.source_id = v.visit_id inner join network_people np on t.hin = np.hin inner join network_payers npay on np.hin = npay.hin inner join payer_mstr pm on pm.payer_id = npay.payer_id inner join contract_links cl on cl.payer_id = pm.payer_id inner join contract_mstr cm on cm.contract_id = cl.contract_id where v.visit_date < '2005-01-06' and cm.contract_id = 'MedicareContractNumber' open medicare_cursor declare @tnt int declare @nmt int declare @source_id varchar(30) declare @tnt_result int fetch first from medicare_cursor into @source_id while @@fetch_status = 0 begin --This will be the number of medicare transactions, NMT set @nmt = (select count(t.source_id) from transactions t inner join claims c on t.source_id = c.visit_id where t.tran_code_id in ( 'code1', 'code2') and t.source_id = @source_id) --This will be the total number of transactions, TNT set @tnt = (select count(t.source_id) from transactions t inner join claims c on t.source_id = c.visit_id where t.tran_code_id not in ( 'code1', 'code2') and t.source_id = @source_id) set @tnt_result = @tnt /* If the difference of TNT - NMT = TNT for a given source id, then select the patient information, source_id and the date. If TNT - NMT < TNT, then do not return anything. */ if (@tnt - @nmt = @tnt_result) begin declare @firstname varchar(40) declare @lastname varchar(40) declare @visitdate varchar(40) set @firstname = (select distinct np.first_name from transactions t inner join network_people np on np.hin = t.hin inner join claims c on c.visit_id = t.source_id where t.source_type 'A' and t.source_id = @source_id) set @lastname = (select distinct np.last_name from transactions t inner join network_people np on np.hin = t.hin inner join claims c on c.visit_id = t.source_id where t.source_type 'A' and t.source_id = @source_id) set @visitdate = (select distinct c.visit_date from transactions t inner join network_people np on np.hin = t.hin inner join claims c on c.visit_id = t.source_id where t.source_type 'A' and t.source_id = @source_id) print @firstname + ' ' + @lastname + ' DATE: ' + @visitdate end fetch next from medicare_cursor into @source_id end close medicare_cursor deallocate medicare_cursor
VIEWS ON THIS POST

129

Posted on:

Tuesday 20th November 2012
View Replies!

[Microsoft][SQL Native Client]TCP Provider: Timeout error [258]

hello , I am new in this pls help me to overcome this error..... Seering this error teh past few days and normal site across a lot of different pages. , i am using sql server 2005 [Microsoft][SQL Native Client]TCP Provider: Timeout error [258] error categorey : Microsoft OLE DB Provider for ODBC Drivers
VIEWS ON THIS POST

352

Posted on:

Tuesday 20th November 2012
View Replies!

Capacity Planning

Hi all, I was just wondering how you all go about doing your capacity planning on databases. I'm about to work this out and so far I've been told as a rough guide to do: (total col length x num rows) + (total index col length x num index rows) + 10% contingency. How does this sound And most importantly, how do I size for the system created tables like sysfiles etc, and also views and stored procs Any definitive methods Many many
VIEWS ON THIS POST

53

Posted on:

Tuesday 20th November 2012
View Replies!

How to crack SQL, .Net exams with ranksheet.com

Friends, Let me know if you have a question pattern for the Free Online Exam and Certification for Sql / .Net in ranksheet.com
VIEWS ON THIS POST

132

Posted on:

Tuesday 20th November 2012
View Replies!

Creating a view called vw_ProductAverage

I have to create a view that displays the average quantity used per invoice for each product. and substitute NULL values with zeros. Please I need help and will appreciate any! I have a MATERIALDETAIL table with columns invoiceID, productCode and Quantity. THIS IS WHAT I HAVE SOFAR: ************************* create view vw_ProductAverage as select distinct product_code from materialdetail go select * from vw_productAverage go *************************** PLEASE help.
VIEWS ON THIS POST

137

Posted on:

Monday 26th November 2012
View Replies!