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.