This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to make the comma separated week aggregation for common schedules.
Input Data:
|
1 2 3 4 5 6 7 8 |
ScheduleID StartDate EndDate WeekID ----------- ---------- ---------- ------ 1 2017-01-08 2017-01-09 1 1 2017-01-08 2017-01-09 2 2 2017-02-08 2017-02-09 4 3 2017-04-08 2017-05-08 6 3 2017-04-08 2017-05-08 7 4 2017-02-10 2017-04-09 3 |
Expected Output:
|
1 2 3 4 5 6 |
ScheduleID StartDate EndDate Weeks ----------- ---------- ---------- ------- 4 2017-02-10 2017-04-09 3 3 2017-04-08 2017-05-08 6, 7 2 2017-02-08 2017-02-09 4 1 2017-01-08 2017-01-09 1, 2 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE Schedules ( ScheduleID INT ,StartDate DATE ,EndDate DATE ,WeekID VARCHAR(2) ) GO INSERT INTO Schedules VALUES (1,'2017-01-08','2017-01-09',1) ,(1,'2017-01-08','2017-01-09',2) ,(2,'2017-02-08','2017-02-09',4) ,(3,'2017-04-08','2017-05-08',6) ,(3,'2017-04-08','2017-05-08',7) ,(4,'2017-02-10','2017-04-09',3) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT ScheduleID,StartDate, EndDate, STUFF(( SELECT ', ' + t1.WeekID FROM Schedules t1 WHERE s.ScheduleID = t1.ScheduleID AND s.StartDate = t1.StartDate AND s.EndDate = t1.EndDate FOR XML PATH('')), 1, 1, '') Weeks FROM Schedules s GROUP BY ScheduleID,StartDate,EndDate ORDER BY ScheduleID DESC |
Please try the different solution for this puzzle and share it via comment...
