- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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?

t-sqlquery
Comment

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

**10** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy