CODEWITHSUNDEEP
sqlsql-servert-sql
Welldonebacon
Welldonebacon

Reputation: 83

Not grouping by time interval SQL Server

I'm trying to group the results by time intervals (hourly) I cannot for the life of me figure this out. I need the experts to help me, can anyone see where I'm going wrong?

DECLARE @IntervalMinutes AS INT 
SELECT  @IntervalMinutes = 60 

SELECT    
        CONVERT(VARCHAR(5),DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',dbo.cmp_EEAddLines.CallTime) / @IntervalMinutes) * @IntervalMinutes, '19000101'),108) AS CallTime, 
        COUNT(dbo.Dial.HistoryID) AS TotalDials,
        SUM(CONVERT(numeric, dbo.CRCTotal.Contact)) AS Contacts, 
        SUM(CONVERT(numeric, dbo.CRCTotal.FinalCRC)) AS Completes
FROM        
        dbo.cmp_EEAddLines LEFT OUTER JOIN
        dbo.Dial ON 
        dbo.cmp_EEAddLines.DialID = dbo.Dial.DialID LEFT OUTER JOIN
        dbo.CRCTotal ON 
        dbo.Dial.CRC = dbo.CRCTotal.CRC
WHERE  
        (dbo.cmp_EEAddLines.CallDate >= 29/03/2015)
GROUP BY
        CallTime

Upvotes: 0

Views: 55

Answers (3)

Stephan
Stephan

Reputation: 6018

Everyone seems to be breaking up each datetime into each part when in reality, you only need two parts. The date and the hour. Try this:

SELECT  CAST(dbo.cmp_EEAddLines.CallTime AS DATE) as dates,
        DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime) AS hr
...

GROUP BY    CAST(dbo.cmp_EEAddLines.CallTime AS DATE),
            DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime)

If you would like it in one column, you can try this instead. It will round each datetime value to it's nearest hour.

SELECT CAST(CONCAT(CAST(dbo.cmp_EEAddLines.CallTime AS DATE),' ',DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime),':00:00.000') AS DATETIME)

....

GROUP BY CAST(CONCAT(CAST(dbo.cmp_EEAddLines.CallTime AS DATE),' ',DATEPART(HOUR,dbo.cmp_EEAddLines.CallTime),':00:00.000') AS DATETIME)

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40289

Here's one way to produce a list of "hits" by year/month/day/hour.

  • There will only be rows present for hours that had activity--that is, there will be gaps.
  • Based on those outer joins, you could have TotalDials without any Contacts or Completes
  • The resulting list does not contain a single datetime value. If required, that could be added by making this a subquery and "stitching together" the date components back into a datetime.

The code:

SELECT 
   datepart(yy, al.CallTime)  Year
  ,datepart(mm, al.CallTime)  Month
  ,datepart(dd, al.CallTime)  Day
  ,datepart(hh, al.CallTime)  Hour
  ,COUNT(di.HistoryID)                TotalDials
  ,SUM(CONVERT(numeric, cr.Contact))  Contacts
  ,SUM(CONVERT(numeric, cr.FinalCRC)) Completes
FROM        
    dbo.cmp_EEAddLines  al
     INNER JOIN dbo.Dial di  --  Left join to inner; counts and sums are identical, query will be faster
      ON al.DialID = di.DialID
     LEFT OUTER JOIN dbo.CRCTotal cr
      ON di.CRC = cr.CRC
 group by
   datepart(yy, al.CallTime)  --  Year
  ,datepart(mm, al.CallTime)  --  Month
  ,datepart(dd, al.CallTime)  --  Day
  ,datepart(hh, al.CallTime)  --  Hour
 order by
   datepart(yy, al.CallTime)  --  Year
  ,datepart(mm, al.CallTime)  --  Month
  ,datepart(dd, al.CallTime)  --  Day
  ,datepart(hh, al.CallTime)  --  Hour

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You could break out each of the date parts you want:

select year(e.cmp_EEAddLines.CallTime), month(e.cmp_EEAddLines.CallTime),
       day(e.cmp_EEAddLines.CallTime),
       datepart(hour, cmp_EEAddLines.CallTime),
       . . .
group by year(e.cmp_EEAddLines.CallTime), month(e.cmp_EEAddLines.CallTime),
         day(e.cmp_EEAddLines.CallTime),
         datepart(hour, cmp_EEAddLines.CallTime)

This doesn't give the final output as a datetime, but it might work for your needs.

Note: e is an alias for the table with the column.

Upvotes: 2

Related Questions