January 21, 2003 at 12:30 pm
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
January 21, 2003 at 2:13 pm
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
January 21, 2003 at 2:52 pm
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