We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL SELECT Statement: weekly sales report

camelo1972
camelo1972 asked
on
Medium Priority
2,217 Views
Last Modified: 2008-01-09
I am using SQL Server 2000 with all of the latest service packs on Windows Server 2003 with all of the latest service packs.

I have a table called "sales" which looks like:
<pre>
| product_name | amount_sold | sale_date  |
--------------------------------------------
| Product_a    | 2           | 2006/10/22 |
| Product_b    | 12          | 2006/10/24 |
| Product_a    | 10          | 2006/10/27 |
| Product_c    | 5           | 2006/10/26 |
| Product_c    | 1           | 2006/10/23 |
... and so on
</pre>
I want to generate a report that lists the total number of each product sold on each day of the week over the course of a single week.  An example of the table I hope to generate is:
<pre>
| Product Name | S | M | Tu | W | Th | F | Sa |
-----------------------------------------------
| Product_a    | 2 | 2 | 2  | 3 | 10 | 10| 10 |
| Product_b    | 4 | 0 | 12 | 8 | 14 | 0 | 9  |
| Product_c    | 1 | 1 | 2  | 6 | 5  | 7 | 8  |
</pre>
The SQL statement I have come up with gives me:  
<pre>
| Product Name | S | M | Tu | W | Th | F | Sa |
-----------------------------------------------
| Product_a    |   |   |    |   |    |   | 39 |
| Product_b    |   |   |    |   |    |   | 47 |
| Product_b    |   |   |    |   |    |   | 30 |
</pre>
Here it is the SQL statement:

SELECT sales.product_name,
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 1 THEN SUM(sales.amount_sold) END AS [S],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 2 THEN SUM(sales.amount_sold) END AS [M],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 3 THEN SUM(sales.amount_sold) END AS [Tu],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 4 THEN SUM(sales.amount_sold) END AS [W],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 5 THEN SUM(sales.amount_sold) END AS [Th],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 6 THEN SUM(sales.amount_sold) END AS [F],
CASE DATEPART(weekday,MAX(sales.sale_date)) WHEN 0 THEN SUM(sales.amount_sold) END AS [Sa]
FROM sales
WHERE sales.sale_date >= @dateLow AND sales.sale_date <= @dateHigh
GROUP BY sales.product_name
ORDER BY sales.product_name

Any ideas on how to modify my statement?
Comment
Watch Question

Commented:
Try this
Select      Distinct
      sales.product_name,
      Case DW when 1 then amount_sold end S,
      Case DW when 2 then amount_sold end M,
      Case DW when 3 then amount_sold end Tu,
      Case DW when 4 then amount_sold end W,
      Case DW when 5 then amount_sold end Th,
      Case DW when 6 then amount_sold end F,
      Case DW when 7 then amount_sold end Sa
From      
(      SELECT       sales.product_name,
            DatePart(dw,sales.sale_date) DW,
            Sum(sales.amount_sold) amount_sold
      FROM Sales
      WHERE sales.sale_date >= @dateLow AND sales.sale_date <= @dateHigh
      GROUP BY sales.product_name,DatePart(dw,sales.sale_date)
      ) As sales
ORDER BY sales.product_name
Commented:
sorry I just tested my last solution was not correct one

try this
Select      Distinct
      sales.Product_Name,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=1 ) S,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=2 ) M,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=3 ) Tu,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=4 ) W,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=5 ) Th,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=6 ) F,
      (      Select       sum(amount_sold) from Sales A
            where       A.Product_Name=sales.Product_Name and
                  A.sale_date Between dateLow and dateHigh and
                  datepart(dw,A.sale_date)=7 ) Sa
From      Sales
Inner Join
      (Select @dateLow as dateLow, @dateHigh as dateHigh ) as Dates
ON      sales.sale_date Between dateLow and dateHigh

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT sales.product_name,
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 1 THEN sales.amount_sold ELSE 0 END) AS [S],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 2 THEN sales.amount_sold ELSE 0 END) AS [M],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 3 THEN sales.amount_sold ELSE 0 END) AS [Tu],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 4 THEN sales.amount_sold ELSE 0 END) AS [W],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 5 THEN sales.amount_sold ELSE 0 END) AS [Th],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 6 THEN sales.amount_sold ELSE 0 END) AS [F],
    SUM(CASE WHEN DATEPART(weekday, sales.sale_date) = 7 THEN sales.amount_sold ELSE 0 END) AS [Sa]
FROM sales
WHERE sales.sale_date >= @dateLow AND sales.sale_date <= @dateHigh
GROUP BY sales.product_name
ORDER BY sales.product_name
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.