December 19, 2011 at 4:49 am
Hi,
I have recently moved over to SQL from Oracle. I had a case statement which basically updated a column called weekend_incl. The criteria was as follows: If IncidentDate falls between Friday 19:00 to Sunday 19:00 then 'Y' in weekend_incl column else 'N'.
What I basically require now is this same result in a SELECT statement and NOT an Update:
This is my oracle code(not sure if it will help)
Update Table_1
SET Weekend_incl =
(case when IncidentDate between trunc(IncidentDate,'IW') + 4 + 19/24
and trunc(fwf.IncidentDate,'IW') + 6 + 19/24
then 'Y' else 'N' end)
Thanks very much.
December 19, 2011 at 6:05 am
I think I arrived at a solution:
Select Case WHEN convert(varchar(20),GETDATE(),108) BETWEEN '19:00:00' and '23:59:59'
and DATENAME(dw,GETDATE()) IN ('Friday')
OR convert(varchar(20),GETDATE(),108) BETWEEN '00:00:00' and '19:00:00'
and DATENAME(dw,GETDATE()) IN ('Sunday')
OR DATENAME(dw,GETDATE()) IN ('Saturday') THEN 'Y' ELSE 'N' END
Any feedback would be appreciated, thanks
December 19, 2011 at 6:24 am
It works, but the day of week and time calculations don't have to be run multiple times. Apply is handy for this sort of thing:
SELECT
d.aRow,
d.aDate,
x.[Dayname],
x.TheTime,
Weekend = CASE
WHEN x.[Dayname] = 'Friday' AND x.TheTime >= '19:00:00.0000000' THEN 'Y'
WHEN x.[Dayname] = 'Saturday' THEN 'Y'
WHEN x.[Dayname] = 'Sunday' AND x.TheTime <= '19:00:00.0000000' THEN 'Y'
ELSE 'N' END
FROM (
SELECT aRow = 1, aDate = GETDATE() UNION ALL
SELECT aRow = 2, aDate = GETDATE()-0.75 UNION ALL
SELECT aRow = 3, aDate = GETDATE()-1 UNION ALL
SELECT aRow = 4, aDate = GETDATE()-2 UNION ALL
SELECT aRow = 5, aDate = GETDATE()-2.75 UNION ALL
SELECT aRow = 6, aDate = GETDATE()-3 UNION ALL
SELECT aRow = 7, aDate = GETDATE()-4
) d -- Sample data
CROSS APPLY (SELECT [Dayname] = DATENAME(dw,d.aDate), TheTime = CAST(d.aDate AS TIME)) x
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 1:16 pm
declare @Incidents table(IncidentDate datetime)
insert into @Incidents
select '20111216 18:59:00' union all
select '20111216 19:00:00' union all
select '20111218 19:00:00' union all
select '20111218 19:01:00'
SELECT IncidentDate
,case when IncidentDate between dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105'))
and dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105')) +2 then 'Y' else 'N' end Weekend_incl
from @Incidents
December 20, 2011 at 12:12 am
@ ChrisM@home: Thanks for your solution. I'm not that advanced and was wondering if you could please explain the CROSS APPLY in your code and the benefits?
@ VIG: Your solution is also efficient but I'm quite lost with your code. Would it be possible to please explain it?
Sorry if the questions appear trivial but I'm keen to learn and improve my own skills.
Thanks
December 20, 2011 at 1:05 am
2 mic.con87
Sure
expression DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105') calculates latest Friday before or on a given reference date
The rest is clear
dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105')) - Friday, 19:00
dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105')) +2 -Sunday,19:00
December 20, 2011 at 1:34 am
APPLY is explained in Paul White's papers - links are in my sig. It's somewhat similar to putting a correlated subquery in the FROM list. Read the papers - APPLY is tremendously useful.
VIG's solution is far more efficient than mine, possibly more efficient when used with APPLY so the calculation is performed only once:
declare @Incidents table(IncidentDate datetime)
insert into @Incidents
select '20111216 18:59:00' union all
select '20111216 19:00:00' union all
select '20111218 19:00:00' union all
select '20111218 19:01:00'
SELECT IncidentDate,
case when IncidentDate between x.WeekendStart and x.WeekendStart +2 then 'Y' else 'N' end Weekend_incl
from @Incidents
CROSS APPLY (
SELECT WeekendStart = dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105'))
) x
VIG's code works like this:
declare @Incidents table(IncidentDate datetime)
insert into @Incidents
select '20111214 00:00:00' union all
select '20111215 00:00:00' union all
select '20111216 18:59:00' union all
select '20111216 19:00:00' union all
select '20111218 19:00:00' union all
select '20111218 19:01:00' union all
select '20111219 00:00:00'
SELECT IncidentDate,
Weekend_incl= case when IncidentDate between x.WeekendStart and x.WeekendStart +2 then 'Y' else 'N' end,
DaysSince19000105= DATEDIFF(day, '19000105', IncidentDate),
WeeksSince19000105= DATEDIFF(day, '19000105', IncidentDate)/7.0, -- fridays have no fractional part
WholeWeeksSince19000105 = DATEDIFF(day, '19000105', IncidentDate)/7, -- fractional part removed leaving 'friday'
WholeWeeksAsDays= DATEDIFF(day, '19000105', IncidentDate)/7*7,
FridayAsDate= DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105')
from @Incidents
CROSS APPLY (
SELECT WeekendStart = dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105'))
) x
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 20, 2011 at 6:22 am
Thank-you very much for the explanations. I'll have a look at that paper, sounds pretty useful!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply