Author |
Topic |
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2010-11-30 : 13:36:39
|
Have following:sum(case when convert(varchar(10), i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112) then cast(i.gross as decimal(15, 2)) else 0 end) as qty, Need to change so if the date / from condition is met I need to test on:sum(case when convert(varchar(10), i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112) then if i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2),i.gross) else convert(decimal(15,2),i.gross) * 42 END) as qty, I have a hard time getting the syntax right. Thank you. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2010-11-30 : 13:46:25
|
[code]then CASE WHEN i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2),i.gross) else convert(decimal(15,2),i.gross) * 42 END END) as qty[/code] |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2010-11-30 : 16:09:51
|
Purrrfect, worked great. Thank you. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-12-01 : 01:10:48
|
Instead of using nested case statements, just put an AND to the statementsum( case when convert(varchar(10), i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112) and i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2), i.gross) else convert(decimal(15,2),i.gross) * 42 END) as qty, N 56°04'39.26"E 12°55'05.63" |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2010-12-01 : 07:30:20
|
quote: Originally posted by Peso Instead of using nested case statements, just put an AND to the statementsum( case when convert(varchar(10), i.date, 112) >= convert(varchar(10), @DateFromCurrMonth, 112) and convert(varchar(10), i.date, 112) < convert(varchar(10), @DateToCurrMonth, 112) and i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2), i.gross) else convert(decimal(15,2),i.gross) * 42 END) as qty, N 56°04'39.26"E 12°55'05.63"
That would change the logic; may be that is what the OP really wants to accomplish. But the two queries are not equivalent. |
|
|
|
|
|