In SQL I need to summarize different values of a column based on another column

Question

I need the query to give a summary of different ResourceId, You can see "G1" AND a series of "M0" Resources.
I want this "M0" Resources as "Press shop" in summary with its scheduledqty as sum and G1 as "Grinding".
This is my query:

Select
    '' as Detail,
    ShiftDate,
    Max(ShiftId) as ShiftId,
    ResourceId,
    ResourceDesc,
    LineId,
    CellNo,
    Item,
    ItemDesc,
    Sum(ScheduledQty) as ScheduledQty
from (
    Select Case When b.Remarks= 'B' and cast(a.PlannedStartTime as Time) <='08:30:00.0000000' 
        Then Case When datename(dw,cast(a.PlannedStartTime as Date))='Monday'
            Then cast(a.PlannedStartTime-2 as Date)
            else cast(a.PlannedStartTime-1 as Date) end
        Else cast(a.PlannedStartTime as Date) 
    End as ShiftDate,
    b.Remarks as ShiftId,
    a.ResourceId,
    c.ResourceDesc,
    c.LineId,
    c.CellNo,
    a.RoutingId as Item,
    d.ItemDesc,
    convert(nvarchar(8),a.PlannedStartTime,114) as StartTime,
    convert(nvarchar(8),a.PlannedEndTime,114) as EndTime,
    Sum(Round(a.QtyAllocated,0)) as ScheduledQty 
From WorkOrderOpResPlan a
Left Join LocationCalendar b On a.PlantId=b.PlantId
    and Cast(a.PlannedStartTime as Time)>=Cast(b.ShiftStartTime as Time)
    and Cast(a.PlannedEndTime as Time)<=cast(b.ShiftEndTime as Time)
Inner Join ResourceMaster c on a.ResourceId=c.ResourceId
    and a.PlantId=c.PlantId and c.ResourceType='Simple'
Left Join ItemMaster d On a.RoutingId=d.Item
    and a.PlantId=d.PlantId
Where a.PlantId='SDL'
Group by
    b.Remarks,
    a.ResourceId,
    c.ResourceDesc,
    c.LineId,
    c.CellNo,
    a.RoutingId,
    d.ItemDesc,
    a.PlannedStartTime,
    a.PlannedEndTime)x
Where ShiftDate<=Cast(GetDate()+2 as Date)
Group by ShiftDate, ResourceId, ResourceDesc, LineId, CellNo, Item, ItemDesc

I want to get the scheduledQty to be summarized based on the differrent ResourceId. Please Help Me

This is the image 1

Answer 1

Try this please. Please note that to reduce the number of rows you have to decide which column values are not needed (like Item and Item Description).

SELECT
      '' AS Detail
    , ShiftDate
    , MAX(ShiftId) AS ShiftId
    , ResourceId
    , ResourceDesc
    , LineId
    , CellNo
    , SUM(ScheduledQty) AS ScheduledQty
FROM (
      SELECT
            CASE
                  WHEN b.Remarks = 'B' AND
                  CAST(a.PlannedStartTime AS time) <= '08:30:00.0000000' THEN CASE
                        WHEN DATENAME(dw, CAST(a.PlannedStartTime AS date)) = 'Monday' THEN CAST(a.PlannedStartTime - 2 AS date)
                        ELSE CAST(a.PlannedStartTime - 1 AS date)
                  END
                  ELSE CAST(a.PlannedStartTime AS date)
            END AS ShiftDate
          , b.Remarks AS ShiftId
          , a.ResourceId
          , CASE
                  WHEN a.ResourceId = 'M0' THEN 'Press shop'
                  WHEN a.ResourceId = 'G1' THEN 'Grinding'
                  ELSE c.ResourceDesc
            END AS ResourceDesc
          , c.LineId
          , c.CellNo
          , a.RoutingId AS Item
          , SUM(ROUND(a.QtyAllocated, 0)) AS ScheduledQty
      FROM WorkOrderOpResPlan a
            LEFT JOIN LocationCalendar b ON a.PlantId = b.PlantId
                        AND CAST(a.PlannedStartTime AS time) >= CAST(b.ShiftStartTime AS time)
                        AND CAST(a.PlannedEndTime AS time) <= CAST(b.ShiftEndTime AS time)
            INNER JOIN ResourceMaster c ON a.ResourceId = c.ResourceId
                        AND a.PlantId = c.PlantId
                        AND c.ResourceType = 'Simple'
            LEFT JOIN ItemMaster d ON a.RoutingId = d.Item
                        AND a.PlantId = d.PlantId
      WHERE a.PlantId = 'SDL'
      GROUP BY
            b.Remarks
          , a.ResourceId
          , CASE
                  WHEN a.ResourceId = 'M0' THEN 'Press shop'
                  WHEN a.ResourceId = 'G1' THEN 'Grinding'
                  ELSE c.ResourceDesc
            END
          , c.LineId
          , c.CellNo
          , a.RoutingId
) x
WHERE ShiftDate <= CAST(GETDATE() + 2 AS date)
GROUP BY
      ShiftDate
    , ResourceId
    , ResourceDesc
    , LineId
    , CellNo