ok i really need some help

Question

question

muk asked Jul 2, '12

ok i really need some help

I have this query that is supposed to get all of the information a donor makes (smallest amount, largest amount, first, last, sum of certain types of gifts, etc. I need to modify this a bit. Right now it gets the largest donation of hard credits, I need to get the largest donation hard or soft credit (there are two collumns: CONDES_HARD_CREDIT_AMT and CONDES_SOFT_CREDIT_AMT) I need the largest value from either for one person. Also, for some people if they have a soft credit entered, the value from the hard credit field is null or 0 therefore, the smallest amount or last amount is not correct because it will just be null or 0. The same applies for finding the sums. Can someone help me modify this query to correctly output largest amount, largest amount date, smallest amount, smallest amount date, last amount, last amount date, first amount, first amount date, sum of gifts of type 'GF', sum of gifts of type 'PP', sum of gifts of type 'NP', sum of all gifts, sum of soft credits Here is what I have so far: 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;

sql-server-2008 t-sql sql-server-2008-r2 query reporting

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-07-02T20:50:26Z

Oleg answered Jul 2, '12

I modified the query again so now it should give you correct results. The problem is that majority of records have values in only one of the 2 amount columns, but still there are records which have 0 in place of null in one of the two. It makes it look like when the donation is registered in the system, the user populates one of the columns but can also place a value of zero in another column. This means that you have to always get the useful amount from 2 columns and this will incidentally help you from running into problems when calculating gifts, NP gifts and sum of all gifts. I used coalesce function to get the useful value from hard amount, soft amount and zero. Additionally, I applied **nullif** function to the hard amount. This prevents you from erroneously grabbing the value of zero for those records which have zero in the hard amount and some positive amount in the soft amount column. Please check the query below and let me know if it works: 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 coalesce(nullif(cdd.condes_hard_credit_amt, 0), cdd.condes_soft_credit_amt, 0) amt, c.contrib_date, row_number() over (order by coalesce( nullif(cdd.condes_hard_credit_amt, 0), cdd.condes_soft_credit_amt, 0) desc) L, row_number() over (order by coalesce( nullif(cdd.condes_hard_credit_amt, 0), cdd.condes_soft_credit_amt, 0) 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; Hope this helps, Oleg

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Jul 02, 2012 at 09:21 PM

@muk I edited the answer a bit. Evidently what happens is that when you have NULL values from the aggregates they cannot really be compared to anything and so I applied **isnull** to all case statements to address this issue. With ANSI\_NULLS option set to ON as it should be, something like **NULL < 5** is neither true nor it is false. This is why you get incorrect info from the case statemenents. Please try updated query and let me know if it works. By the way, how do you format your queries? They certainly don't come out right even though all you have to do is copy the code from SSMS, paste it in your question (or answer), highlight the code and press Ctrl + K.

1 ·

muk commented · Jul 02, 2012 at 09:04 PM

Thank you Oleg! I tried this but I am getting no values at all for Largest, Smallest, and Last :-/

0 ·

Oleg commented · Jul 02, 2012 at 10:36 PM

@muk I updated the answer, it should work now (I hope).

0 ·

muk commented · Jul 03, 2012 at 02:05 AM

Thank you @Oleg! You save the day again!

0 ·

Answer 2 · 2012-07-02T21:08:50Z

muk answered Jul 2, '12

Ok I tweeked it a little to 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,
       CASE
         WHEN c.[hard largest amount] > c.[soft largest amount] THEN
         c.[hard largest amount]
         ELSE c.[soft largest amount]
       END [Life Largest Amount],
       CASE
         WHEN c.[hard largest amount] > c.[soft largest amount] THEN
         c.[hard largest amount date]
         ELSE c.[soft largest amount date]
       END [Largest Amount Date],
       CASE
         WHEN c.[hard smallest amount] < c.[soft smallest amount] THEN
         c.[hard smallest amount]
         ELSE c.[soft smallest amount]
       END [Life Smallest Amount],
       CASE
         WHEN c.[hard smallest amount] < c.[soft smallest amount] THEN
         c.[hard smallest amount date]
         ELSE c.[soft smallest amount date]
       END [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) [Hard Largest Amount],
                            Max(CASE
                                 WHEN l = 1 THEN contrib_date
                                 ELSE NULL
                               END) [Hard Largest Amount Date],
                            Max(CASE
                                 WHEN l_soft = 1 THEN ammt
                                 ELSE NULL
                               END) [Soft Largest Amount],
                            Max(CASE
                                 WHEN l_soft = 1 THEN contrib_date
                                 ELSE NULL
                               END) [Soft Largest Amount Date],
                            Max(CASE
                                 WHEN s = 1 THEN amt
                                 ELSE NULL
                               END) [Hard Smallest Amount],
                            Max(CASE
                                 WHEN s = 1 THEN contrib_date
                                 ELSE NULL
                               END) [Hard Smallest Amount Date],
                            Max(CASE
                                 WHEN s_soft = 1 THEN ammt
                                 ELSE NULL
                               END) [Soft Smallest Amount],
                            Max(CASE
                                 WHEN s_soft = 1 THEN contrib_date
                                 ELSE NULL
                               END) [Soft 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,
cdd.condes_soft_credit_amt                ammt,
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 condes_soft_credit_amt DESC) L_Soft,
Row_number()
  OVER (
    ORDER BY condes_soft_credit_amt ASC)  S_Soft,
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
WHERE   pv.id = '0000946'; so now I am getting the correct value for Largest but last and smallest are still blank. I suspect that this is because no it is getting the last and smallest values as null... hmmmmm

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

muk commented · Jul 02, 2012 at 09:09 PM

If the last amount is a soft amount, how would I modify this query to see that since it is getting the last amount by contribution date.

0 ·

Answer 3 · 2012-07-02T21:31:43Z

muk answered Jul 2, '12

@Oleg unfortunately I still got null for last amount and the wrong amount for largest, I got the hard credit amount not the soft credit amount. With the tweek I did previously I got the correct largest amount but the last and smallest amount are null so I combined my changes into your updated query and got this: 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, case when isnull(c.[Hard Largest Amount], 0) > isnull(c.[Soft Largest Amount], 0) then c.[Hard Largest Amount] else c.[Soft Largest Amount] end [Life Largest Amount], case when isnull(c.[Hard Largest Amount], 0) > isnull(c.[Soft Largest Amount], 0) then c.[Hard Largest Amount Date] else c.[Soft Largest Amount Date] end [Largest Amount Date], case when isnull(c.[Hard Smallest Amount], 0) > isnull(c.[Soft Smallest Amount], 0) then c.[Hard Smallest Amount] else c.[Soft Smallest Amount] end [Life Smallest Amount], case when isnull(c.[Hard Smallest Amount], 0) > isnull(c.[Soft Smallest Amount], 0) then c.[Hard Smallest Amount Date] else c.[Soft Smallest Amount Date] end [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) [Hard Largest Amount], max(case when L = 1 then CONTRIB_DATE else null end) [Hard Largest Amount Date], max(case when L_Soft = 1 then ammt else null end) [Soft Largest Amount], max(case when L_Soft = 1 then CONTRIB_DATE else null end) [Soft Largest Amount Date], max(case when S = 1 then amt else null end) [Hard Smallest Amount], max(case when S = 1 then CONTRIB_DATE else null end) [Hard Smallest Amount Date], max(case when S_Soft = 1 then ammt else null end) [Soft Smallest Amount], max(case when S_Soft = 1 then CONTRIB_DATE else null end) [Soft 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, cdd.CONDES_SOFT_CREDIT_AMT ammt, 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 CONDES_SOFT_CREDIT_AMT desc) L_Soft, row_number() over (order by CONDES_SOFT_CREDIT_AMT asc) S_Soft, 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 where pv.ID = '0000946'; btw, this ID is one I am using to check my results in case you were wondering. My result set from combining our queries still gives me blanks for last and smallest :(

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Jul 02, 2012 at 10:08 PM

@muk From what I see, there are 2 columns storing the credit amount and it appears that the same record can have value in only one of the two columns, that is, if the soft amount is null then there is a value in the hard amount. The opposite is true as well, that is if there is a value in the soft amount then the hard amount is null for the same record. If this is correct then the query can be simplified. Please let me know and I will update the query in my answer in few minutes.

0 ·

Answer 4 · 2012-07-02T21:43:00Z

muk answered Jul 2, '12

Here is some sample data: these are all of the contributions this woman made. [1]: /storage/temp/283-details.txt Her largest amount should be 65, last amount should be 65

details.txt (1.6 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question