Need help with GROUP BY...

• iamjoe

  Newbie

  Points: 4

  More actions

  November 16, 2024 at 3:13 pm

  #4482686

  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