0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Tayyab Hussain
  • 3
  • 4
  • 3
    Sample data and desired results would really help clarify what you are asking. – Dale K Apr 28 '22 at 05:50
  • Aside... `select distinct OperationName, linename, ... group by OperationName, linename` seems redundantly redundant. – AlwaysLearning Apr 28 '22 at 06:13
  • 2
    There are plenty of questions on how to pivot dynamically on [so], what were wrong with those? What about them didn't you understand? Though dynamic pivots are normally something for your presentation layer. – Thom A Apr 28 '22 at 06:57

0 Answers0