CODEWITHSUNDEEP
sqlsql-server
MrPickle5
MrPickle5

Reputation: 522

Better ways to write this SQL Server query?

Trying to display the sales for some dates. Columns should be those said dates.

Got it to work, but the code is so messy and complicated that my instinct tells me there has to be an easy way to write this.

Can anyone point me in the right direction or come up with a more elegant solution?

Query

SELECT
     (SELECT Jul10.TotalSales
    FROM   
     (SELECT sale.SaleDate, SUM (sale_bow.Qty * bow.Price) As TotalSales
    FROM sale 
         INNER JOIN sale_bow ON sale_bow.SaleID = sale.SaleID 
         INNER JOIN bow ON bow.BowID = sale_bow.BowID GROUP BY sale.SaleDate) As Jul10
    WHERE Jul10.SaleDate ='1999-07-10') AsJul10, 
    (SELECT Jul11.TotalSales
        FROM (SELECT sale.SaleDate, SUM (sale_bow.Qty * bow.Price) As TotalSales
        FROM sale 
            INNER JOIN sale_bow ON sale_bow.SaleID = sale.SaleID 
            INNER JOIN bow ON bow.BowID = sale_bow.BowID
GROUP BY sale.SaleDate) As Jul11 
    WHERE Jul11.SaleDate ='1999-07-11') As Jul11,
(SELECT Jul12.TotalSales
    FROM
(SELECT sale.SaleDate, SUM (sale_bow.Qty * bow. Price) As TotalSales 
    FROM sale 
        INNER JOIN sale_bow ON sale_bow.SaleID = sale.SaleID 
        INNER JOIN bow ON bow.BowID = sale_bow.BowID GROUP BY sale.SaleDate)
As Jul12 WHERE Jul12.SaleDate = '1999-07-12')
As Jul12, (SELECT Jul13.TotalSales
    FROM (SELECT sale.SaleDate, SUM (sale_bow.Qty * bow.Price) As TotalSales
    FROM sale
        INNER JOIN sale_bow ON sale_bow.SaleID = sale.SaleID
        INNER JOIN bow ON bow.BowID = sale_bow.BowID
GROUP BY sale.SaleDate) As Jul13 WHERE Jul13.SaleDate ='1999-07-13') As Jul13

Upvotes: 0

Views: 78

Answers (1)

JohnS
JohnS

Reputation: 2052

Try this:

SELECT
  Jul10 = SUM(CASE WHEN SaleDate = '1999-07-10' THEN sale_bow.Qty * bow.Price ELSE 0 END),
  Jul11 = SUM(CASE WHEN SaleDate = '1999-07-11' THEN sale_bow.Qty * bow.Price ELSE 0 END),
  Jul12 = SUM(CASE WHEN SaleDate = '1999-07-12' THEN sale_bow.Qty * bow.Price ELSE 0 END),
  Jul13 = SUM(CASE WHEN SaleDate = '1999-07-13' THEN sale_bow.Qty * bow.Price ELSE 0 END)
FROM
  sale
  INNER JOIN sale_bow ON sale_bow.SaleID = sale.SaleID
  INNER JOIN bow ON bow.BowID = sale_bow.BowID

Upvotes: 2

Related Questions