x

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, 2012 at 06:58 PM in Default

muk gravatar image

muk
400 32 34 37

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, 2012 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, 2012 at 07:57 PM

SirSQL gravatar image

SirSQL
4.8k 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, 2012 at 08:02 PM Pavel Pawlowski
I stand corrected
Jun 29, 2012 at 08:08 PM SirSQL
so any insight on what may be causing my issue?
Jun 29, 2012 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, 2012 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, 2012 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.

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:

x990
x374

asked: Jun 29, 2012 at 06:58 PM

Seen: 809 times

Last Updated: Jul 02, 2012 at 03:24 PM