calculate totals based on different grouping condi

i have two tables OD and ODD. i need to find total quantifies shipped, and to_ ship , per day, per part for a particular delivery for 14 days. from report_date - 7 to report_date + 7. for grouping and aggregation, printing_date to be used for shipped_qty and exp_shipping_date to be used for to_ship qty.

join results of both tables

expected output

expected results corrected

i'm unclear how to have the date column that is in the range of REPORT_DATE -7 to REPORT_DATE+7, along the qty_shipped and qty_to_ship totals which respectively related to PRINTING_DATE and EXP_SHIP_DATE.

    DECLARE @REPORT AS DATETIME='2019-06-19 00:00:00.000'

    SELECT DISTINCT TOP 1000
            PLANT
            ,PARTS
            ,DATE_RANGE AS DATE
            ,SHIPPED AS QTY_SHIPPED
            ,TO_SHIP AS QTY_TO_SHIP
            FROM(
                SELECT  
                        PLANT,
                        PARTS,
                        DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) AS PRINTING_DATE,
                        EXP_SHIP_DATE AS EXP_SHIP_DATE,
                        --SUM(CASE WHEN (DATEADD(dd, 0, DATEDIFF(dd, 06, PRINTING_DATE))<=@REPORT AND DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) >= DATEADD(DAY,-7,@REPORT)) THEN QTY_PICKED ELSE 0 END) OVER (PARTITION BY PLANT,PARTS,DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) ORDER BY  PLANT,PARTS,DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE))) AS SHIPPED
                        --,SUM(CASE WHEN EXP_SHIP_DATE>=@REPORT AND EXP_SHIP_DATE <= DATEADD(DAY,7,@REPORT) THEN QTY_SAP ELSE 0 END) OVER (PARTITION BY PLANT,PARTS,EXP_SHIP_DATE ORDER BY  PLANT,PARTS,EXP_SHIP_DATE) AS TO_SHIP
                        SUM(QTY_PICKED) OVER (PARTITION BY PLANT,PARTS,DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) ORDER BY  PLANT,PARTS,DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE))) AS SHIPPED
                        ,SUM(QTY_SAP) OVER (PARTITION BY PLANT,PARTS,EXP_SHIP_DATE ORDER BY  PLANT,PARTS,EXP_SHIP_DATE) AS TO_SHIP
                        [ODD_TABLE] ODD
                             INNER JOIN
                            [OD_TABLE] OD 
                             ON
                             ODD.OUTBOUNDDELIVERY = OD.OUTBOUNDDELIVERY

                        WHERE PLANT = '1173' AND EXP_SHIP_DATE!=''
                        AND 
                        ((DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) <= DATEADD(DAY,7,@REPORT) AND DATEADD(dd, 0, DATEDIFF(dd, 0, PRINTING_DATE)) >= DATEADD(DAY,-7,@REPORT))
                         OR 
                        (EXP_SHIP_DATE <= DATEADD(DAY,7,@REPORT)AND EXP_SHIP_DATE >= DATEADD(DAY,-7,@REPORT)))
    ) SUB_QRY

    INNER JOIN 
            (--DECLARE @REPORT AS DATETIME='2019-06-19 00:00:00.000'
                SELECT DATEADD(DAY, 7, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 6, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 5, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 4, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 3, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 2, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 1, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, 0, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -1, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -2, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -3, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -4, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -5, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -6, @REPORT) AS DATE_RANGE
                UNION ALL
                SELECT DATEADD(DAY, -7, @REPORT) AS DATE_RANGE
                ) DATE_RANGE_VALUE
    ON

    DATE_RANGE_VALUE.DATE_RANGE = EXP_SHIP_DATE
    ORDER BY 
    PLANT,PARTS, DATE_RANGE
    ASC

expected is a result set as follows 


1173    PARTS   DATE          QTY_SHIPPED   QTY_TO_SHIPPED
        REPORT DATE+7       
        REPORT DATE+6       
        REPORT DATE+5       
        REPORT DATE+4       
        REPORT DATE+3       
        REPORT DATE+2       
        REPORT DATE+1       
        REPORT DATE     
        REPORT DATE-1       
        REPORT DATE-2       
        REPORT DATE-3       
        REPORT DATE-4       
        REPORT DATE-5       
        REPORT DATE-6       
        REPORT DATE-7

I can see several steps to make this problem easier. I am assuming you dont want datetime you only want to consider date without the time part. so we would need to get the table with date only to make the problem easier.

second thing you have two dates and you want to group by date, so lets group each one alone and then merge the result set back.

third, you would need a range for date from -7 to +7. ok lets try to break it down to smaller peaces and get some results.

 DECLARE @REPORT AS DATETIME='2019-06-19 01:00:01.000'
 Declare @Report_min as date=DATEADD(DAY,-7,@REPORT)
 Declare @Report_max as date=DATEADD(DAY,7,@REPORT)
 Declare @Plant as varchar(100)='1173'



 ;with ODcte as (
     --to get all our datetimes to dates only (getting the time out)
      select   
          OUTBOUNDDELIVERY 
         ,PLANT 
         ,cast(PRINTING_DATE as date) SHIP_DATE
         ,cast(EXP_SHIP_DATE as date) EXP_SHIP_DATE
      from OD_TABLE
  ),shipped as (
        --group only by shipped and get the sum
        select PLANT,PARTS,SHIP_DATE,SUM(QTY_PICKED) SHIPPED_Qty
            from ODD_TABLE ODD
                 INNER JOIN ODcte as OD ON ODD.OUTBOUNDDELIVERY = OD.OUTBOUNDDELIVERY
            WHERE PLANT = @Plant AND SHIP_DATE between  @Report_min and @Report_max 
            Group By PLANT,PARTS,SHIP_DATE
    ),Exp_ship as (
          --group only by exp to ship and get the sum
        select PLANT,PARTS,EXP_SHIP_DATE,SUM(QTY_SAP) Exp_SHIPPED_Qty
            from ODD_TABLE ODD
                 INNER JOIN ODcte as OD ON ODD.OUTBOUNDDELIVERY = OD.OUTBOUNDDELIVERY
            WHERE PLANT = @Plant AND EXP_SHIP_DATE between  @Report_min and @Report_max 
            Group By PLANT,PARTS,EXP_SHIP_DATE
    ),DateRange as (
        --lets generate a list of days
        select @Report_min [date] union all
        select dateadd(day,1,[date]) from DateRange where date<@Report_max
    ),shippedWithAllReportDays as(
    select PLANT,PARTS,DateRange.[date],SHIPPED_Qty
     From DateRange
        left outer join shipped on shipped.SHIP_DATE=DateRange.[date]
    ),exp_shippWithAllReportDays as(
        select PLANT,PARTS,DateRange.[date],Exp_SHIPPED_Qty
        From DateRange
            left outer join Exp_ship on Exp_ship.EXP_SHIP_DATE=DateRange.[date]
    )
    select 
        s.PLANT,s.PARTS,s.[date],SHIPPED_Qty,Exp_SHIPPED_Qty
     from shippedWithAllReportDays s
        left outer join exp_shippWithAllReportDays e on 
            s.PLANT=e.PLANT and
            s.PARTS=e.PARTS and
            s.[date]=e.[date]

please try it and hope it helps.