Need help on this SQL query. It produces a Pivoted Hour Wise result of a production line of a work shift, at a particular date.
SELECT distinct Operations.OperationName,Lines.linename,
SUM(CASE WHEN op_Time_From='07:00' THEN Production END) as [07:00],
SUM(CASE WHEN op_Time_From='08:00' THEN Production END) as [08:00],
SUM(CASE WHEN op_Time_From='09:00' THEN Production END) as [09:00],
SUM(CASE WHEN op_Time_From='10:00' THEN Production END) as [10:00],
SUM(CASE WHEN op_Time_From='11:00' THEN Production END) as [11:00],
SUM(CASE WHEN op_Time_From='12:00' THEN Production END) as [12:00],
SUM(CASE WHEN op_Time_From='14:00' THEN Production END) as [14:00],
SUM(CASE WHEN op_Time_From='15:00' THEN Production END) as [15:00],
SUM(CASE WHEN op_Time_From='16:00' THEN Production END) as [16:00],
SUM(CASE WHEN op_Time_From='17:00' THEN Production END) as [17:00]
FROM Production
JOIN Lines on Lines.lineID=production.Line_FK_ID
JOIN Operations on Operations.opID=production.OP_FK_ID
JOIN [Shift] on [Shift].ShiftID=production.Shift_FK_ID
WHERE
ShiftID in (1)
and op_date in ('2022-04-26')
and Line_FK_ID in (1)
Group By Operations.OperationName,Lines.linename
The Case When condition uses static time, I want to generate the time dynamically. Any ideas?
select distinct OperationName, linename, ... group by OperationName, linenameseems redundantly redundant.