asked on

Run SQL select with WHILE

Hey there,

This is a tough one. If I could award 10,000 points I'd do it.

Here goes:
I have a table named 'tblMainHistory'. This table is created by a daily SQL Server Agent job that selects all of the records from 'tblMain' and appends them to 'tblMainHistory' and adds the current date to each record in a column named 'RecordDate'.

The following select will return the location codes and their counts for the date specified in the WHERE clause.

SELECT ISNULL(h.DESCRIPTION, 'UNASSIGNED') AS Description, COUNT(DISTINCT v.RECORDIDNUMBER) RecordCount
FROM  tblMainHistory v, tblLocationCode h
WHERE h.LOCATIONCODE =* v.LOCATION
AND RecordDate='2011-5-3'
GROUP BY ISNULL(h.DESCRIPTION, 'UNASSIGNED')

Open in new window

My goal is to have a script that will select the location code counts from tblMainHistory and dump them into a column along with the date in RecordDate. The result would look like this:

RecordDate | Location 1 | Location 2 | Location 3 | Location 4 |
---------------------------------------------------------------------------
2011-05-03 | 30 | 4 | 19 | 3 |
2011-05-02 | 34 | 5 | 19 | 2 |

etc.

I have NO clue how to do this. Any help will be GREATLY appreciated.

Thanks!

tigin44

if you sql server is above 2005 the you should use the PIVOT operation otherwise you shuld use case statements...

tigin44

here is a pivot sample...

CREATE TABLE tblMainHistory (
RecordDate	DATE,
location	int
)
	
INSERT INTO tblMainHistory VALUES(GETDATE(), 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 4) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 4) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE(), 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 2) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 1) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 3) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 4) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 4) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 4) 	
INSERT INTO tblMainHistory VALUES(GETDATE()-1, 4) 	
	
SELECT RecordDate, [1] AS location1, [2] AS location2, [3] AS location3, [4] AS location4

FROM(	SELECT RecordDate, RecordDate AS R_Date, location
	FROM tblMainHistory 
) AS PVT
PIVOT
(
	COUNT(R_Date) FOR location IN ([1], [2], [3], [4])
) AS P

Open in new window

ttist25

ASKER

Thanks for your response tigin. I really appreciate it.

I looked up PIVOT/UNPIVOT at msdn.microsoft.com but I don't really understand it.

In the insert statements you posted, what are the numbers after GETDATE for? Is there a pattern there?

Anthony Perkins

>>In the insert statements you posted, what are the numbers after GETDATE for? <<
It is called sample data.

Unfortunately, when we do not have any meaingful sample data provided we need to create our own, which means it will not be very meaningful.

If you would like to supply some sample data and the output expected we can then give you constructive solutions.

It would be also helpful if you could provide responses to the questions asked, such as what version you are using, so that we do give you inappropriate answers.

Sharath S

1) What is your SQL version?
2) How many maximum locations can be expected. If this is an unknown value, you need dynamic pivot.
3) Can you post the result of your query?

ttist25

ASKER

Gotcha - thanks.

I'm using SQL Server 2008 Enterprise. Here is an example of what the select returns:

Description      RecordCount
First Floor      4
Second Floor      15
Third Floor      243
Fourth Floor      48
Fifth Floor      60
Sixth Floor      203
Seventh Floor      18
Eighth Floor      502
Ninth Floor      99
Tenth Floor      1
UNASSIGNED      27

and the number of locations are always the same.

Thanks again!

Anthony Perkins

So did you try the solution suggested here http:#a35702482

ASKER CERTIFIED SOLUTION

G Trurab Khan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

Ghunaima! You're my HEROINE (in the mythological sense - not the narcotic sense)!

This worked perfectly. Thanks again to everyone.

ttist25

ASKER

Shoot - I spoke too soon.

I have opened a new question here if you are interested.

Almost there!