Sales Report

  • I have written a sales report for the Northwind db and am looking for the best way of doing it.

    The sql below generates a report that counts the number of orders before 1997 and after 1997 for each employee.

    Is there a better way of doing it?

    thanks in advance,

    Billy

    ------------ cut here ------------

    use Northwind

    SELECT

    EMPLOYEEID,

    SUM(COUNT_BEFORE_97) AS TOT_BEFORE_97,

    SUM(COUNT_AFTER_97) AS TOT_AFTER_97

    FROM (

    SELECT

    EMPLOYEEID,

    CASE WHEN ORDERDATE < '1/1/1997' THEN COUNT(ORDERID) ELSE 0 END AS COUNT_BEFORE_97,

    CASE WHEN ORDERDATE >= '1/1/1997' THEN COUNT(ORDERID) ELSE 0 END AS COUNT_AFTER_97

    FROM

    ORDERS

    GROUP BY

    EMPLOYEEID, ORDERDATE

    ) THE_DATA

    GROUP BY EMPLOYEEID;

    ------------ cut here ------------

    The results should look like this:

    EMPLOYEEID TOT_BEFORE_97 TOT_AFTER_97

    ----------- ------------- ------------

    1 26 97

    2 16 80

    3 18 109

    4 31 125

    5 11 31

    6 15 52

    7 11 61

    8 19 85

    9 5 38

  • I'm sure someone else has a slicker way, but here's mine:

    SELECT A.EMPLOYEEID, COUNT_BEFORE_97, COUNT_AFTER_97

    FROM (

    SELECT EMPLOYEEID, COUNT(ORDERID) AS COUNT_BEFORE_97

    FROM ORDERS

    WHERE ORDERDATE < '1/1/1997'

    GROUP BY EMPLOYEEID

    ) A FULL OUTER JOIN (

    SELECT EMPLOYEEID, COUNT(ORDERID) AS COUNT_AFTER_97

    FROM ORDERS

    WHERE ORDERDATE >= '1/1/1997'

    GROUP BY EMPLOYEEID

    ) B ON A.EMPLOYEEID = B.EMPLOYEEID

    ORDER BY A.EMPLOYEEID

    Note that you need the FULL OUTER JOIN in case an employee only has sales in one of the criteria (but this example has none like that).

    Jay


    Jay Madren

  • thanks Jay for your response.

    Yes, the full outer join is necessary for those employees that may not have order records in the one of the time frame.

    Your sql statement looks neater but requires one additional scan on the orders table than the original one.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply