How I can group the results by day in this query with SQL Server?

Question

I have this table structure for Diary table:

 CREATE TABLE Diary
(
     [IdDiary] bigint, 
     [UserId] int,
     [IdDay] numeric(18,0),
     [IsAnExtraHour] bit
);

INSERT INTO Diary ([IdDiary],  [UserId],  [IdDay], [IsAnExtraHour])
values 
(51, 1409, 1, 0),
(52, 1409, 1, 1),
(53, 1409, 3, 0),
(54, 1409, 5, 0),
(55, 1409, 5, 1),
(56, 1408, 2, 0);

And this structure for DiaryTimetable table:

CREATE TABLE DiaryTimetable
(
     [IdDiary] bigint, 
     [Hour] varchar(50)
);

  INSERT INTO DiaryTimetable ([IdDiary], [Hour])
VALUES
    (51, '09:00'),
    (51, '09:30'),
    (51, '10:00'),
    (51, '10:30'),
    (51, '11:00'),
    (52, '15:00'),
    (52, '15:30'),
    (52, '16:00'),
    (52, '16:30'),
    (52, '17:00'),
    (53, '11:00'),
    (53, '11:30'),
    (53, '12:00'),
    (53, '12:30'),
    (53, '13:00'),
    (54, '10:00'),
    (54, '10:30'),
    (54, '11:00'),
    (54, '11:30'),
    (54, '12:00'),
    (55, '16:00'),
    (55, '16:30'),
    (55, '17:00'),
    (55, '17:30'),
    (55, '18:00'),
    (56, '15:00'),
    (56, '15:30'),
    (56, '16:00'),
    (56, '16:30'),
    (56, '17:00');

I used this query to get the max hour and the min hour for the userid 1409, to get for each day the time thats enter and the time thats leave the work. The idday correspond with the number of the day of the week. For example 1 is monday, 2 is tuesday etc...

 SELECT d.IdDiary, d.IdDay, MIN(Hour) as 'Start Time', MAX(Hour) as 'End Time', IsAnExtraHour
FROM Diary AS d
LEFT JOIN DiaryTimetable AS dt ON d.IdDiary = dt.IdDiary
where userid = 1409
GROUP BY d.IdDiary, d.IdDay, IsAnExtraHour

This query give this result:

I want to get this result:

    Day       Start Time    End Time    Start Extra Time    End Extra Time
    -----     ----------    --------    ---------------     ---------------
   Monday       09:00         11:00        15:00                17:00
   Wednessday   11:00         13:00        
   Friday       10:00         12:00        16:00                18:00

I have a column (IsAnExtraHour) this column indicates if this row has extra hours in a day, for example an employe start work in monday at 09:00 to 11:00 and then works again in the afternoon at 15:00 to 17:00, so I want to know how can I group this hours in the same row, I hope I've been able to express clearly, I accept suggestions thanks.

That's how one should ask SQL questions. All do as Esraa_92 does! — Quassnoi, Apr 19 '16 at 09:22
Don't use single quotes for column aliases. To avoid confusion and hard-to-find bugs, you should use single quotes only for string and date/time constants. — Gordon Linoff, Apr 19 '16 at 11:06

score 9 · Accepted Answer · answered Apr 19 '16 at 09:30

SELECT  d.IdDay,
        MIN(CASE WHEN isAnExtraHour = 0 THEN hour END) as 'Start Time',
        MAX(CASE WHEN isAnExtraHour = 0 THEN hour END) as 'End Time',
        MIN(CASE WHEN isAnExtraHour = 1 THEN hour END) as 'Start Extra Time',
        MAX(CASE WHEN isAnExtraHour = 1 THEN hour END) as 'End Extra Time'
FROM    Diary AS d
LEFT JOIN
        DiaryTimetable AS dt
ON      dt.IdDiary = d.IdDiary
WHERE   userid = 1409
GROUP BY
        d.IdDay

