Reporting Book Sales

Congratulations to Narasimhan Jayachandran, a database management consultant for HTC Global Services in Troy, Michigan, and John Hanson, vice president of operations for MEDePass, Inc.. Narasimhan won first prize of $100 for the best solution to the February Reader Challenge, "Reporting Book Sales." John won second prize of $50. Here’s a recap of the problem and the solution to the February Reader Challenge.

Problem:

Phil generates reports for a sales team in a company that sells books. The company stores publication data for its books in a SQL Server 2000 database. Phil receives sales data updates in a Microsoft Excel file that has the following header labels for columns: stor_id, yr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec. The spreadsheet contains quantity of books sold in each store by year and month. You can generate sample data for the Excel spreadsheet from the Sales table in the Pubs database by using the following code:

SELECT s.stor_id, year(s.ord_date) AS yr,
    SUM(CASE month(s.ord_date) WHEN 1 THEN s.qty ELSE 0 END) AS Jan,
    SUM(CASE month(s.ord_date) WHEN 2 THEN s.qty ELSE 0 END) AS Feb,
    SUM(CASE month(s.ord_date) WHEN 3 THEN s.qty ELSE 0 END) AS Mar,
    SUM(CASE month(s.ord_date) WHEN 4 THEN s.qty ELSE 0 END) AS Apr,
    SUM(CASE month(s.ord_date) WHEN 5 THEN s.qty ELSE 0 END) AS May,
    SUM(CASE month(s.ord_date) WHEN 6 THEN s.qty ELSE 0 END) AS Jun,
    SUM(CASE month(s.ord_date) WHEN 7 THEN s.qty ELSE 0 END) AS Jul,
    SUM(CASE month(s.ord_date) WHEN 8 THEN s.qty ELSE 0 END) AS Aug,
    SUM(CASE month(s.ord_date) WHEN 9 THEN s.qty ELSE 0 END) AS Sep,
    SUM(CASE month(s.ord_date) WHEN 10 THEN s.qty ELSE 0 END) AS Oct,
    SUM(CASE month(s.ord_date) WHEN 11 THEN s.qty ELSE 0 END) AS Nov,
    SUM(CASE month(s.ord_date) WHEN 12 THEN s.qty ELSE 0 END) AS Dec
    FROM Sales AS s
    GROUP BY s.stor_id, year(s.ord_date)

Phil needs to import the data from the Excel file into a SQL Server table called StoreSalesSummary by unpivoting the month columns from the spreadsheet. You can create the StoreSalesSummary table by using the following code:

CREATE TABLE StoreSalesSummary (
    stor_id int NOT NULL,
    qty int NOT NULL,
    yr smallint NOT NULL,
    mn tinyint NOT NULL,
    PRIMARY KEY(stor_id, yr, mn)
    )

Help Phil import only the Excel spreadsheet data into the StoreSalesSummary table, then insert and update each store’s sales from the spreadsheet. Import only the stores with a nonzero quantity value for any month.

Solution:

Phil can use the OPENDATASOURCE() rowset function in SQL Server 2000 to read the Excel file data as a table. If the Excel file contains a worksheet called YearlySales, he can use the following code to read the Excel spreadsheet as a table:

SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\YearlySalesSummary.xls";User ID=Admin;Password=
    ;Extended properties=Excel 5.0')...\[YearlySales$\] s

Phil could also set up a linked server connection to the Excel file or use the OPENROWSET() function to read the data from the Excel file as a table.

Now Phil can manipulate the data from the Excel spreadsheet in a SQL Server table. The following code puts the Excel data into a temporary table called #Sales in the desired format:

SELECT s1.stor_id, s1.qty, s1.yr, s1.mn
    INTO #Sales
    FROM (
    SELECT s.stor_id,
    CASE m.mn
    WHEN 1 THEN s.\[Jan\]
    WHEN 2 THEN s.\[Feb\]
    WHEN 3 THEN s.\[Mar\]
    WHEN 4 THEN s.\[Apr\]
    WHEN 5 THEN s.\[May\]
    WHEN 6 THEN s.\[Jun\]
    WHEN 7 THEN s.\[Jul\]
    WHEN 8 THEN s.\[Aug\]
        WHEN 9 THEN s.\[Sep\]
    WHEN 10 THEN s.\[Oct\]
    WHEN 11 THEN s.\[Nov\]
    WHEN 12 THEN s.\[Dec\]
    END AS qty,
    s.yr,
    m.mn
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\YearlySalesSummary.xls";User ID=Admin;Password=
        ;Extended properties=Excel 5.0')...\[YearlySales$\] s
    CROSS JOIN (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL
    SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
    ) AS m(mn)
    ) s1
    WHERE s1.qty > 0.0
SELECT * FROM #Sales

The query’s cross join lets Phil generate a row for each month column. Once Phil converts the columns to rows, he can obtain the correct quantity value by using a CASE expression in the SELECT list, based on the pseudo month column, mn. Finally, the code’s WHERE clause filters the rows that have a nonzero qty value for each year and month combination.

Now Phil can use the data from the temporary table #Sales to update the data in the StoreSalesSummary table, as the following code shows:

