asked on

MSSQL Join Queries

Im trying to build a SQL Query for goldmine to grab each month's sum sale amount, but I cannot figure out how to run multiple 'where' inside of one query

SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'January' FROM OPMGR WHERE ((CLOSEDDATE >= '01/01/2011') AND (CLOSEDDATE <= '01/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'February' FROM OPMGR WHERE ((CLOSEDDATE >= '02/01/2011') AND (CLOSEDDATE <= '02/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'March' FROM OPMGR WHERE ((CLOSEDDATE >= '03/01/2011') AND (CLOSEDDATE <= '03/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'April' FROM OPMGR WHERE ((CLOSEDDATE >= '04/01/2011') AND (CLOSEDDATE <= '04/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'May' FROM OPMGR WHERE ((CLOSEDDATE >= '05/01/2011') AND (CLOSEDDATE <= '05/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'June' FROM OPMGR WHERE ((CLOSEDDATE >= '06/01/2011') AND (CLOSEDDATE <= '06/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'July' FROM OPMGR WHERE ((CLOSEDDATE >= '07/01/2011') AND (CLOSEDDATE <= '07/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'August' FROM OPMGR WHERE ((CLOSEDDATE >= '08/01/2011') AND (CLOSEDDATE <= '08/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'September' FROM OPMGR WHERE ((CLOSEDDATE >= '09/01/2011') AND (CLOSEDDATE <= '09/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'October' FROM OPMGR WHERE ((CLOSEDDATE >= '10/01/2011') AND (CLOSEDDATE <= '10/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'November' FROM OPMGR WHERE ((CLOSEDDATE >= '11/01/2011') AND (CLOSEDDATE <= '11/31/2011') AND (U_STAGE = '(Closed - Won)'));
SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'December' FROM OPMGR WHERE ((CLOSEDDATE >= '12/01/2011') AND (CLOSEDDATE <= '12/31/2011') AND (U_STAGE = '(Closed - Won)'));

Here are all the queries (the dates need to be changed on some ie. feb should b 2/28/2011), but I need it to run as ONE query, per USERID (still need to add AND userid=''), so it should show up as the following

USER | JAN | FEB | MAR | APR | MAY | JUNE | JULY | AUG | SEPT | OCT | NOV | DEC | TOTAL
user1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 35

Is there an actual way to do this or do I need to do seperate queries for every answer

twol

What version of SQL? This is a crosstab type report. Depending on the version of SQL, you can use a stored proc or a built in function:

SQL2008:
http://blog.sqlauthority.com/2008/10/01/sql-server-example-of-pivot-unpivot-cross-tab-query-in-different-sql-server-versions/

SQL2000:
http://www.mssqltips.com/tip.asp?tip=937

ASKER CERTIFIED SOLUTION

GMGenius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

facilitygateway

ASKER

Thank you GMGenius, that answer was exactly what I was looking for!

facilitygateway

ASKER

Is there another way to make the Month's different ?

ie.

SELECT 'Amy' AS Employee, ISNULL(SUM(CLOSEAMT), 0) AS 'January'
FROM OPMGR
WHERE (CLOSEDDATE >= '01/01/2011') AND (CLOSEDDATE <= '01/31/2011') AND (U_STAGE = '(Closed - Won)') AND (USERID = 'AMY')
UNION
SELECT 'Amy' AS Employee, ISNULL(SUM(CLOSEAMT), 0) AS 'February'
FROM OPMGR
WHERE (CLOSEDDATE >= '02/01/2011') AND (CLOSEDDATE <= '02/28/2011') AND (U_STAGE = '(Closed - Won)') AND (USERID = 'AMY')

It shows up as
Employee|January
rather than
Employee|January|February

twol

You should look at my post, and the links. A cross tab will do exactly what you want. And it will be flexible as you update your reporting.

GMGenius

Try this

SELECT ISNULL(SUM(CLOSEAMT), 0) AS 'January' ,
     (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '03/01/2011') AND (CLOSEDDATE <= '03/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'February',
     (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '03/01/2011') AND (CLOSEDDATE <= '03/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'March',
	 (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '04/01/2011') AND (CLOSEDDATE <= '04/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'April'


[repeat each month as above inside ()

FROM OPMGR WHERE ((CLOSEDDATE >= '01/01/2011') AND (CLOSEDDATE <= '01/31/2011') AND (U_STAGE = '(Closed - Won)'))

Open in new window

GMGenius

Or this should also work

SELECT (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '01/01/2011') AND (CLOSEDDATE <= '01/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'January',
       (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '03/01/2011') AND (CLOSEDDATE <= '03/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'February',
       (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '03/01/2011') AND (CLOSEDDATE <= '03/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'March',
       (SELECT ISNULL(SUM(CLOSEAMT), 0) FROM OPMGR WHERE ((CLOSEDDATE >= '04/01/2011') AND (CLOSEDDATE <= '04/31/2011') AND (U_STAGE = '(Closed - Won)'))) as 'April'

Open in new window

facilitygateway

ASKER

Alright,

Thanks again!