- Home /

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?

Comment

**Answer** by SirSQL
·
Jun 29, 2012 at 07:57 PM

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.

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

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**

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

Sql 2005 Case Clause Query 2 Answers

why is my query eliminating nulls 1 Answer

Need some help Please 2 Answers

Issue using the "in Sub query" 3 Answers

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges