根据不同的分组条件计算总数 [英] calculate totals based on different grouping conditions
问题描述
我有两个表 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.
这篇关于根据不同的分组条件计算总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
