問(wèn)題描述

我有兩個(gè)表 OD 和 ODD.我需要找到發(fā)貨的總數(shù)量，并且在 14 天的特定交貨期內(nèi)，每天運(yùn)送每個(gè)零件.從report_date - 7 到report_date + 7.對(duì)于分組和聚合，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.

連接兩個(gè)表的結(jié)果

預(yù)期輸出

預(yù)期結(jié)果更正

我不清楚如何使日期列在 REPORT_DATE -7 到 REPORT_DATE+7 范圍內(nèi)，以及分別與 PRINTING_DATE 和 EXP_SHIP_DATE 相關(guān)的 qty_shipped 和 qty_to_ship 總數(shù).

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

推薦答案

我可以看到幾個(gè)步驟來(lái)簡(jiǎn)化這個(gè)問(wèn)題.我假設(shè)您不想要日期時(shí)間，您只想考慮沒(méi)有時(shí)間部分的日期.所以我們只需要獲取帶有日期的表格，就可以使問(wèn)題更容易.

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.

第二件事，您有兩個(gè)日期并且您想按日期分組，因此讓我們單獨(dú)對(duì)每個(gè)日期進(jìn)行分組，然后將結(jié)果集合并回來(lái).

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.

第三，您需要一個(gè)從 -7 到 +7 的日期范圍.好的，讓我們?cè)囍阉纸獬筛〉暮推讲⒌玫揭恍┙Y(jié)果.

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]

請(qǐng)嘗試一下，希望對(duì)您有所幫助.

please try it and hope it helps.

這篇關(guān)于根據(jù)不同的分組條件計(jì)算總數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！