Need help with GROUP BY...

Question

Post reply

Need help with GROUP BY...

iamjoe

Valued Member

Points: 64
More actions
November 16, 2024 at 3:14 pm

#4482687

Here's my data...
I am trying to return total days in status for each status. I was suggested to use a #tempTable but I am new to that.

SELECT
[Issue ID]
,[Status Name]
,FORMAT([Status Enter Datetime], 'MM-dd-yyyy') AS 'ENTER Date'
,[No Of Seconds in Current Status] AS 'seconds in state'
,IIF([Status Exit Datetime] is null, GETDATE(), [Status Enter Datetime]) as 'EXIT Date'
,IIF([Status Exit Datetime] is null,DATEDIFF(day,[Status Enter Datetime],GETDATE()),
DATEDIFF(day,[Status Enter Datetime],[Status Exit Datetime])) AS 'DAYS IN STATUS'
INTO #tempTable
FROM my-actual-table
WHERE [Issue ID] = '7928712'

SELECT
[Issue ID]
,[Status Name]
,SUM(CONVERT( int ,'DAYS IN STATUS')) AS 'Total Days in Status'
FROM #tempTable
GROUP BY [Issue ID],[Status Name]
ORDER BY [Issue ID],[Status Name]
The query above was giving an error conversion failure.

Thanks in advance!
-joe

Viewing 2 posts - 1 through 1 (of 1 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

Duplicate post. Don't post here... post at the following link so we can keep any answers in one spot.

https://www.sqlservercentral.com/forums/topic/need-help-with-group-by-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)