根据不同的分组条件计算总数

问题描述

我有两个表 OD 和 ODD.我需要找到发货的总数量，并且在 14 天的特定交货期内，每天运送每个零件.从report_date - 7 到report_date + 7.对于分组和聚合，printing_date 用于shipped_qty，exp_shipping_date 用于to_ship qty.

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.

连接两个表的结果

预期输出

预期结果更正

我不清楚如何使日期列在 REPORT_DATE -7 到 REPORT_DATE+7 范围内，以及分别与 PRINTING_DATE 和 EXP_SHIP_DATE 相关的 qty_shipped 和 qty_to_ship 总数.

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.

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

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.

这篇关于根据不同的分组条件计算总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！