x
login about faq Site discussion (meta-askssc)

query question

I have 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],
 c.[sum np gifts],
 c.[sum soft credit]
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],
                           Sum(ammt) [Sum Soft Credit]
                    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 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; 

everything was find until I added [Sum Soft Credit], for some people it is right but for some people I get nulls for life smallest amount or last amount randomly, what could this be?

more ▼

asked Jun 29 '12 at 06:58 PM in Default

muk gravatar image

muk
300 8 23 26

woah I actually just realized that even before adding this field, some people have nulls for smallest amount when they have a largest amount and last gift, something is wrong with this query, help!

Jun 29 '12 at 07:02 PM muk
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You are doing a sum on the ammt but aren't handling null values. If you try to sum up anything with a null you'll get null.

more ▼

answered Jun 29 '12 at 07:57 PM

SirSQL gravatar image

SirSQL
4.6k 1 3

@SirSQL, that's not true.. You will receive NULL only in EXPRESSIONS.

SELECT 1 + NULL + 2 returns NULL

However

WITH Data AS (
 SELECT 1 AS N
 UNION ALL SELECT NULL
 UNION ALL SELECT 2
 UNION ALL SELECT NULL
 UNION ALL SELECT 5
)
SELECT
 SUM(N)
FROM Data

Returns correctly 8.

Jun 29 '12 at 08:02 PM Pavel Pawlowski

I stand corrected

Jun 29 '12 at 08:08 PM SirSQL

so any insight on what may be causing my issue?

Jun 29 '12 at 08:41 PM muk

I am with Pavel on this... NULL values are ignored while summing up. You also receive the NULL elimination warning. From BOL

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM > can be used with numeric columns only. Null values are ignored

Jul 02 '12 at 07:08 AM Sacred Jewel

I see what you are saying; however, this is not my issue. The sums actually give me the correct values. I am getting the incorrect values for smallest amount and last amount

Jul 02 '12 at 03:24 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.

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x913
x321

asked: Jun 29 '12 at 06:58 PM

Seen: 365 times

Last Updated: Jul 02 '12 at 03:24 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.