x

query help please

Hello everyone, I have this query that reports data on a specific donor (I work at a school) . It includes their largest donation, last donation, first donation, and sums of donations. I need to do two things: I need to change it to report this information for all donors in the table not just the ID I have specified and I need to add a collumn to compute the sum of all gifts of type 'NP'. The problem is that for all other computed collumns I only needed information on gifts of type 'PP' or 'GF' so I dont know how to add this one collumn. Any help is greatly appreciated. Thank you. And thanks to those who have already helped me come up with parts of this query.

select
    pv.ID, pv.Prefix, pv.[First Name], pv.[Middle Name], 
    pv.[Last Name], pv.[Company Name], pv.[Address Line 1], 
    pv.[Address Line 2], pv.[Address Line 3], pv.City, 
    pv.[State], pv.Zip, pv.[Address Type], pv.[Preferred Email], 
    pv.Cell, pv.Home, c.[Life Largest Amount],
    c.[Largest Amount Date],
    c.[Life Smallest Amount], c.[Smallest Amount Date],
    c.[Last Amount], c.[Last Amount Date],
    c.[First Amount], c.[First Amount Date],c.[Sum of Gifts], c.[Sum Paid to Date]
    from PREFERRED_MAIL_VIEW pv cross apply
    (
        select
            max(case when L = 1 then amt else null end) [Life Largest Amount],
            max(case when L = 1 then CONTRIB_DATE else null end) [Largest Amount Date],
            max(case when S = 1 then amt else null end)  [Life Smallest Amount],
            max(case when S = 1 then CONTRIB_DATE else null end) [Smallest Amount Date],
            max(case when D=1 then amt else null end) [Last Amount],
            max(case when D=1 then CONTRIB_DATE else null end) [Last Amount Date],
            max(case when A=1 then amt else null end) [First Amount],
            max(case when A=1 then CONTRIB_DATE else null end) [First Amount Date],
            SUM(CASE WHEN CONTRIB_TYPE = 'GF' THEN amt ELSE 0 END) [Sum of Gifts],
            SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN amt ELSE 0 END) [Sum Paid to Date]
            from 
            (
                select 
                    cdd.CONDES_HARD_CREDIT_AMT amt, c.CONTRIB_DATE,
                    row_number() over (order by CONDES_HARD_CREDIT_AMT desc) L,
                    row_number() over (order by CONDES_HARD_CREDIT_AMT asc) S,
 row_number() over (order by CONTRIB_DATE desc) D,
                   row_number() over (order by CONTRIB_DATE asc) A,
                 CONTRIB_TYPE
                from CONTRIB_DONOR cd inner join CONTRIB_DONOR_DESIG cdd
                    on cd.CONTRIB_DONOR_ID = cdd.CONDES_CONTRIB_DONOR
                inner join CONTRIBUTION c
                    on cdd.CONDES_CONTRIBUTION = c.CONTRIBUTION_ID
                where cd.COND_DONOR = pv.ID and (c.CONTRIB_TYPE = 'GF' OR c.CONTRIB_TYPE = 'PP')
            ) t
    ) c
where pv.ID = '0000959';
more ▼

asked Jun 26, 2012 at 04:31 PM in Default

muk gravatar image

muk
400 30 33 35

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Since you need a report for all users instead of the specified one, just remove the where pv.ID = '0000959' part. Adding a sum of all donations of type NP will require a small change in the code. Because you need to consider this type as well, it needs toi be added to the list of contrib_type values, currently listing only GF and PP. On the other hand, because other calculations should include only those types, you need to explicitly filter the other type in your case statements. For example, I remember that to calculate the largest amount you originally wanted to only consider GF types. If this is the case then you need to adjust the case statement accordingly. Currently, this line reads:

max(case when L = 1 then amt else null end) [Life Largest Amount],

If you need to consider only the amounts for contrib_type = 'GF' then this line should instead read this:

max(case when L = 1 and CONTRIB_TYPE = 'GF' then amt else null end) 
[Life Largest Amount],

etc. This adjustment aside, here is the query which reports for all users and includes the additionaly column you need:

select
    pv.ID, pv.Prefix, pv.[First Name], pv.[Middle Name], 
    pv.[Last Name], pv.[Company Name], pv.[Address Line 1], 
    pv.[Address Line 2], pv.[Address Line 3], pv.City, 
    pv.[State], pv.Zip, pv.[Address Type], pv.[Preferred Email], 
    pv.Cell, pv.Home, c.[Life Largest Amount],
    c.[Largest Amount Date],
    c.[Life Smallest Amount], c.[Smallest Amount Date],
    c.[Last Amount], c.[Last Amount Date],
    c.[First Amount], c.[First Amount Date],
    c.[Sum of Gifts], c.[Sum Paid to Date],
    c.[Sum NP Gifts]
    from PREFERRED_MAIL_VIEW pv cross apply
    (
        select
            max(case when L = 1 then amt else null end) [Life Largest Amount],
            max(case when L = 1 then CONTRIB_DATE else null end) [Largest Amount Date],
            max(case when S = 1 then amt else null end)  [Life Smallest Amount],
            max(case when S = 1 then CONTRIB_DATE else null end) [Smallest Amount Date],
            max(case when D=1 then amt else null end) [Last Amount],
            max(case when D=1 then CONTRIB_DATE else null end) [Last Amount Date],
            max(case when A=1 then amt else null end) [First Amount],
            max(case when A=1 then CONTRIB_DATE else null end) [First Amount Date],
            SUM(CASE WHEN CONTRIB_TYPE = 'GF' THEN amt ELSE 0 END) [Sum of Gifts],
            SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN amt ELSE 0 END) 
            [Sum Paid to Date],
            sum(case when CONTRIB_TYPE = 'NP' then amt else null end) 
            [Sum NP Gifts]
            from 
            (
                select 
                    cdd.CONDES_HARD_CREDIT_AMT amt, c.CONTRIB_DATE,
                    row_number() over (order by CONDES_HARD_CREDIT_AMT desc) L,
                    row_number() over (order by CONDES_HARD_CREDIT_AMT asc) S,
                    row_number() over (order by CONTRIB_DATE desc) D,
                    row_number() over (order by CONTRIB_DATE asc) A,
                    CONTRIB_TYPE
                from CONTRIB_DONOR cd inner join CONTRIB_DONOR_DESIG cdd
                    on cd.CONTRIB_DONOR_ID = cdd.CONDES_CONTRIB_DONOR
                inner join CONTRIBUTION c
                    on cdd.CONDES_CONTRIBUTION = c.CONTRIBUTION_ID
                where cd.COND_DONOR = pv.ID 
                    and (c.CONTRIB_TYPE in ('GF', 'NP', 'PP'))
            ) t
    ) c;
Oleg
more ▼

answered Jun 26, 2012 at 05:19 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

wow thank you so much again Oleg. You have come through for me twice and I really appreciate it!
Jun 26, 2012 at 06:02 PM muk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x582
x369
x343

asked: Jun 26, 2012 at 04:31 PM

Seen: 836 times

Last Updated: Jun 26, 2012 at 06:59 PM