[Resolved] Using case with cast and sum

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] Using case with cast and sum

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-04 : 13:51:22

Have following, not sure about the syntax:

case when i.mode = 'J' then cast(sum(i.gross) as decimal(15,2)) else 0 end) as Gross,
case when i.mode <> 'J' then cast(sum(i.gross * 42) as decimal(15,2)) else 0 end) as Gross,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-04 : 13:54:42

SUM(CASE WHEN i.mode = 'J' THEN convert(decimal(15,2),i.gross) ELSE 0 END)

Jim

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-04 : 14:02:43

Jim,

I have 2 lines, will there be an else and an end?

insert into #AC2_ReceiptTable(ac2_gross_qty
                              )

select     SUM(CASE WHEN i.mode = 'J' THEN convert(decimal(15,2),i.gross) ELSE 0 END)
           SUM(CASE WHEN i.mode <> 'J' THEN convert(decimal(15,2),i.gross * 42) ELSE 0 END)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-04 : 14:05:49

[code]

SELECT
SUM(CASE WHEN i.mode = 'J' THEN convert(decimal(15,2),i.gross) ELSE 0 END)
,SUM(CASE WHEN i.mode <> 'J' THEN convert(decimal(15,2),i.gross*42) ELSE 0 END)

[/code]

OR

SELECT
SUM(CASE
WHEN i.mode = 'J'
THEN convert(decimal(15,2),i.gross)
ELSE i.gross * 42
END
) as Gross

[code]

[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-04 : 14:07:12

oops,bad copy and pasting!


SELECT
SUM(CASE 
WHEN i.mode = 'J' 
THEN convert(decimal(15,2),i.gross) 
ELSE convert(decimal(15,2),i.gross) * 42 
END
) as Gross

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-04 : 15:05:10

Jim, thank you, worked fine...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 15:27:21

And as learned in school, you do the truncation of data in the last stage...

SELECT	CONVERT(DECIMAL(15, 2), SUM(i.Gross * CASE WHEN i.Mode = 'J' THEN 1.0 ELSE 42.0 END)) AS Gross

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 15:31:22

[code]DECLARE @Sample TABLE
(
Gross DECIMAL(10, 5),
Mode CHAR(1)
)

INSERT @Sample
SELECT 0.009, 'N' UNION ALL
SELECT 0.008, 'J' UNION ALL
SELECT 0.007, 'J' UNION ALL
SELECT 0.006, 'J' UNION ALL
SELECT 0.005, 'N' UNION ALL
SELECT 0.004, 'N'

-- Snufse
SELECT SUM(CASE WHEN Mode = 'J' THEN CONVERT(DECIMAL(15, 2), Gross) ELSE CONVERT(DECIMAL(15, 2), 42 * Gross) END) AS Gross
FROM @Sample

-- Peso
SELECT CONVERT(DECIMAL(15, 2), SUM(Gross * CASE WHEN Mode = 'J' THEN 1.0 ELSE 42.0 END)) AS Gross
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-04 : 21:53:19

quote:
Originally posted by Peso

And as learned in school, you do the truncation of data in the last stage...

school never teach so deep about sql...

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources