June 8, 2006 at 11:21 am
I need to do something like the following where the case statement is in the where clause? Any ideas?
select v.chartID, max(rs1.dateService) as dateService from myTable v,
(select distinct dateService, chartID from myTable where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1
where (v.chartID=rs1.chartID) and (v.procedureKey='90657' or v.procedureKey='90658') and (
(case when @mon=1 then dateDiff(day,v.dateService,@myDate)< 216
when @mon=2 then dateDiff(day,v.dateService,@myDate)<244
when @mon=3 then dateDiff(day,v.dateService,@myDate)<275
when @mon=4 then dateDiff(day,v.dateService,@myDate)<305
when @mon=5 then dateDiff(day,v.dateService,@myDate)<336
when @mon=6 then dateDiff(day,v.dateService,@myDate)<366
when @mon=7 then dateDiff(day,v.dateService,@myDate)<32
when @mon=8 then dateDiff(day,v.dateService,@myDate)<63
when @mon=9 then dateDiff(day,v.dateService,@myDate)<93
when @mon=10 then dateDiff(day,v.dateService,@myDate)<124
when @mon=11 then dateDiff(day,v.dateService,@myDate)<154
when @mon=12 then dateDiff(day,v.dateService,@myDate)<185
end)
group by v.chartID, rs1.procedureKey
June 8, 2006 at 11:27 am
DECLARE
@mycheck int
IF @mon=1
set @mycheck = 216
else if @mon=2
set @mycheck = 244
.....
where
....
dateDiff(day,v.dateService,@myDate) < @mycheck
June 8, 2006 at 11:40 am
Geez, well that would work, wouldn't it? thanks. Trying to make it too hard.
JB
June 8, 2006 at 11:41 am
Went with this, but is there a way to do in where clause?
DECLARE
@mycheck int
IF @mon=1
set @mycheck = 216
else if @mon=2
set @mycheck = 244
.....
where
....
dateDiff(day,v.dateService,@myDate) < @mycheck
June 8, 2006 at 11:57 am
What is this attempting to do ? Perform date comparisons by taking into account a fiscal year end that is different from a calandar year end ?
Wouldn't it be better to have a small 12 row table that provides the mapping of month to day count that you can join to, so that you don't have to code this over & over ?
June 8, 2006 at 12:00 pm
Yep, you understand the scenario. Hmmm, I would have thought that executing the code once a month in a stored procedure was considered 'better' than adding a static lookup table with 12 rows. But, I'm obviously not really an expert. You likeee the table idea best?
Thanks everyone, for the responses. I appreciate the input.
June 8, 2006 at 1:06 pm
So is this cumulative YTD - type data?
if so, I would
- get the relevant Fiscal year start ('@FYS')
-get the end date for the calculation, truncate time portion, add 1 ('@UpperBoundEx')
-run the query with: where ... date >= @FYS and date < @UpperBoundEx.
You should generally try to do any manipulation of scalar values outside the query, where they are guaranteed to be done only once, and where they won't complicate the execution plan. You can also take the opportunity to get them into a form suitable for your query - for example the above date comparison can use an index seek on the date column, where a datediff can't.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply