SUM Case When (decimal's!)

Question

Post reply

SUM Case When (decimal's!)

dramaqueen

SSCommitted

Points: 1569
More actions
October 27, 2015 at 10:16 am

#322094

Hi,
Can someone please explain why this is happening.
I am using a select query to do the following;
SUM(CASE WHEN [Measure] = 'XXX' THEN 1 ELSE 0 END) AS 'SEN-04'
The numbers are then inserted into a temp table and looking at the temp table it is only showing whole numbers. I then insert the data into an audit table and it represents the numbers as 0.00.
This has been doing my head in all day. I have tried Cast() and Convert() with no luck.
Thanks in advance for any help.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

JohnG69 Hall of Fame Points: 3114 More actions · Answer 1

what is the field definition of the audit table set as?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

dramaqueen (10/27/2015)
Hi,
Can someone please explain why this is happening.
I am using a select query to do the following;
SUM(CASE WHEN [Measure] = 'XXX' THEN 1 ELSE 0 END) AS 'SEN-04'
The numbers are then inserted into a temp table and looking at the temp table it is only showing whole numbers. I then insert the data into an audit table and it represents the numbers as 0.00.
This has been doing my head in all day. I have tried Cast() and Convert() with no luck.
Thanks in advance for any help.

What is the data type of 'SEN-04' in the audit table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dramaqueen SSCommitted Points: 1569 More actions · Answer 3

Here is the design view of the audit table. I am putting the data into the value field.

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 4

dramaqueen (10/27/2015)
Hi,
Can someone please explain why this is happening.
I am using a select query to do the following;
SUM(CASE WHEN [Measure] = 'XXX' THEN 1 ELSE 0 END) AS 'SEN-04'
The numbers are then inserted into a temp table and looking at the temp table it is only showing whole numbers. I then insert the data into an audit table and it represents the numbers as 0.00.
This has been doing my head in all day. I have tried Cast() and Convert() with no luck.
Thanks in advance for any help.

What is the schema for the temp table?

Don Simpson

I'm not sure about Heisenberg.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

dramaqueen (10/27/2015)
Here is the design view of the audit table. I am putting the data into the value field.

Can you post the code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 6

*** removed double post.

Don Simpson

I'm not sure about Heisenberg.

dramaqueen SSCommitted Points: 1569 More actions · Answer 7

I have stripped some of the code out that is irrelevant but here is what its doing.

IF OBJECT_ID('tempdb.dbo.##TempTable1', 'U') IS NOT NULL DROP TABLE ##TempTable1

SELECT

SUM(CASE WHEN [Measure] = 'XXX' THEN 1 ELSE 0 END) AS 'SEN-04'

INTO ##TempTable1

FROM [SERVER].[DB].[dbo].

INSERT INTO [SERVER].[DB].[dbo].

SELECT , [MONTH], [SEN-04] AS [Value], 'SEN-04' AS [Measure], GETDATE() AS [Inserted] FROM ##TempTable1

dramaqueen SSCommitted Points: 1569 More actions · Answer 8

I have worked it out! It was something to do with a later statement being CAST().

Thanks everyone.