Reporting Sales

Congratulations to Sergey Koshkin, senior software developer for Softmatics, Inc. in Syktyvkar, Russia, and Quentin Ran, an independent consultant in Houston, Texas. Sergey won first prize of $100 for the best solution to the February Reader Challenge, "Reporting Sales." Quentin won second prize of $50. Here’s a recap of the problem and the solution to the February Reader Challenge.

Problem:

Karen is a SQL Server developer at a Web-hosting company that uses SQL Server 2000 and 7.0 to host several sales databases. The Purchases table in each sales database captures items that a company sells. The Purchases table schema, with the relevant columns, is

CREATE TABLE Purchases (
SaleID int NOT NULL IDENTITY PRIMARY KEY,
SaleAmount money NOT NULL,
SaleTime smalldatetime NOT NULL
)
CREATE INDEX idx_saletime ON Purchases( SaleTime )

-- Sample data:
INSERT INTO Purchases (SaleAmount, SaleTime)
        VALUES (6.24, 'Jan 1, 2002 8:22 am')
INSERT INTO Purchases (SaleAmount, SaleTime)
        VALUES (22.29, 'Jan 1, 2002 10:42 pm')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (128.40, 'Jan 2, 2002 11:12 am')
INSERT INTO Purchases (SaleAmount, SaleTime)
        VALUES (0.45, 'Jan 3, 2002 6:28 pm')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (16.32, 'Jan 4, 2002 11:41 am')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (9.11, 'Jan 6, 2002 6:30 pm')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (69.96, 'Jan 6, 2002 6:55 pm')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (6.99, 'Jan 6, 2002 8:23 pm')
INSERT INTO Purchases (SaleAmount, SaleTime) 
        VALUES (18.43, 'Jan 7, 2002 11:42 am')

Karen needs to generate a report from each database’s Purchases table that shows the following for a given month of the year:

Day of the month, in the format MM/DD, that an item was purchased
Number of items sold each day
Total amount sold each day

In addition, the report must include all the days between the first and last sales date of the month, and the output must be ordered by the day of the month. Help Karen produce this report efficiently.

Solution:

Karen first simplifies the querying process by building a Calendar table that contains dates and that can contain additional attributes, such as week number or localized month names that she can use in her reporting. She builds a Calendar table that contains dates for the year 2002, as follows:

CREATE TABLE Calendar ( dt smalldatetime NOT NULL PRIMARY KEY )
INSERT INTO Calendar ( dt )
SELECT cast( '2002-1-1' AS smalldatetime ) + count(*) - 1
 FROM master..sysobjects o1
 JOIN master..sysobjects o2
  ON o2.id 

For January for example, Karen can easily obtain all the days in a given month by using the following statements: 

DECLARE @Date smalldatetime, @MonthStart
 smalldatetime, @MonthNext smalldatetime
SET @Date = '2002-1-1'
SET @MonthStart = CONVERT( varchar,
	 @Date - day(@Date) + 1, 112 )
SET @MonthNext = dateadd( month, 1, @MonthStart )

SELECT d.dt
 FROM Calendar AS d
 WHERE d.dt >= @MonthStart
AND d.dt 

Karen can use the same logic to obtain all the days between the first and the last purchase for a given month by using the following query: 

DECLARE @Date smalldatetime, @MonthStart
 smalldatetime, @MonthNext smalldatetime
SET @Date = '2002-1-1'
SET @MonthStart = CONVERT( varchar, @Date - day(@Date) + 1, 112 )
SET @MonthNext = dateadd( month, 1, @MonthStart )

SELECT d.dt, LEFT( CONVERT( varchar, d.dt, 101 ), 5 ) AS MMDD
 FROM Calendar AS d
 WHERE d.dt >= @MonthStart
  AND d.dt = @MonthStart AND 
	     s.SaleTime 

She can obtain the remaining report details by using the following query:

DECLARE @Date smalldatetime, @MonthStart 
 smalldatetime, @MonthNext smalldatetime
