• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 893
  • Last Modified:

SQL Aggregation Quandary

I beat my head on my desk today trying to figure this out and could not.  

I've attached the data structure.  It has the three tables and their data with the expected results from a query.  Here are the requirements:

1. Display 1 record per load
2. Only display the office revenue for the office with the most miles.
3. Aggregate the total office revenue
4. Aggregate miles for the load
5. Aggregate the load by fuel charge, line haul charge, and total load charges.
6. The base criteria would look something like " Where A.Load_ID in (1, 2, 3) "

Thanks in advance for any help.  It is truly appreciated.

Ryan
Data.xlsx
0
nikotromus11
Asked:
nikotromus11
  • 6
  • 3
  • 2
1 Solution
 
sachitjainCommented:
select A.Load_Id, A.Invoice_Id, B.OFfice,
      B.Miles,
      (Select Revenue_Amt from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id)  Office_Fuel,
      (Select Revenue_Amt from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id)  Office_Line_Haul,
      (Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id and C.Trip_ID = B.Trip_Id) Total_Office,
      (Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id)  Load_Fuel,
      (Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id)  Load_Line_Haul,
      (Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id) Total_Load
from TableA A
            left join (select Load_Id, Invoice_ID, MAX(Office) Office, MAX(Trip_Id) Trip_Id, sum(Miles) Miles from TableB group by Load_Id, Invoice_ID) B
                        on B.Load_Id = A.Load_Id and B.Invoice_ID = A.Invoice_Id
0
 
nikotromus11Author Commented:
I'll try this out tomorrow when I get in front of an SQL engine.  But, I can already see it has problems without looking very deep.  For one thing, why are you worried about max office or max trip?  Shouldn't you be more concerned about max miles, as that is the requirement for displaying office information.  Requirement 2 was completely missed, along with requirement 6.
0
 
lwadwellCommented:
My take on it ...
;with tableA as (
select 1 as Load_ID, 123 as Invoice_ID union all
select 2 as Load_ID, 124 as Invoice_ID union all
select 3 as Load_ID, 125 as Invoice_ID
), tableB as (
select 1 as Load_ID, 123 as Invoice_ID, 100 as Trip_ID, 50 as Miles, 500 as Office union all
select 1 as Load_ID, 123 as Invoice_ID, 101 as Trip_ID, 100 as Miles, 501 as Office union all
select 2 as Load_ID, 124 as Invoice_ID, 102 as Trip_ID, 222 as Miles, 502 as Office union all
select 3 as Load_ID, 125 as Invoice_ID, 103 as Trip_ID, 43 as Miles, 503 as Office union all
select 3 as Load_ID, 125 as Invoice_ID, 104 as Trip_ID, 48 as Miles, 504 as Office
), tableC as (
select 123 as Invoice_ID, 100 as Trip_ID, 'Line_Haul' as Rate_Type, 250 as Revenue_Amt union all
select 123 as Invoice_ID, 100 as Trip_ID, 'Fuel' as Rate_Type, 50 as Revenue_Amt union all
select 123 as Invoice_ID, 101 as Trip_ID, 'Line_Haul' as Rate_Type, 500 as Revenue_Amt union all
select 123 as Invoice_ID, 101 as Trip_ID, 'Fuel' as Rate_Type, 75 as Revenue_Amt union all
select 124 as Invoice_ID, 102 as Trip_ID, 'Line_Haul' as Rate_Type, 350 as Revenue_Amt union all
select 124 as Invoice_ID, 102 as Trip_ID, 'Fuel' as Rate_Type, 13 as Revenue_Amt union all
select 125 as Invoice_ID, 103 as Trip_ID, 'Line_Haul' as Rate_Type, 100 as Revenue_Amt union all
select 125 as Invoice_ID, 103 as Trip_ID, 'Fuel' as Rate_Type, 20 as Revenue_Amt union all
select 125 as Invoice_ID, 104 as Trip_ID, 'Line_Haul' as Rate_Type, 7000 as Revenue_Amt union all
select 125 as Invoice_ID, 104 as Trip_ID, 'Fuel' as Rate_Type, 550 as Revenue_Amt
), tableBview as (
SELECT Load_ID, Invoice_ID, Trip_ID
     , Office, row_number()over(partition by Load_ID, Invoice_ID order by Miles DESC) OfficeRN
     , SUM(Miles)over(partition by Load_ID, Invoice_ID) as Miles
FROM tableB
)
SELECT a.Load_ID, a.Invoice_ID
     , b.Office, b.Miles
     , SUM(CASE WHEN c.Rate_Type = 'Fuel' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Fuel
     , SUM(CASE WHEN c.Rate_Type = 'Line_Haul' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Line_Haul
     , SUM(CASE WHEN b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Total_Office
     , SUM(CASE WHEN c.Rate_Type = 'Fuel' THEN c.Revenue_Amt END) as Load_Fuel
     , SUM(CASE WHEN c.Rate_Type = 'Line_Haul' THEN c.Revenue_Amt END) as Load_Line_Haul
     , SUM(c.Revenue_Amt) as Total_Load
FROM tableA A
INNER JOIN tableBview B ON a.Load_ID = b.Load_ID AND a.Invoice_ID = b.Invoice_ID AND b.OfficeRN = 1
INNER JOIN tableC C ON b.Invoice_ID = c.Invoice_ID
WHERE a.Load_ID in (1,2,3)
GROUP BY a.Load_ID, a.Invoice_ID
     , b.Office, b.Miles

Open in new window

NOTE: The first 3 CTE's create my test data ... you need only keep the 4th CTE 'tableBview'.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
nikotromus11Author Commented:
What language is this in?

 row_number()over(partition
0
 
sachitjainCommented:
Alright so here you go

select A.Load_Id, A.Invoice_Id, D.OFfice,
      B.Miles,
      (Select Revenue_Amt from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id)  Office_Fuel,
      (Select Revenue_Amt from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id)  Office_Line_Haul,
      (Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id and C.Trip_ID = D.Trip_Id) Total_Office,
      (Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Fuel' and C.Invoice_ID = A.Invoice_Id)  Load_Fuel,
      (Select sum(Revenue_Amt) from TableC C where C.Rate_Type = 'Line_Haul' and C.Invoice_ID = A.Invoice_Id)  Load_Line_Haul,
      (Select sum(Revenue_Amt) from TableC C where C.Invoice_ID = A.Invoice_Id) Total_Load
from TableA A
            left join (select Load_Id, Invoice_ID, sum(Miles) Miles, MAX(Miles) MaxMiles
                              from TableB
                              group by Load_Id, Invoice_ID) B
                  on B.Load_Id = A.Load_Id and B.Invoice_ID = A.Invoice_Id
            left join TableB D on D.Load_Id = A.Load_Id and D.Invoice_ID = A.Invoice_Id and D.Miles = B.MaxMiles

Now each of your condition is covered with this query
0
 
nikotromus11Author Commented:
@ lwadwell,

You actually took the time to add the data to temp tables and run it.  I really appreciate it.  

I am working with an Informix database engine.  The only think I see in there that I don't recognize is your example is the " row_number()over(partition " syntax.  I'll have to see if I can find something like this in Informix.

Ryan
0
 
lwadwellCommented:
Informix ... I need to look into that ... row_number() is a SQL Server, Oracle and DB2 function.
0
 
lwadwellCommented:
Perhaps this instead
SELECT a.Load_ID, a.Invoice_ID
     , b.Office, b.Miles
     , SUM(CASE WHEN c.Rate_Type = 'Fuel' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Fuel
     , SUM(CASE WHEN c.Rate_Type = 'Line_Haul' AND b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Office_Line_Haul
     , SUM(CASE WHEN b.Trip_ID = c.Trip_ID THEN c.Revenue_Amt END) as Total_Office
     , SUM(CASE WHEN c.Rate_Type = 'Fuel' THEN c.Revenue_Amt END) as Load_Fuel
     , SUM(CASE WHEN c.Rate_Type = 'Line_Haul' THEN c.Revenue_Amt END) as Load_Line_Haul
     , SUM(c.Revenue_Amt) as Total_Load
FROM tableA A
INNER JOIN (SELECT Load_ID, Invoice_ID, Trip_ID
                 , Office, (SELECT SUM(Miles) 
                            FROM tableB y 
                            WHERE x.Load_ID = y.Load_ID AND x.Invoice_ID = y.Invoice_ID) Miles
            FROM tableB x
            WHERE Office = (SELECT FIRST 1 Office 
                            FROM tableB y 
                            WHERE x.Load_ID = y.Load_ID AND x.Invoice_ID = y.Invoice_ID 
                            ORDER BY Miles DESC) ) B 
ON a.Load_ID = b.Load_ID AND a.Invoice_ID = b.Invoice_ID
INNER JOIN tableC C ON b.Invoice_ID = c.Invoice_ID
WHERE a.Load_ID in (1,2,3)
GROUP BY a.Load_ID, a.Invoice_ID
     , b.Office, b.Miles

Open in new window

Move the CTE into an in-line view as Informix doesn't support them either I think ... but not sure I have the syntax right.  I haven't used Informix much ever and not for many, many years.
0
 
nikotromus11Author Commented:
Unfortunately it's what we use at wal-mart logistics.  I much prefer SQL server myself.  I'll try to work with it tomorrow.  Thanks.
0
 
nikotromus11Author Commented:
Dude - with a little finagling, I got it to work!  Thanks a ton!  I'm accepting the solution!!!!!!
0
 
nikotromus11Author Commented:
Thanks Again!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now