February 15, 2013 at 7:17 am
CASE
WHEN appointment.effdate <= @enddate-27 and APPOINTMENT.Expdate between @enddate-27 and @enddate-21 THEN DATEADD(day,-27,@enddate)
when APPOINTMENT.EffDate < @enddate -27 and APPOINTMENT.Expdate> @enddate-21 then DATEADD(day,-27,@enddate)
when APPOINTMENT.EffDate between @ENDdate-27 and @enddate-21 then APPOINTMENT.EffDate
end as week1start,
Case
when APPOINTMENT.EffDate<= @enddate-27 And APPOINTMENT.Expdate between @enddate-27 and @enddate-21 Then APPOINTMENT.Expdate
when APPOINTMENT.EffDate < @enddate-27 and APPOINTMENT.Expdate > @enddate-21 then DATEADD(day,-21,@enddate)
when APPOINTMENT.EffDate between @enddate-27 and @enddate-21 and APPOINTMENT.Expdate > @enddate-21 then DATEADD(day,-21,@enddate)
end as week1end,
DATEDIFF(day,week1start,week1end) AS week1dif
February 15, 2013 at 7:21 am
This will work:
DATEDIFF(day, CASE
WHEN appointment.effdate <= @enddate-27 and APPOINTMENT.Expdate between @enddate-27 and @enddate-21 THEN DATEADD(day,-27,@enddate)
when APPOINTMENT.EffDate < @enddate -27 and APPOINTMENT.Expdate> @enddate-21 then DATEADD(day,-27,@enddate)
when APPOINTMENT.EffDate between @ENDdate-27 and @enddate-21 then APPOINTMENT.EffDate
end, Case
when APPOINTMENT.EffDate<= @enddate-27 And APPOINTMENT.Expdate between @enddate-27 and @enddate-21 Then APPOINTMENT.Expdate
when APPOINTMENT.EffDate < @enddate-27 and APPOINTMENT.Expdate > @enddate-21 then DATEADD(day,-21,@enddate)
when APPOINTMENT.EffDate between @enddate-27 and @enddate-21 and APPOINTMENT.Expdate > @enddate-21 then DATEADD(day,-21,@enddate)
end)
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 15, 2013 at 8:38 am
I'd like to make a suggestion. Developers really need to stop using this notation for subtracting days from a date: @datevariable - 2. Really should use the DATEADD function for this as the previous syntax will not work on the DATE and DATETIME2 data types.
Try this on a SQL Server 2008 instance:
declare @TestDate date = '2013-02-15',
@TestDate2 datetime2 = '2013-02-15 8:00:00';
select @TestDate, @TestDate - 21;
GO
declare @TestDate2 datetime2 = '2013-02-15 8:00:00';
select @TestDate2, @TestDate2 - 21;
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply