Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 [Resolved] Sum(Case with if else ....)

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]
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-11-30 : 16:09:51
Purrrfect, worked great. Thank you.
Go to Top of Page

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 statement

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) 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"
Go to Top of Page

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 statement

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) 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.
Go to Top of Page
   

- Advertisement -