December 6, 2011 at 11:33 am
I am not sure what happened to me, this query seems to be simple but what I wrote simply doesn't work:
select i.SysID, i.Code, d.Title, d.NextReviewDate
from infosys i
inner join [document] d on d.sysid = i.sysid
inner join OrgUnits org on org.OrgUnitID = i.OrgUnitID
where org.Business_Line = @BL and
case when @Due = 1 then NextReviewDate < getDate()
else when @Due = 2 then (NextReviewDate < getDate() + 30 AND NextReviewDate >= getDate())
else when @Due = 3 then (NextReviewDate < getDate() + 60 AND NextReviewDate >= getDate() + 30)
else when @Due = 4 then (NextReviewDate < getDate() + 90 AND NextReviewDate >= getDate() + 60)
else when @Due = 5 then (NextReviewDate >= getDate() + 90)
end
Error:
Incorrect syntax near '<'. (that's the first case line):w00t::w00t:
December 6, 2011 at 11:45 am
Ninja's_RGR'us (12/6/2011)
Declare @Begindate & @EndDateDo your cases first to set the range then use between in the select.
If you insist on the where (making it non sargable)
AND 1 = CASE WHEN @a =1 AND DateCol < Whatever THEN 1
WHEN @a = 2 AND... THEN 1
ELSE 0 END
OK, in order to make it not "non sargable", define a range would be better in terms of perf.
But wouldn't that be the initial question?
declare @begindate, @enddate
where org.Business_Line = @BL and
case when @Due = 1 then NextReviewDate between (@begindate1, @enddate1)
else when @Due = 2 then NextReviewDate between (@begindate2, @enddate2)
.... end
December 6, 2011 at 11:49 am
BTW DATEADD(M, 1, Date) <> DATEADD(D, 30, Date)
December 6, 2011 at 11:59 am
Do you think this is correct?
where org.Business_Line = @BL and
1 = CASE WHEN @Due = 1 AND NextReviewDate between @start1 and @end1 THEN 1
WHEN @Due = 2 AND NextReviewDate between @start2 and @end2 THEN 1
WHEN @Due = 3 AND NextReviewDate between @start3 and @end3 THEN 1
WHEN @Due = 4 AND NextReviewDate between @start4 and @end4 THEN 1
WHEN @Due = 5 AND NextReviewDate between @start5 and @end5 THEN 1
ELSE 0 END
Thank you.
December 6, 2011 at 12:02 pm
No, use a single @start and @end and use then only once in the where.
Time for coffee :hehe:.
December 6, 2011 at 12:16 pm
Ninja's_RGR'us (12/6/2011)
No, use a single @start and @end and use then only once in the where.Time for coffee :hehe:.
Can't deliver coffee to Montreal from Toronto, TH does not provide that service 😛
I don't understand your code, can you write it for me pls?
Thanks
December 6, 2011 at 3:10 pm
Actually, I came up with this:
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
((@Due = 1 and d.NextReviewDate < getdate()) or
(@Due = 2 and d.NextReviewDate >= getdate() and d.NextReviewDate < dateadd(dd, 30, getdate())) or
(@Due = 3 and d.NextReviewDate >= dateadd(dd, 30, getdate()) and d.NextReviewDate < dateadd(dd, 60, getdate())) or
(@Due = 4 and d.NextReviewDate >= dateadd(dd, 60, getdate()) and d.NextReviewDate < dateadd(dd, 90, getdate())) or
(@Due = 5 and d.NextReviewDate >= dateadd(dd, 90, getdate())));
December 6, 2011 at 3:14 pm
halifaxdal (12/6/2011)
Ninja's_RGR'us (12/6/2011)
No, use a single @start and @end and use then only once in the where.Time for coffee :hehe:.
Can't deliver coffee to Montreal from Toronto, TH does not provide that service 😛
I don't understand your code, can you write it for me pls?
Thanks
I meant for you to take a little break... you would have figured this out in no time.
Check out Lynn's post. It's the correct syntax for the where clause but it garantees a table (or index) scan. The single between with preset values would have allowed for a seek.
December 6, 2011 at 3:29 pm
Ninja - You'll have to show me what you were trying to accomplish, just not clicking today.
Here is another way to write my query, just requires more typing:
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 1 and d.NextReviewDate < getdate()
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 2 and
d.NextReviewDate >= getdate() and
d.NextReviewDate < dateadd(dd, 30, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 3 and
d.NextReviewDate >= dateadd(dd, 30, getdate()) and
d.NextReviewDate < dateadd(dd, 60, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 4 and
d.NextReviewDate >= dateadd(dd, 60, getdate()) and
d.NextReviewDate < dateadd(dd, 90, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 5 and
d.NextReviewDate >= dateadd(dd, 90, getdate());
December 6, 2011 at 5:58 pm
Really???
--This assumes you want to ignore the time part
--Adjust as you see fit
DECLARE @Today DATETIME
SET @Today = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
SELECT @BeginDate = BD, @EndDate = ED FROM
(
SELECT '1753-01-01' AS BD, @Today AS ED
WHERE @Due = 1
UNION ALL
--Note that I use +1 month rather than 30 days
SELECT @Today AS BD, DATEADD(M, 1, @Today) AS ED
WHERE @Due = 2
...
)
This needs to tweak the pairs correctly, then use between those 2 dates in the final query.
More advanced stuff, you might want to call different proc depending on the date range. At some point, 1 day to 2 months of data will likely need 2 different access paths.
December 7, 2011 at 9:23 am
Lynn Pettis (12/6/2011)
Ninja - You'll have to show me what you were trying to accomplish, just not clicking today.Here is another way to write my query, just requires more typing:
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 1 and d.NextReviewDate < getdate()
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 2 and
d.NextReviewDate >= getdate() and
d.NextReviewDate < dateadd(dd, 30, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 3 and
d.NextReviewDate >= dateadd(dd, 30, getdate()) and
d.NextReviewDate < dateadd(dd, 60, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 4 and
d.NextReviewDate >= dateadd(dd, 60, getdate()) and
d.NextReviewDate < dateadd(dd, 90, getdate())
union all
select
i.SysID,
i.Code,
d.Title,
d.NextReviewDate
from
infosys i
inner join [document] d
on d.sysid = i.sysid
inner join OrgUnits org
on org.OrgUnitID = i.OrgUnitID
where
org.Business_Line = @BL and
@Due = 5 and
d.NextReviewDate >= dateadd(dd, 90, getdate());
That's a long query, initially I thought about it but gave up because I was thinking using case in where clause might be able to handle the task.
Thank you.
December 7, 2011 at 9:26 am
Lynn Pettis (12/6/2011)
Ninja - You'll have to show me what you were trying to accomplish, just not clicking today.
Please don't be tough to Ninja, maybe it was a tough day for him. I always appreciate his help in the past, if Tim Horton can deliver order by taking online order, and if I know you guys' address, I would have made lots of cappuccino orders :-):-):-)
December 7, 2011 at 9:33 am
halifaxdal (12/7/2011)
Lynn Pettis (12/6/2011)
Ninja - You'll have to show me what you were trying to accomplish, just not clicking today.Please don't be tough to Ninja, maybe it was a tough day for him. I always appreciate his help in the past, if Tim Horton can deliver order by taking online order, and if I know you guys' address, I would have made lots of cappuccino orders :-):-):-)
Nothing tough on me here. I know exactly what I have in mind and how it works well in prod :-D.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply