Esraa_92 Esraa_92 - 7 months ago 9
SQL Question

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

sql fiddle demo here

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:

enter image description here

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.

Answer
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