December 4, 2011 at 12:45 am
Hi all
I'm new to SQL (2 weeks) so forgive me if this answer has been posted previously - I have searched.
I'm currently looking for data between 08:00 and 07:59 the next day. I use the following code which works. I've done this for each day.
sum(case when Datename(dd,CaseStarted) = 1 and Datename(hh,CaseStarted) > 7 and [x] like ('%y%') then 1 else 0 end)
+ sum(case when Datename(dd,CaseStarted) = 2 and Datename(hh,CaseStarted) < 8 and [x] like ('%y%') then 1 else 0 end) as 'Day period 1-2',
...
sum(case when Datename(dd,CaseStarted) = 30 and Datename(hh,CaseStarted) > 7 and [x] like ('%y') then 1 else 0 end)
+ sum(case when Datename(dd,CaseStarted) = 31 and Datename(hh,CaseStarted) < 8 and [x] like ('%y') then 1 else 0 end) as 'Day period 30-31',
I've manually changed some of the word's to 'x' and 'y' in the example above
But the problem occurs when i go into the next month, examples including:
30th Sep-1st Oct
31st Oct-1st Nov
28th Feb-1st Mar
29th Feb-1st Mar
Any ideas? I've tried a few things to add 1 to the month but cant seem to get it to work.
Thank you in advance for your help.
Malcolm
December 4, 2011 at 2:39 am
Here's a short example how to use the DATEADD/DATEDIFF approach:
DECLARE @tbl TABLE
(
DateFrom DATETIME
)
INSERT INTO @tbl
VALUES(
'2011-09-30'),
('2011-10-31')
SELECT
DATEADD(hh,8,DATEADD(dd,DATEDIFF(dd,0,DateFrom),0)) AS HourFrom,
DATEADD(hh,32,DATEADD(dd,DATEDIFF(dd,0,DateFrom),0)) AS HourTo
FROM @tbl
In your query, you'd use >= HourFrom AND < HourTo to get the range without the 8:00 value of the next day.
Using your current approach you might end up missing values betwee 7:59 and 8:00 (e.g. 7:59:30).
December 6, 2011 at 11:32 am
Hi Lutz
Thank you very much for your reply.
I'll give it a go.
Kind regards
Malcolm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply