summary report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

summary report

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-15 : 00:14:32

I am trying to create a summary report that will group by terminal and commodity class. I cannot figure out how to join these in the same summary because there isn’t any relationship other than the order number that I do not wnat to group by. When I use the following select than the total are included in the lh column. Can anyone see what I am doing wrong?

Here is the code I am using

select a.ivh_revtype1 as Terminal, a.cmd_class,
sum (a.LineHaul) LineHaul,
sum (b.FSC) as FSC,
sum (b.ACC) as ACC,
sum (a.TotalReveune) as Total
from nvoices_summary as a
left join invoices_summary as b on b.ord_number = a.ord_number --and b.cmd_class = 'UNK'
where month(a.ivh_deliverydate) = '01' and a.cmd_class <> 'UNK'
group by a.ivh_revtype1, a.cmd_class
order by a.ivh_revtype1, a.cmd_class

Here are examples of 2 records. I would like the following as a result

Terminal cmd_class LinHaul FSC ACC
SID GAS Amount Amount Amonnt
OMA LPG Amount Amount Amount

Here is the sample data

ord_number ivh_revtype1 cmd_code cmd_class linehaul fsc acc
10035 SID UNKNOWN UNK NULL 85.82 NULL
10035 SID N GAS 660.13 NULL NULL
10097 OMA LPG LPG 688.10 NULL NULL
10097 OMA UNKNOWN UNK NULL NULL 250.00
10097 OMA UNKNOWN UNK NULL 96.33 NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 05:33:55

select a.ivh_revtype1 as Terminal, a.cmd_class,
sum (COALESCE(a.LineHaul, 0)) LineHaul,
sum (COALESCE(b.FSC, 0)) as FSC,
sum (COALESCE(b.ACC, 0)) as ACC,
sum (COALESCE(a.TotalReveune, 0)) as Total
from nvoices_summary as a
left join invoices_summary as b on b.ord_number = a.ord_number
where month(a.ivh_deliverydate) = '01' and a.cmd_class <> 'UNK'
group by a.ivh_revtype1, a.cmd_class
order by a.ivh_revtype1, a.cmd_class

?? It would be a LOT better if you posted actual expected result instead if just "amount"...

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-15 : 09:01:16

Hello Peso

That gave the same result as what I had. Below are the results that it should be.

UNK is either the ACC or the FSC amount, but should be included in the same totals with the cmd_class that is not null. ACC and FSC is not a commodity, so there is nothing in the commodity table to find. The only relationship I can see is the ord_number. Here is what the output should look like with the Totals.

Here are examples of 2 records. I would like the following as a result

Terminal cmd_class LinHaul FSC ACC Total
SID GAS 660.13 85.82 745.95
OMA LPG 688.10 96.33 250.00 1034.43

Here is the sample data

ord_number ivh_revtype1 cmd_code cmd_class linehaul fsc acc
10035 SID UNKNOWN UNK NULL 85.82 NULL
10035 SID N GAS 660.13 NULL NULL
10097 OMA LPG LPG 688.10 NULL NULL
10097 OMA UNKNOWN UNK NULL NULL 250.00
10097 OMA UNKNOWN UNK NULL 96.33 NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 10:43:18

[code]DECLARE @Sample TABLE
(
ord_number INT,
ivh_revtype1 VARCHAR(20),
cmd_code VARCHAR(20),
cmd_class VARCHAR(20),
linehaul MONEY,
fsc MONEY,
acc MONEY
)

INSERT @Sample
SELECT 10035, 'SID', 'UNKNOWN', 'UNK', NULL , 85.82, NULL UNION ALL
SELECT 10035, 'SID', 'N' , 'GAS', 660.13, NULL , NULL UNION ALL
SELECT 10097, 'OMA', 'LPG' , 'LPG', 688.10, NULL , NULL UNION ALL
SELECT 10097, 'OMA', 'UNKNOWN', 'UNK', NULL , NULL , 250.00 UNION ALL
SELECT 10097, 'OMA', 'UNKNOWN', 'UNK', NULL , 96.33, NULL

SELECT ivh_revtype1 AS Terminal,
MAX(NULLIF(cmd_class, 'unk')) AS cmd_class,
SUM(COALESCE(linehaul, 0)) AS linhaul,
SUM(COALESCE(fsc, 0)) AS fsc,
SUM(COALESCE(acc, 0)) AS acc,
SUM(COALESCE(linehaul, 0) + COALESCE(fsc, 0) + COALESCE(acc, 0)) AS Total
FROM @Sample
GROUP BY ivh_revtype1[/code]

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-03-15 : 13:22:08

I think this is what I an looking for. Thanks for all the help Peso

select a.ivh_revtype1 as Terminal, a.cmd_class,
sum (a.LineHaul) LineHaul,
sum (b.FSC) as FSC,
sum (b.ACC) as ACC,
sum (a.LineHaul+ISNULL(b.FSC, 0)+ISNULL(b.ACC,0)) as Total
from invoices_summary as a
left join (
select ord_number, sum(FSC) FSC, sum(ACC) ACC
from invoices_summary
where month(ivh_deliverydate) = '01' and cmd_class = 'UNK'
group by ord_number ) b on b.ord_number = a.ord_number
where month(a.ivh_deliverydate) = '01'
group by a.ivh_revtype1, a.cmd_class
order by a.ivh_revtype1, a.cmd_class

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources