Help changing the query to display multiple column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help changing the query to display multiple column

Author

Topic

dsburton
Starting Member

6 Posts

Posted - 2005-06-13 : 04:57:32

Hi,

I have a query, which gives out the total pledge amt and total number of pledges per year

and month as follows...
SELECT E.FIRSTNAME, E.LASTNAME, E.EMPNUM, YEAR(A.PLEDGEDATE) AS PLEDGEYEAR,
MONTH(A.PLEDGEDATE) AS PLEDGEMONTH, SUM(A.PLEDGEAMT) AS PLEDGEAMT, COUNT(A.PLEDGEAMT)AS

NOOFPLEDGES
FROM DONATIONS A LEFT JOIN EMPLOYEE E ON A.CALLERNUM = E.EMPNUM
WHERE A.CALLERNUM IS NOT NULL
AND PLEDGEDATE >= '01/01/2005' AND PLEDGEDATE <= '05/31/2005'
GROUP BY E.EMPNUM, YEAR(A.PLEDGEDATE), MONTH(A.PLEDGEDATE), E.FIRSTNAME, E.LASTNAME
ORDER BY E.EMPNUM

Now, I need to change the query, as I have different types of pledges (ex. Type 'A' and

'B'), to display the "NOOFPLEDGES" as "NOOFAPLEDGES" and "NOOFBPLEDGES". I tried out using

different queries for each pledgetype and storing the values in a temp table in the

application. But the performance has been badly effected.

Can I get the result in a single query....
FIRSTNAME, LASTNAME, EMPNUM, PLEDGEYEAR, PLEDGEMONTH, PLEDGEAMT, NOOFAPLEDGES, NOOFBPLEDGES

Please Help!

Dorothy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-13 : 06:30:45

Try this


SELECT E.FIRSTNAME, E.LASTNAME, E.EMPNUM, YEAR(A.PLEDGEDATE) AS PLEDGEYEAR, 
MONTH(A.PLEDGEDATE) AS PLEDGEMONTH, SUM(A.PLEDGEAMT) AS PLEDGEAMT, 
SUM(CASE WEHN A.PLEDGEAMT='A' THEN 1 ELSE 0 END) AS NOOFAPLEDGES,
SUM(CASE WEHN A.PLEDGEAMT='B' THEN 1 ELSE 0 END) AS NOOFBPLEDGES FROM DONATIONS A LEFT JOIN EMPLOYEE E ON A.CALLERNUM = E.EMPNUM 
WHERE A.CALLERNUM IS NOT NULL
AND PLEDGEDATE >= '01/01/2005' AND PLEDGEDATE <= '05/31/2005'
GROUP BY E.EMPNUM, YEAR(A.PLEDGEDATE), MONTH(A.PLEDGEDATE), E.FIRSTNAME, E.LASTNAME
ORDER BY E.EMPNUM

Madhivanan

Failing to plan is Planning to fail

dsburton
Starting Member

6 Posts

Posted - 2005-06-13 : 06:34:40

Thanks! It worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources