Pivot or Case Statement
-
I am trying to produce a data set that will display me all dates from today going forward by my location and specialty
This below is my case statement to do this but it will always return as 0
Where have I gone wrong or should I use a PIVOT to do this?
SELECT Location
,Specialty
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,0,SessionSlotDate) AND SessionStatus = 'Appointment Booked' THEN 1 ELSE 0 END) 'Booked'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,0,SessionSlotDate) AND SessionStatus = 'Open' THEN 1 ELSE 0 END) 'Open'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,1,SessionSlotDate) AND SessionStatus = 'Appointment Booked' THEN 1 ELSE 0 END) 'Booked'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,1,SessionSlotDate) AND SessionStatus = 'Open' THEN 1 ELSE 0 END) 'Open'
FROM ##ReportData
WHERE Location = 'OnSite'
AND Groupings = '1stAppts'
GROUP BY Location, Specialty
ORDER BY Specialty
-
Without sample data it's hard to tell, but also
SessionSlotDate = DATEADD(dd,1,SessionSlotDate)
is always false - typo?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (8/11/2011)
Without sample data it's hard to tell, but alsoSessionSlotDate = DATEADD(dd,1,SessionSlotDate)
is always false - typo?
I've attached sample data
-
Could you please provide data insert script not an extract as it's useless.
You can follow the link at the bottom of my signature to find out how the question should be asked in this forum in order to attract helpfull and prompt response.
-
jez.lisle (8/11/2011)
Mark-101232 (8/11/2011)
Without sample data it's hard to tell, but alsoSessionSlotDate = DATEADD(dd,1,SessionSlotDate)
is always false - typo?
I've attached sample data
Table scripts
CREATE TABLE TestData (
SessionID numeric(10, 0) NOT NULL
,SessionSlotDate datetime NULL
,SessionSlotMonth varchar(10) NULL
,SessionSlotTime varchar(10) NULL
,SessionDuration int NULL
,SessionStatus varchar(25) NULL
,Location varchar(25) NULL
,Groupings varchar(25) NULL
) ON [PRIMARY]
GO
revised query
SELECT Location
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,0,SessionSlotDate) AND SessionStatus = 'Appointment Booked' THEN 1 ELSE 0 END) 'Booked'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,0,SessionSlotDate) AND SessionStatus = 'Open' THEN 1 ELSE 0 END) 'Open'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,1,SessionSlotDate) AND SessionStatus = 'Appointment Booked' THEN 1 ELSE 0 END) 'Booked'
,SUM(CASE WHEN SessionSlotDate = DATEADD(dd,1,SessionSlotDate) AND SessionStatus = 'Open' THEN 1 ELSE 0 END) 'Open'
FROM TestData
WHERE Location = 'OnSite'
AND Groupings = '1stAppts'
GROUP BY Location
-
Your question does look better now and your query looks fine to me.
If you post insert script for sample data you have we can see what it will return.
Meanwhile if I insert the following into your table:
insert TestData select 1, getdate(), null,null,null,'Appointment Booked', 'OnSite', '1stAppts'
insert TestData select 2, getdate(), null,null,null,'Appointment Booked', 'OnSite', '1stAppts'
insert TestData select 3, getdate(), null,null,null,'Open', 'OnSite', '1stAppts'
your query returns:
LocationBookedOpenBookedOpen
OnSite2100
You can see non-zero values in first two columns, the last tow always going to be zero as
SessionSlotDate = DATEADD(dd,1,SessionSlotDate) will always evaluate to false (it was already mentioned in previous replies)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply