mySQL - a freakish query. Can you optimize this? a challenge

[Parabola]

Have to create a report, which displays each district Manager, his Name, and the sum of the prices he sells at. Each part we have has 7 price levels they can sell at. they may also raise the price slightly to make commission. the prices are in inventory_pricing, and when ordered high to low, are like this:
p4,p3,p2,p5,p6,p7,p1

Anyways, here is my current query, which works exactly as I need it to. However, as you could imagine, it is slow. It needs to be optimized. Anyone care to try?

select p4.DistrictManager, s.Name, p4.Price4, p3.Price3, p2.Price2, p5.Price5, p6.Price6, p7.Price7, p1.Price1
FROM
   (Select ROUND(SUM(l.Price), 2) AS Price4, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price4) group by h.DistrictManager) AS p4
   inner join

   (Select ROUND(SUM(l.Price), 2) AS Price3, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price3) and (l.Price < i.Price4) group by h.DistrictManager) AS p3
   on p4.districtmanager = p3.districtmanager inner join

   (Select ROUND(SUM(l.Price), 2) AS Price2, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price2) and (l.Price < i.Price3) group by h.DistrictManager) AS p2
   on p3.districtmanager = p2.districtmanager inner join

   (Select ROUND(SUM(l.Price), 2) AS Price5, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price5) and (l.Price < i.Price2) group by h.DistrictManager) AS p5
   on p2.districtmanager = p5.districtmanager inner join

   (Select ROUND(SUM(l.Price), 2) AS Price6, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price6) and (l.Price < i.Price5) group by h.DistrictManager) AS p6
   on p5.districtmanager = p6.districtmanager inner join

   (Select ROUND(SUM(l.Price), 2) AS Price7, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price7) and (l.Price < i.Price6) group by h.DistrictManager) AS p7
   on p6.districtmanager = p7.districtmanager inner join

   (Select ROUND(SUM(l.Price), 2) AS Price1, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber
   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber
   where (l.Price >= i.Price1) and (l.Price < i.Price7) group by h.DistrictManager) AS p1
   on p1.districtmanager = p7.districtmanager inner join

sales_districtmanagers s on p7.districtmanager = s.districtmanager

[Parabola]

And now for the fun part.... I have to add a few variables- start date, end date (which is l.InvoiceDate), and whether or not the DM is an actual sales rep, or just a territory (s.Human [logical])


AND THE REAL KICKER
I Have to convert it to SQL!

RAWR!!!!!!!!!!

[Parabola]

Ok, got it into SQL, added the variables, which this will be used in VS2008. But... still wonder if it could be optimized, although now that it is in SQL, it seriously takes about 1 second versus the few minutes it took in mySQL.... glad I could help myself lol.
But seriously- if you have any ideas how to optimize the following, feel free to say so

SELECT     p4.DistrictManager, s.Name, SUM(p4.Price4) AS Price4, SUM(p3.Price3) AS Price3, SUM(p2.Price2) AS Price2, SUM(p5.Price5) AS Price5, 
                      SUM(p6.Price6) AS Price6, SUM(p7.Price7) AS Price7, SUM(p1.Price1) AS Price1
FROM         (SELECT     ROUND(SUM(l.Price), 2) AS Price4, h.DistrictManager
                       FROM          inventory_pricing AS i INNER JOIN
                                              invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price4 <= l.Price INNER JOIN
                                              invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                       WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                       GROUP BY h.DistrictManager) AS p4 INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price3, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price3 <= l.Price AND i.Price4 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p3 ON p4.DistrictManager = p3.DistrictManager AND p4.Price4 > p3.Price3 AND p4.Price4 > p3.Price3 INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price2, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price2 <= l.Price AND i.Price3 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p2 ON p3.DistrictManager = p2.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price5, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price5 <= l.Price AND i.Price2 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p5 ON p2.DistrictManager = p5.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price6, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price6 <= l.Price AND i.Price5 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p6 ON p5.DistrictManager = p6.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price7, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price7 <= l.Price AND i.Price6 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p7 ON p6.DistrictManager = p7.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price1, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price1 <= l.Price AND i.Price7 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p1 ON p1.DistrictManager = p7.DistrictManager INNER JOIN
                      sales_districtmanagers AS s ON p7.DistrictManager = s.DistrictManager
WHERE     (s.Human <> @human)
GROUP BY p4.DistrictManager, s.Name
ORDER BY p4.DistrictManager

[Jordan]

Wow, that is one long query.

[BlaineSch]

Holy **** dude!

[djemmers]

although now that it is in SQL, it seriously takes about 1 second versus the few minutes it took in mySQL.... glad I could help myself lol.

I know this is a bit off topic but...
This I don't understand.
what do you mean?
I thought sql was the language and mySQL the program?
So how can you change the query from mysql to sql?

[Orjan]

I know this is a bit off topic but...
This I don't understand.
what do you mean?
I thought sql was the language and mySQL the program?
So how can you change the query from mysql to sql?

Well, each server system has their own dialects of SQL, with add ons and sometimes a little bit different way of expressing certain things. the pure statements normally is very similar, but when it comes to other things as calculation functions and so, it starts to differ, or just limitations of results on how many rows to return differs between the different systems.

[djemmers]

ah ok , I was affraid that I missed something crucial and made all my queries slow up till now ;-)

[Parabola]

Basically here's the reason why SQL is better: mySQL is free. You can get mySQL server and all that stuff for free, which is ok for small things, but not large companies. Microsoft SQL however is not free. It costs a bit. But, that also means you get a better running server. And as Orjan explained, 2 different companies means 2 different dialects of the language. For example, c++ was developed by Bjarne Stroustrup starting in 1979 at Bell Labs (courtesy wikipedia), where as c# (of course developed later) was developed by Microsoft within the .NET initiative (courtesy wikipedia). Developed by two different groups, so they are slightly different. I know they aren't that close, but I say they are similar in that... well they are lol.

It's just how programming is... there are dialects of many different languages lol

[relapse]

No way, SQL is the Language and is a standard. MySQL IS the Program as is MS SQL. There are slight function differences for non-standard functions but SQL is suppose to be universal.