問題描述
我有兩個表 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模板網！