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

avatar image

muk
440 33 35 40

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

avatar image

SirSQL
4.9k 4 5

@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.

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:

x1066
x425

asked: Jun 29, 2012 at 06:58 PM

Seen: 942 times

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

Copyright 2016 Redgate Software. Privacy Policy