-- Update the existing rows first:    
    UPDATE s2    
    SET qty = s2.qty + s1.qty    
    FROM #Sales AS s1    
    JOIN StoreSalesSummary AS s2    
    ON s2.stor_id = s1.stor_id    
    AND s2.yr = s1.yr    
    AND s2.mn = s1.mn

-- Add the new rows next:
INSERT INTO storesalessummary
SELECT s1.stor_id, s1.qty, s1.yr, s1.mn
    FROM #Sales AS s1
    WHERE NOT EXISTS(SELECT *
    FROM StoreSalesSummary AS s2
    WHERE s2.stor_id = s1.stor_id
    AND s2.yr = s1.yr
    AND s2.mn = s1.mn)
SELECT * FROM #Sales

Phil could also unpivot the Excel spreadsheet’s month columns by using a UNION ALL query. The following example assumes that Phil has dumped the data from the worksheet into a temporary table called #ExcelSales:

SELECT stor_id, yr, 1 AS mn, Jan AS qty
    FROM #ExcelSales
    WHERE Jan > 0
    UNION ALL
SELECT stor_id, yr, 2 AS mn, Feb AS qty
    FROM #ExcelSales
    WHERE Feb > 0
    UNION ALL
SELECT stor_id, yr, 3 AS mn, Mar AS qty
    FROM #ExcelSales
    WHERE Mar > 0
    UNION ALL
SELECT stor_id, yr, 4 AS mn, Apr AS qty
    FROM #ExcelSales
    WHERE Apr > 0
    UNION ALL
SELECT stor_id, yr, 5 AS mn, May AS qty
    FROM #ExcelSales
    WHERE May > 0
    UNION ALL
SELECT stor_id, yr, 6 AS mn, Jun AS qty
    FROM #ExcelSales
    WHERE Jun > 0
    UNION ALL
SELECT stor_id, yr, 7 AS mn, Jul AS qty
    FROM #ExcelSales
    WHERE Jul > 0
    UNION ALL
SELECT stor_id, yr, 8 AS mn, Aug AS qty
    FROM #ExcelSales
    WHERE Aug > 0
    UNION ALL
SELECT stor_id, yr, 9 AS mn, Sep AS qty
    FROM #ExcelSales
    WHERE Sep > 0
    UNION ALL
SELECT stor_id, yr, 10 AS mn, Oct AS qty
    FROM #ExcelSales
    WHERE Oct > 0
    UNION ALL
SELECT stor_id, yr, 11 AS mn, Nov AS qty
    FROM #ExcelSales
    WHERE Nov > 0
    UNION ALL
SELECT stor_id, yr, 12 AS mn, Dec AS qty
    FROM #ExcelSales
    WHERE Dec > 0

The cross-join technique typically performs better than the UNION ALL query for unpivoting operations. The cross-join involves less code and fewer joins on the main table. You can compare the performance of both solutions by looking at their execution plan costs, I/O statistics, and the time SQL Server takes to execute the queries.

MARCH READER CHALLENGE:

Now, test your SQL Server savvy in the March Reader Challenge, "Restoring a Database" (below). Submit your solution in an email message to [email protected] by February 19. 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.

Keith is the DBA for a company that runs several SQL Server 2000 data warehouses. He has a crucial database that contains the fact and dimension tables for the data warehouse. Keith performs full backups every week and periodic log backups after the database backup. The company’s development team has requested the latest copy of the database along with any log backups for testing purposes. The team needs to run ad hoc queries against the database at different points in time to collect statistics. Keith uses the following sequence of steps to create the database and log backups:

CREATE DATABASE DW

ALTER DATABASE DW SET recovery bulk_logged
RAISERROR ('-- Performing full backup...', 0, 1) WITH nowait

-- Full backup of database
BACKUP DATABASE DW TO DISK = 'c:\temp\DW.bak' WITH init
GO

-- Create table t1
CREATE TABLE DW..t1 ( i int IDENTITY )
INSERT INTO DW..t1 DEFAULT VALUES

-- Initial log backup
RASIERROR ('-- Initial log backup...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.1' WITH init

-- Create table t2 for bulk loading
CREATE TABLE DW..t2 ( c char( 8000 ) DEFAULT 'x' )
INSERT DW..t2 DEFAULT VALUES

-- Add new log file on a different volume because of space constraints
ALTER DATABASE DW ADD log FILE ( name = 'DW_TempLog' , filename = 'c:\temp\DW_TempLog.ldf' )

-- Bulk inserts and other operations here

-- Log backup after first ALTER DATABASE command
RAISERROR ('-- Log backup after first ALTER DATABASE...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.2' WITH init

-- Remove temporary log file
ALTER DATABASE DW REMOVE FILE 'DW_Templog'

-- Log backup after second ALTER DATABASE command
RAISERROR ('-- Log backup after second ALTER DATABASE...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.3' WITH init
DROP DATABASE DW
GO

Keith also needs to provide the commands for restoring the database (in read-only format) up to and including the latest log backup, DW.trn.3. Help Keith write the script to restore a read-only copy of the database after different backups have been restored on a development server.

Comments

Plain text