CODEWITHSUNDEEP
sqlsql-server
PookPook
PookPook

Reputation: 467

SQL query hourly for each day

I have a question that seems to be quite complex. I needed to know what happens in a session that day, at a certain time.

Briefly I have a table that shows me all sessions of a given area. These sessions have a start date and a start time and an end time.

You can see in this table:

idArea | idSession |  startDate  |    startTime        |    endTime  
   1   |    1      |  2013-01-01 | 1900-01-01 09:00:00 | 1900-01-01 12:00:00
   1   |    2      |  2013-01-01 | 1900-01-01 14:00:00 | 1900-01-01 15:00:00
   1   |    3      |  2013-01-04 | 1900-01-01 09:00:00 | 1900-01-01 13:00:00
   1   |    4      |  2013-01-07 | 1900-01-01 10:00:00 | 1900-01-01 12:00:00
   1   |    5      |  2013-01-07 | 1900-01-01 13:00:00 | 1900-01-01 18:00:00
   1   |    6      |  2013-01-08 | 1900-01-01 10:00:00 | 1900-01-01 12:00:00

Then I also have a table that shows me all hours interspersed, ie every half hour (I created this table on purpose for this requirement, if someone has a better idea, I can say that I will try to adapt).

idHour |   Hour
   1   |   1900-01-01 00:00:00
   2   |   1900-01-01 00:30:00
   3   |   1900-01-01 01:00:00
  ............................
   4   |   1900-01-01 09:00:00
   5   |   1900-01-01 09:30:00
   6   |   1900-01-01 10:00:00
   7   |   1900-01-01 10:30:00
  ............................

In the end that's what I want to present was this:

  startDate  |    startTime        |    SessionID
  2013-01-01 | 1900-01-01 09:00:00 |        1
  2013-01-01 | 1900-01-01 09:30:00 |        1
  2013-01-01 | 1900-01-01 10:00:00 |        1
  2013-01-01 | 1900-01-01 10:30:00 |        1
  2013-01-01 | 1900-01-01 11:00:00 |        1
  2013-01-01 | 1900-01-01 11:30:00 |        1
  2013-01-01 | 1900-01-01 11:30:00 |        1
  2013-01-01 | 1900-01-01 14:00:00 |        1
  2013-01-01 | 1900-01-01 14:30:00 |        1
  2013-01-01 | 1900-01-01 15:00:00 |        1

This table is only for idSession=1 what I wanted was for all sessions. If there are no sessions for one day can return NULL.

The hard this query or procedure, is that they have to show me all the days of the month when there are sessions for that area.

For this, I already used this query:

;WITH t1 AS 
(
    SELECT  
        startDate,
        DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', startDate), '1900-01-01') firstInMonth,
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', startDate) + 1, '1900-01-01')) lastInMonth,
        COUNT(*) cnt
    FROM    
        @SessionsPerArea
    WHERE
        idArea = 1
    GROUP BY
        startDate
), calendar AS
(
    SELECT DISTINCT 
        DATEADD(DAY, c.number, t1.firstInMonth) d
    FROM    
        t1 
    JOIN
        master..spt_values c ON type = 'P'
                             AND DATEADD(DAY, c.number, t1.firstInMonth) BETWEEN t1.firstInMonth AND t1.lastInMonth
)
SELECT  
    d date, 
    cnt Session
FROM
    calendar c
LEFT JOIN 
    t1 ON t1.startDate = c.d

It is quite complex, if anyone has an easy way to do this was excellent.

Upvotes: 1

Views: 549

Answers (2)

user2191219
user2191219

Reputation: 36

I think maybe you simply need an outer join between calendar and @SessionsPerArea...so all the days in the calendar table are returned regardless of a match to the @SessionsPerArea table?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, this is simply a join between the calendar table and @SessionPerArea,w ith the right conditions:

select spa.StartDate, c.hour as StartTime, spa.idSession as SessionId
from calendar c join
     @SessionsPerArea spa
     on c.hour between spa.startTime and spa.EndTime

The join is matching all times between the start and end times in the data, and then returning the values.

Upvotes: 1

Related Questions