I am looking for the correct syntax
I can set up different queries but I am looking for one query
Tables:
T1 Country Metal Date Pnum Austria Gold 20-Dec-18 P31182 Austria Gold 20-Dec-18 P31181 Belgium Grass 6-Dec-17 P30703 Italy Gold 16-Nov-17 P30702 Zimbabwe Copper 8-Jul-17 P29999 T2 Pnum T Week Type Color Descr Status Buy P31182 2 2019-23 Apple Black Description one Ready 200 P31181 1 2019-23 Apple Black Description two Ready 150 P30703 2 2019-28 Apple Black Description four Waiting 120 T3 Work Hr Pr Pnum AFNA 4 3.50 P31182 CONS 3 1.25 P31182 TREX 2.5 3.00 P31182 AFNA 3 3.50 P31181 CONS 3 1.25 P31181 TREX 2.5 3.00 P31181 AFNA 4 3.50 P30703 MEET 5 5.00 P30703 TREX 2.5 3.00 P30703 T4 Used Pnum 3010.50 P31182 500.00 P31182 2254.47 P31181 555.55 P30703 155.25 P30703
Desired result: (minus the calculation in brackets, that’s for clarity)
Country Date Week Type Color Metal Pnum Descr Status Hr*T Hr*T*Pr Used*T Buy*T Work Austria 20-Dec-18 2019-23 Apple Black Gold P31182 Description one Ready 19 50.50 7021 400 Austria (4*2) (4*2*3.50) (3010.50*2) (200*2) AFNA Austria (3*2) (3*2*1.25) (500*2) CONS Austria (2.5*2) (2.5*2*3) TREX Austria 20-Dec-18 2019-23 Apple Black Gold P31181 Description two Ready 8.5 25.50 2254.47 150 Austria (2.5*1) (2.5*1*3.00) (2254.47*1) (150*1) AFNA Austria (3*1) (3*1*3.50) CONS Austria (2.5*1) (2.5*1*3.00) TREX Belgium 6-Dec-17 2019-28 Apple Black Grass P30703 Description four Waiting 23 93.00 1421.60 240 Belgium (4*2) (4*2*3.50) (555.55*2) (120*2) AFNA Belgium (5*2) (5*2*5.00) (155.25) MEET Belgium (2.5*2) (2.5*2*3) TREX
If I do something like this I get wrong totals under the SUM columns
SELECT Country, Date, Week, Type, Color, Metal, Pnum, Desc, Status, SUM(Hr*T), SUM(Hr*T*Pr), (Used*T), (Buy*T), Work FROM T2 INNER JOIN T1 table1 on table1.Pnum = table2.Pnum INNER JOIN T3 table3 on table3.Pnum = table2.Pnum INNER JOIN T4 table4 on table4.Pnum = table2.Pnum GROUP BY Pnum
EDIT Sorry, I forgot to include a Pnum in the first table. I will add it and will play around with the answers. Table T1 is not very important. It only needs some info from there depending on the Pnum in T2
Advertisement
Answer
Try something like this (apologies for the vague references, but it seems that your original example is already somewhat simplified from reality):
; with CTE As
(Select Country, Date, Week, Pnum.... -- the rest of your columns here
, sum(HR*T),... -- the rest of your aggregations here
, '' as Work
From T1
Inner Join T2... -- the already working joins you have here
Group by Country, Date, Week, Pnum... -- repeat the columns from the first line here
union all
Select distinct '', '', '', Pnum... --Add a '' here for each column that you want to be blank, otherwise pull the column name
, Work -- retain this column
From T3)
select * from CTE
order by Pnum, case when Work = '' then 1 else 2 end -- to sort the results the way you have them
Basically, you are creating your aggregate rows and your “work” rows separately, then combining them into one table and ordering the result to “group” them together.