Solved

SQL SELECT Statement: weekly sales report

Posted on 2006-11-06
5
2,063 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?
0
Comment
Question by:camelo1972
  • 2
5 Comments
 
LVL 11

Expert Comment

by:rw3admin
ID: 17882831
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
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 63 total points
ID: 17883002
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
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 62 total points
ID: 17883507
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
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now