Arsalan Qaiser Arsalan Qaiser - 1 month ago 9
SQL Question

trouble in query

hey every one i have this record

id date hours groupkey
1 1-12-2016 1 NULL
2 2-12-2016 2 NULL
3 3-12-2016 1 3
4 4-12-2016 1 3
5 5-12-2016 1 3


and i want this

id startdate enddate hours
1 1-12-2016 1-12-2016 1
2 2-12-2016 2-12-2016 2
3 3-12-2016 5-12-2016 3


i am using this query

SELECT Max(Isnull(groupkey, id)) AS id,
Min(date) AS startdate,
Max(date) AS enddate,
Sum(timeoffhours) AS hours,
CASE
WHEN Min(groupkey) IS NULL THEN 'No'
ELSE 'Yes'
END Status
FROM table
WHERE employeekey = 20
AND date >= '1/1/2016'
AND date <= '12/31/2016'
GROUP BY groupkey


but it is making one row for null group key please help me

Answer

You should GROUP BY Isnull(groupkey, id)) to solve your issue here. You're still grouping on the regular groupkey.