[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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!
0
ttist25
Asked:
ttist25
  • 4
  • 2
  • 2
  • +2
1 Solution
 
tigin44Commented:
if you sql server is above 2005 the you should use the PIVOT operation otherwise you shuld use case statements...
0
 
tigin44Commented:
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

0
 
ttist25Author Commented:
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?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
>>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.
0
 
SharathData EngineerCommented:
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?
0
 
ttist25Author Commented:
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!
0
 
Anthony PerkinsCommented:
So did you try the solution suggested here http:#a35702482
0
 
GhunaimaCommented:
Try this

 
SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1
	, sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2
	, sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3
	, sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4
	, sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5
	, sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6
	, sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7
	, sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8
	, sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9
	, sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10
	, sum(case when h.description IS null then 1 else 0 end) as UnAssigned
FROM  tblMainHistory v, tblLocationCode h
WHERE h.LOCATIONCODE =* v.LOCATION
GROUP BY RecordDate

Open in new window

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

This worked perfectly.  Thanks again to everyone.
0
 
ttist25Author Commented:
Shoot - I spoke too soon.

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

Almost there!  
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now