SET @Date = '2002-1-1'
SET @MonthStart = CONVERT( varchar, @Date - day(@Date) + 1, 112 )
SET @MonthNext = dateadd( month, 1, @MonthStart )

SELECT d.MMDD,
   COALESCE( count( p.SaleId ), 0 ) AS Items,
   COALESCE( sum( p.saleamount ), 0 ) AS SaleTotal
FROM Purchases AS p
RIGHT JOIN (
   SELECT dt, LEFT( CONVERT( varchar, d.dt, 101 ), 5 ) AS MMDD
    FROM Calendar AS d
   WHERE d.dt >= @MonthStart
    AND d.dt = @MonthStart AND s.SaleTime = d.dt AND p.SaleTime 

The RIGHT JOIN in this query ensures that the output includes all the days in a month. And by counting the SaleID value column, Karen can determine the number of items purchased in a day. In this case, we want to count only the actual number of sales for each month and because of the OUTER JOIN, COUNT (*) will return at least 1; COUNT( SaleID ) instead returns zero for days that don’t have purchases, which eliminates NULL values.
	Karen could also use a derived table in the query to obtain the days of the month between the first and last purchase date. The query in the derived table returns all the days from the Calendar table that are within the first and last purchase dates using the EXISTS sub-query. However, the derived-table technique can be slightly inefficient because of the correlated EXISTS sub-query. Initially determining the first and last purchase dates for the period and joining against those directly can avoid this. The following SELECT statement uses a derived table "s" to filter the rows from the Calendar table and determine the purchases for each day.

DECLARE @Date smalldatetime, @MonthStart 
  smalldatetime, @MonthNext smalldatetime
SET @Date = '2002-1-1'
SET @MonthStart = CONVERT( varchar, @Date - day(@Date) + 1, 112 )
SET @MonthNext = dateadd( month, 1, @MonthStart )

SELECT LEFT( CONVERT( varchar, d.dt, 101 ), 5 ) AS MMDD,
   COALESCE( count( p.SaleId ), 0 ) AS Items,
   COALESCE( sum(saleamount), 0 ) AS SaleTotal
 FROM Calendar AS d
 JOIN (
    SELECT CONVERT( varchar, min( p1.SaleTime ), 112 ),
	 CONVERT( varchar, max( p1.SaleTime ), 112 )
     FROM Purchases AS p1
     WHERE p1.SaleTime >= @MonthStart AND p1.SaleTime = d.dt AND p.SaleTime = @MonthStart AND d.dt 


MARCH READER CHALLENGE: 


Now, test your SQL Server savvy in the March Reader Challenge, "Recent Orders" (below). Submit your solution in an email message to [email protected] by February 20. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Jack is a database programmer at a company that sells products online. He  writes SQL queries and stored procedures for reports based on user orders. He needs to provide a report that lists the two most recent orders placed by each user. The details of each order are stored in a table that the following code creates:

CREATE TABLE order_details (
userid     int NOT NULL,
ordernum   int NOT NULL,
orderdate  datetime NOT NULL,
shipdate datetime NOT NULL,
PRIMARY KEY ( userid, ordernum ),
UNIQUE ( userid, orderdate )
)
-- Sample data:
INSERT INTO order_details VALUES( 1, 1, '7/1/01', '7/5/01' )
INSERT INTO order_details VALUES( 1, 2, '7/10/01', '7/7/01' )
INSERT INTO order_details VALUES( 1, 3, '7/5/01', '7/2/01' )
INSERT INTO order_details VALUES( 2, 4, '8/1/01', '9/5/01' )
INSERT INTO order_details VALUES( 3, 5, '10/10/01', '10/7/01' )
INSERT INTO order_details VALUES( 3, 6, '10/1/01', '10/2/01' )
INSERT INTO order_details VALUES( 3, 7, '10/24/01', '10/30/01' )
INSERT INTO order_details VALUES( 3, 8, '9/24/01', '9/30/01' )

Help Jack write a query that will return a row that contains the order number, ship date, and order date of the two most recent orders for each user.

MARCH READER CHALLENGE:

Comments

Plain text