Thanks for your answer it´s very easy to understand :-D – Esraa_92 Apr 19 '16 at 09:46 — Esraa_92, Apr 19 '16 at 09:46

score 5 · Answer 2 · edited Apr 19 '16 at 10:08

I use the code from @Quassnoi and I added this:

SELECT DATENAME(weekday, d.idday-1) as 'Day' ,
       MIN(CASE WHEN isAnExtraHour = 0 THEN hour END) AS 'Start Time',
       MAX(CASE WHEN isAnExtraHour = 0 THEN hour END) AS 'End Time',
       MIN(CASE WHEN isAnExtraHour = 1 THEN hour END) AS 'Start Extra Time',
       MAX(CASE WHEN isAnExtraHour = 1 THEN hour END) AS 'End Extra Time'
FROM Diary AS d
LEFT JOIN DiaryTimetable AS dt ON dt.IdDiary = d.IdDiary
WHERE userid = 1409
GROUP BY d.IdDay

I hope this help someone, thanks all for your answers.

score 1 · Answer 3 · answered Apr 19 '16 at 09:26

The way I'd approach this would be to work out your standard and extra hours separately, something like this;

SELECT d.IdDay
    ,MIN(dt.StartTime) AS 'Start Time'
    ,MAX(dt.EndTime) AS 'End Time'
    ,MIN(ex.StartTime) AS 'Start Extra Time'
    ,MAX(ex.EndTime) AS 'End Extra Time'
FROM Diary AS d
LEFT JOIN (
    SELECT IdDiary
        ,MIN(Hour) AS StartTime
        ,MAX(Hour) AS EndTime
    FROM DiaryTimetable
    GROUP BY IdDiary
    ) AS dt ON d.IdDiary = dt.IdDiary
    AND d.IsAnExtraHour = 0
LEFT JOIN (
    SELECT IdDiary
        ,MIN(Hour) AS StartTime
        ,MAX(Hour) AS EndTime
    FROM DiaryTimetable
    GROUP BY IdDiary
    ) AS ex ON d.IdDiary = ex.IdDiary
    AND d.IsAnExtraHour = 1
WHERE userid = 1409
GROUP BY d.IdDay

Of course, you can do a case statement on the IdDay or link it to a date table if you have one to return the day as text (e.g. Monday rather than 1) — Rich Benner, Apr 19 '16 at 09:28

score 1 · Answer 4 · answered Apr 19 '16 at 09:26

You can first get non-extra hours and left join extra hours to them by UserId and IdDay. Like this:

SELECT
    CASE BaseHours.IdDay
        WHEN 1 THEN 'Monday' 
        WHEN 2 THEN 'Tuesday' 
        WHEN 3 THEN 'Wednesday' 
        WHEN 4 THEN 'Thursday' 
        WHEN 5 THEN 'Friday' 
        WHEN 6 THEN 'Saturday' 
        WHEN 7 THEN 'Sunday' 
    END AS [WeekDay],
    MIN(BaseTimeTable.Hour) as 'Start Time',
    MAX(BaseTimeTable.Hour) as 'End Time',
    MIN(ExtraTimeTable.Hour) as 'Start Extra Time',
    MAX(ExtraTimeTable.Hour) as 'End Extra Time'
FROM Diary AS BaseHours
LEFT JOIN Diary ExtraHours
    ON ExtraHours.UserId = BaseHours.UserId
        AND ExtraHours.IdDay = BaseHours.IdDay AND ExtraHours.IsAnExtraHour = 1
JOIN DiaryTimetable AS BaseTimeTable
    ON BaseHours.IdDiary = BaseTimeTable.IdDiary
LEFT JOIN DiaryTimetable AS ExtraTimeTable
    ON ExtraHours.IdDiary = ExtraTimeTable.IdDiary
WHERE BaseHours.Userid = 1409 AND BaseHours.IsAnExtraHour = 0
GROUP BY BaseHours.IdDay

How I can group the results by day in this query with SQL Server?

4 Answers4