根据不同的分组条件计算合计

Question

我有两张桌子OD和ODD。我需要找到总数量发运，和to_船舶，每一天，为一个特定的交付14天的每一部分。从report_date - 7到report_date +7。对于分组和聚合，printing_date用于shipped_qty，exp_shipping_date用于to_ship数量。

两个表的连接结果

​

预期输出

​

​

预期结果已更正

​

​

我不清楚如何使date列位于REPORT_DATE -7到REPORT_DATE+7的范围内，以及分别与PRINTING_DATE和EXP_SHIP_DATE相关的qty_shipped和qty_to_ship总计。

    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

Answer 1

我可以看到几个步骤来使这个问题变得更容易。我假设你不想要日期时间，你只想考虑日期而不考虑时间部分。因此，为了简化问题，我们只需要获取包含日期的表。

第二件事，您有两个日期，并且希望按日期分组，因此让我们单独对每个日期进行分组，然后将结果集合并回去。

第三，你需要一个从-7到+7的日期范围。好的，让我们试着把它分解成更小的和平，并得到一些结果。

 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]

请尝试一下，希望它能有所帮助。