February 2, 2010 at 10:08 am
Hi,
I have the following scenario:
select purchid,
(select
case vctmovementmonth
when 'dec-09' then '2009-12-02 00:00:00:000'
when 'jan-10' then '2010-01-02 00:00:00:000'
else '2010-02-02 00:00:00:000'
end as [MovementMonth])
from purchline
where vctmovementmonth = 'jan-10'
and dataareaid = 'gal'
and MovementMonth > getdate()
The problem is that I can't use the column "MovementMonth" as it is not recognized at this point, I can't use the column vctmovementmonth due to the case statement. On top of this I need the Text field in vctmovementmonth (dec-09 etc) to convert to a date field, I suspect what I have at the moment will be text.
Hope this is not too much to ask.
Cheers
Jason
February 2, 2010 at 10:34 am
The where vctmovementmonth = jan-10 is limiting what is coming back, so you won't be able to compare the MovementMonth calc the way you have it. Try using an 'or' condition like below. Just note that the case condition on the where clause will cause table scans instead of using indexes
declare @vt_results table(PurchID int, vctMovementMonth varchar(30))
insert into @vt_results values (1, 'Dec-09')
insert into @vt_results values (2, 'Dec-09')
insert into @vt_results values (2, 'Jan-10')
insert into @vt_results values (3, 'Jan-10')
insert into @vt_results values (4, 'Jan-10')
insert into @vt_results values (5, 'Nov-09')
select Purchid,
case vctMovementMonth
when 'dec-09' then '2009-12-02 00:00:00:000'
when 'jan-10' then '2010-01-02 00:00:00:000'
else '2010-03-02 00:00:00:000'
end as [MovementMonth]
from @vt_results
where (vctMovementMonth = 'jan-10')
or (case vctMovementMonth
when 'dec-09' then '2009-12-02 00:00:00:000'
when 'jan-10' then '2010-01-02 00:00:00:000'
else '2010-03-02 00:00:00:000'
end > getdate())
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 2, 2010 at 11:01 pm
You might also be able to solve your problem using a Common Table Expression (cte) something like this
;with cte as
(
select purchid, vctmovementmonth, dataareaid, -- and any other columns you want
case vctmovementmonth
when 'dec-09' then '2009-12-02 00:00:00:000'
when 'jan-10' then '2010-01-02 00:00:00:000'
else '2010-02-02 00:00:00:000'
end as [MovementMonth])
from purchline
)
select purchid -- and any other columns from the cte
from cte
where vctmovementmonth = 'jan-10'
and dataareaid = 'gal'
and MovementMonth > getdate()
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply