[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

SQL Aggregate query (SO CLose)!

I am really, really close with this query.  Here is the query, and it works exactly as expected with data set 1:

SELECT bi.Load_ID, bi.bhrv_invoice_id, bi.owning_office_nbr, bi.mile_qty
     , SUM(CASE WHEN tir.lane_rate_type_code = 1002 AND t.Trip_ID = tir.Trip_ID THEN tir.Revenue_Amt END) as Office_Fuel    
     , SUM(CASE WHEN tir.lane_rate_type_code = 1001 AND t.Trip_ID = tir.Trip_ID THEN tir.Revenue_Amt END) as Office_Line_Haul
     , SUM(CASE WHEN t.Trip_ID = tir.Trip_ID THEN tir.Revenue_Amt END) as Total_Office
     , SUM(CASE WHEN tir.lane_rate_type_code = 1002 THEN tir.Revenue_Amt END) as Load_Fuel
     , SUM(CASE WHEN tir.lane_rate_type_code = 1001 THEN tir.Revenue_Amt END) as Load_Line_Haul
     , SUM(tir.Revenue_Amt) as Total_Load     
FROM bhrv_invoice bi
inner JOIN (  SELECT x.Load_ID, x.bhrv_Invoice_ID, x.Trip_ID, x.dom_loc_id, total_mile_qty
                           FROM bhrv_trip x
                           WHERE x.dom_loc_id = (  SELECT y.dom_loc_id 
                                                                     FROM bhrv_trip y  
                                                                     where  x.Load_ID = y.Load_ID AND x.bhrv_Invoice_ID = y.bhrv_Invoice_ID
                                                                     AND y.total_mile_qty =   (select max(z.total_mile_qty)    from bhrv_trip z where z.bhrv_invoice_id = y.bhrv_invoice_id)  )    
                            )   t 
ON bi.Load_ID = t.Load_ID AND bi.bhrv_Invoice_ID = t.bhrv_Invoice_ID
INNER JOIN trip_invoice_rate tir ON t.bhrv_Invoice_ID = tir.bhrv_Invoice_ID
WHERE bi.Load_ID in ( 85083675 )
GROUP BY bi.Load_ID, bi.bhrv_Invoice_ID, bi.owning_office_nbr, bi.mile_qty

Open in new window


However, with data set 2, when I add another trip to a load (in table t from the code snipit, but table b in the excel example), it is doubling the three summing functions for Load_Fuel, Load_Line_Haul and Load_Total.  In data set 2, I highlighted in red what the data difference is, as well as the results that it's returning.

I'm trying to get it NOT to double these results, but I've ran out of ideas.  Thanks in advance for the help.

(from the excel files to the code snipit, table a is named "bi", table b is named "t" and table c is named "tir")

Ryan
Data-Set-1.xlsx
Data-Set-2.xlsx
0
nikotromus11
Asked:
nikotromus11
  • 2
1 Solution
 
nikotromus11Author Commented:
So.... Since nobody is replying, I am guessing nobody could figure this out.  I talked with one of our very senior developers today, and he said the problem with a query like this is that it's trying to do too much, and it's just too un-maintainable.  He recommended to pull it back in two or three chunks, put a key and a business object in a hashmap, and then append more to the business object with another query.  Then return the final result set to the client.  That's probably the approach I will take.  

Although - it would be cool to understand how to keep from doubling up this cost.  =)
0
 
Anthony PerkinsCommented:
Since nobody is replying, I am guessing nobody could figure this out.
No, I suspect it is because it is a difficult problem made more complicated by the fact that there are no setup scripts so everything has to be manually created and manually imported and quite frankly 4 hours to do that is not a lot, even if we were interested.
0
 
lwadwellCommented:
Try this ... I haven't changed the column/table names to the new ones above.
SELECT a.Load_ID, a.Invoice_ID
     , b.Office, b.Miles
     , SUM(CASE WHEN c.Rate_Type = 'Fuel'      AND b.Office = d.Office THEN c.Revenue_Amt END) as Office_Fuel
     , SUM(CASE WHEN c.Rate_Type = 'Line_Haul' AND b.Office = d.Office THEN c.Revenue_Amt END) as Office_Line_Haul
     , SUM(CASE WHEN b.Office = d.Office 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 x.Load_ID, x.Invoice_ID, Trip_ID
                 , Office, sum_Miles as Miles
            FROM tableB x
            INNER JOIN ( SELECT Load_ID, Invoice_ID, MAX(Miles) max_miles, SUM(Miles) sum_miles FROM tableB GROUP BY Load_ID, Invoice_ID ) y
            ON x.Load_ID = y.Load_ID AND x.Invoice_ID = y.Invoice_ID AND x.Miles = max_miles) b
ON a.Load_ID = b.Load_ID AND a.Invoice_ID = b.Invoice_ID
INNER JOIN tableB d ON a.Load_ID = d.Load_ID AND a.Invoice_ID = d.Invoice_ID
INNER JOIN tableC C ON d.Invoice_ID = c.Invoice_ID AND d.Trip_ID = c.Trip_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

0
 
nikotromus11Author Commented:
@ acperkins - true that.  I don't expect anything from this post.  It's a pretty complex monster.  Like i said, I can always break this into different parts and get it.  It will be more maintainable that way anyways.

@wadwell - judging by your post from yesterday, I'm interested to see what this will do!  I'll check it out first thing tomorrow.

I should really post the query that India offshore wrote to try to solve this problem.  Oh my god.  Talk about $5 an hour code.  It is truly laughable.  And - they took over 1 month to write it.

Ryan
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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