July 9, 2012 at 3:07 pm
I used "Blahs" just for this example: One of these queries will sometimes add up to be zero (No payments were made that day in that market to add up) which makes it NULL so even though the second query will have a valid SUM of payments made that day the whole query will add up to NULL just because the first query added up to zero. Is there any way to make the first query to say 0 instead of null so it'll properly add the totals? 1st query adds up to zero and second query adds up to 100. I want it to say 0 + 100 = 100. Right now its saying: Null + 100 = NULL. Thanks a lot.
SELECT
(Select SUM (Amount) From blah1.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate <= '6/30/2012 23:59:59')
+
(Select SUM (Amount) From blah2.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate <= '6/30/2012 23:59:59')
AS "Blah + Blah"
July 9, 2012 at 3:10 pm
SELECT
(Select SUM (ISNULL(Amount,0)) From blah1.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate < '6/30/2012 23:59:59')
+
(Select SUM ((ISNULL(Amount,0)) From blah2.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate < '6/30/2012 23:59:59')
AS "Blah + Blah"
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2012 at 3:12 pm
at the top of your script set concat nulls on then off at bottom 🙂
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.
***The first step is always the hardest *******
July 9, 2012 at 3:26 pm
So like this? Sorry I have not used SET Command before. I'm pretty new to SQL. This didnt work in this format, but I think it is wrong the way I set it up. It says:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 65
Incorrect syntax near the keyword 'OFF'.
SET CONCAT_NULL_YIELDS_NULL {ON}
SELECT
(Select SUM (Amount) From Blah1.dbo.Paymenthistory)
+
(Select SUM (Amount) From Blah2.dbo.Paymenthistory)
As "Blah + Blah"
SET CONCAT_NULL_YIELDS_NULL {OFF}
July 9, 2012 at 3:30 pm
just use the script example I provided.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2012 at 3:35 pm
I tried and I really thought that was going to work but it didn't. Is this set up right? It still says my total = NULL. I'll give you my full queries to see if anything else if affecting that, but it shouldnt.
SELECT
(-- Florida CC Payments --)
Select SUM (ISNULL(Amount, 0)) as "Total Commodity Paid for Florida"
From Contracts.dbo.Paymenthistory
INNER JOIN Contracts.DBO.Serviceaddr
ON Contracts.dbo.Paymenthistory.ServiceAddrID = Contracts.dbo.Serviceaddr.ServiceaddrKey
where Contracts.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Contracts.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Contracts.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Contracts.dbo.Paymenthistory.amount !< '0'
and Contracts.dbo.Paymenthistory.BillDesc not like '%Reapplied%')
+
(-- Georgia CC Payments --)
Select SUM (ISNULL(Amount,0)) as "Total Commodity Paid for Georgia"
From Phase1.dbo.Paymenthistory
INNER JOIN Phase1.DBO.Serviceaddr
ON Phase1.dbo.Paymenthistory.ServiceAddrID = Phase1.dbo.Serviceaddr.ServiceaddrKey
where Phase1.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Phase1.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Phase1.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Phase1.dbo.Paymenthistory.amount !< '0'
and Phase1.dbo.Paymenthistory.BillDesc not like '%Reapplied%')
+
(-- NorthEast CC Payments --)
Select SUM (ISNULL(Amount,0)) as "Total Commodity Paid for NorthEast"
From NY.dbo.Paymenthistory
INNER JOIN NY.DBO.Serviceaddr
ON NY.dbo.Paymenthistory.ServiceAddrID = NY.dbo.Serviceaddr.ServiceaddrKey
where NY.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and NY.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and NY.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and NY.dbo.Paymenthistory.amount !< '0'
and NY.dbo.Paymenthistory.BillDesc not like '%Reapplied%'
and state != 'TX')
+
(-- Georgia Deposit Payments --)
Select SUM (ISNULL(Amount,0)) AS "Total Deposit Payments for Georgia"
From Phase1.dbo.AccountDepositActivity
JOIN Phase1.DBO.Serviceaddr
ON Phase1.dbo.AccountDepositActivity.ServiceAddrID = Phase1.dbo.Serviceaddr.ServiceaddrKey
where DepositType = '2'
And Phase1.dbo.AccountDepositActivity.PostDate >= '6/30/2012 00:00:00'
And Phase1.dbo.AccountDepositActivity.PostDate <= '6/30/2012 23:59:59'
And Phase1.dbo.AccountDepositActivity.UI = 'Adept - CS:creditcard')
As "Total Money From CC Payments For the Day"
July 9, 2012 at 4:28 pm
One of those subselects returns no data so it results in null. You can get around that with another isnull or a query rewrite. If you provided sample data and table structures, somebody could help rewrite that query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 10, 2012 at 3:42 am
looks like i stand corrected and you cant use SET CONCAT_NULL_YIELDS_NULL { ON | OFF } for calculations of int field so use isnull function 🙂
***The first step is always the hardest *******
July 10, 2012 at 1:50 pm
Ok so in this case, there were zero payments made for the first query. The query does not find any results, so it says the result is NULL.
First Query
Select SUM(ISNULL(Amount, 0)) From Contracts.dbo.Paymenthistory
where Contracts.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Contracts.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Contracts.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Contracts.dbo.Paymenthistory.amount !< '0'
and Contracts.dbo.Paymenthistory.BillDesc not like '%Reapplied%'
Results: NULL
(No payments made on june 30th 2012: No Results. If it were Select * From... then it would have no results)
The second query:
Select SUM(ISNULL(Amount, 0)) From Phase1.dbo.Paymenthistory
where Phase1.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Phase1.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Phase1.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Phase1.dbo.Paymenthistory.amount !< '0'
and Phase1.dbo.Paymenthistory.BillDesc not like '%Reapplied%'
Results: 7500.74
And if you change this query to say Select * From.... then the results for the Amount column are below which adds up to 7500.74. Thats 152 different payments adding up to 7500.74
45.97
88.21
41.19
57.61
58.30
50.05
28.70
35.72
32.52
31.54
52.35
67.37
36.28
35.95
28.83
42.19
60.46
79.97
35.86
62.95
40.96
60.27
32.72
109.21
122.90
44.77
39.30
45.00
43.35
47.66
31.19
28.87
79.84
136.96
4.95
50.52
9.34
125.70
70.57
32.53
81.47
97.31
35.26
68.49
88.68
6.43
26.04
39.84
30.56
105.19
76.59
8.05
41.88
34.39
67.27
36.13
31.43
22.97
42.32
52.44
32.78
29.45
34.92
33.55
2.45
32.23
0.01
55.67
38.12
37.62
12.63
44.10
52.50
38.58
93.11
57.52
67.87
43.49
40.68
38.81
60.63
47.78
100.00
79.74
36.63
41.71
79.95
54.86
40.91
95.19
28.67
60.87
47.56
5.17
79.95
43.38
29.97
33.21
35.45
51.85
47.80
35.41
35.84
35.21
50.43
31.85
51.14
28.69
35.48
50.66
41.01
49.98
41.04
27.34
38.89
17.66
41.65
50.33
45.80
30.38
34.18
38.44
38.55
62.79
16.37
36.93
37.88
47.74
53.44
59.95
30.25
39.07
40.13
46.57
37.07
114.36
86.83
47.37
29.38
109.21
65.95
73.06
41.87
42.95
117.36
28.67
41.33
100.00
97.36
43.37
30.81
63.97
Now If I were to add both queries together, it would say that NULL + 7500.74 = NULL which is my problem. I want it to know that there were zero payments made in the first query which in turn means the the amount is zero not NULL. I want it to say 0 + 7500.74 = 7500.74. IF THIS HELPS I'm not sure if I need to somehow convert the amount column to something else. The direct Object Explorer says that the amount column is this: Amount (decimal(18,2), null). There will be no amounts that = 0 in the actual data. I just want the sum of zero payments to = 0. Is this possible?
July 10, 2012 at 1:55 pm
As Jason already said you need to wrap your sum with an ISNULL.
Select ISNULL(SUM(ISNULL(Amount, 0)), 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2012 at 2:02 pm
Awesome. Jason had left out one ISNULL and that zero at the end. That did it. Perfect. Thank you Jason and Sean for your help. I appreciate it very much
July 10, 2012 at 2:35 pm
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 6:23 am
SGT_squeequal (7/10/2012)
looks like i stand corrected and you cant use SET CONCAT_NULL_YIELDS_NULL { ON | OFF } for calculations of int field so use isnull function 🙂
I know the solution for this thread went away from it eventually but it is worth mentioninng that it has been the recommendation since SQL 2005 to avoid using CONCAT_NULL_YIELDS_NULL in new development.
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) - SQL Server 2005
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply