If Value is Null set to zero - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If Value is Null set to zero

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 09:59:30

Have the following Code which currently adds up trx's for a month for a certain account(reknr)
If there are no trx's for a certain month I would like to set the Amount to zero. Currently it just doesn't return a record for that account(reknr) if there are no trx's.
I still want to see the field reknr if there is an amount for it or not.


SELECT     TOP (100) PERCENT dbo.gbkmut.reknr, SUM(dbo.gbkmut.bdr_hfl) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS Year, 
                      MONTH(dbo.gbkmut.datum) AS Month
FROM         dbo.gbkmut LEFT OUTER JOIN
                      dbo.grtbk ON dbo.gbkmut.reknr = dbo.grtbk.reknr
GROUP BY dbo.gbkmut.reknr, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.transtype
HAVING      (dbo.grtbk.bal_vw = 'W') AND (dbo.gbkmut.transtype <> 'V')
ORDER BY 'Year', Month

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 11:55:23

I've tried this, but it only returns one of my Accounts that have a zero amount. There should be 8 others.

SELECT TOP (100) PERCENT dbo.gbkmut.reknr, ISNULL(SUM(dbo.gbkmut.bdr_hfl),0) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS Year,
MONTH(dbo.gbkmut.datum) AS Month

I've also tried changing my links with no luck. The grtbk table holds all of my account numbers. I did a left outer join on that table but still doesn't seem to work.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 14:22:19

Since you reference the outer table in your having clause you have, in effect, turned your left OUTER join into an INNER join. Move the "transtype <> 'V' code to the JOIN criteria.

Be One with the Optimizer
TG

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 14:52:51

I've tried this, still not getting what I'm looking for. :
SELECT TOP (100) PERCENT dbo.gbkmut.reknr, ISNULL(SUM(dbo.gbkmut.bdr_hfl), 0) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS Year,
MONTH(dbo.gbkmut.datum) AS Month
FROM dbo.grtbk LEFT OUTER JOIN
dbo.gbkmut ON dbo.grtbk.reknr = dbo.gbkmut.reknr AND (dbo.gbkmut.transtype <> 'V')
GROUP BY dbo.gbkmut.reknr, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.transtype
HAVING dbo.grtbk.bal_vw = 'W'
ORDER BY 'Year', Month

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 15:00:15

Here is what the data looks like:


grtbk is just basically an account list:
reknr
10001
10002
10003
10004.....

gbkmut holds the trx data
reknr   bdr_hfl(amount)    datum
10001     400              4/29/2009
10001     500              4/15/2009
10002     300              4/15/2009
10004     100              4/20/2009

With the two examples of data above I want to see:
reknr    amount   month  year
10001    900       4     2009
10002    300       4     2009
10003    0         4     2009
10004    100       4     2009

Hope this helps

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 15:19:47

>>Hope this helps
Yes, but it would have been nicer if you provided executable DDL so we don't have to re-type everything :)

The Month and Year can't be filled in for 10003 because there is no data in the trx data table for that reknr.


declare @grtbk table (reknr int, bal_vw char(1))
insert @grtbk
select 10001, 'W' union all
select 10002, 'W' union all
select 10003, 'W' union all
select 10004, 'W'

declare @gbkmut table (reknr int, bdr_hfl int, datum datetime, transtype char(1))
insert @gbkmut
select 10001,     400,              '4/29/2009', 'a' union all
select 10001,     500,              '4/15/2009', 'a' union all
select 10002,     300,              '4/15/2009', 'a' union all
select 10004,     100,              '4/20/2009', 'a'

SELECT r.reknr
       ,ISNULL(SUM(b.bdr_hfl), 0) AS Amount
       --,r.bal_vw
       ,MONTH(b.datum) AS Month
       ,YEAR(b.datum) AS Year
FROM   @grtbk r
LEFT OUTER JOIN @gbkmut b
       ON r.reknr = b.reknr 
       AND (b.transtype <> 'V')
where  r.bal_vw = 'W'
GROUP BY r.reknr
       --,r.bal_vw
       ,YEAR(b.datum)
       ,MONTH(b.datum)
       ,b.transtype
ORDER BY r.reknr, 'Year', Month

OUTPUT:
reknr       Amount      Month       Year
----------- ----------- ----------- -----------
10001       900         4           2009
10002       300         4           2009
10003       0           NULL        NULL
10004       100         4           2009

Be One with the Optimizer
TG

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 15:31:08

I really need the Month and Year. Would it be possible it I had a table to link to that just had dates in it? I was playing around with that idea but still was having trouble.

So I could list all the dates in the date table and link to the trx table, then if there are no trx for a date in my date table I could show zero. I found a script to create a calendar table which basically lists every date for a range you specifiy.

Table was called Calendar and had a field called dt which listed every date from 01/01/1995 thru 12/31/2030

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 15:43:26

Yes, you could cross join all dates (one row for each month/year) with your grtbk table so every reknr has a row for each date. Then instead of selecting dates from gbkmut you can use the date table. You can use any of the methods discussed in your other topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133556

Each time you run it you will just need to know for what date range you want to return data. If you need help with the query then don't forget to post that info as well...

Be One with the Optimizer
TG

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 15:51:07

Thanks for your help, I will look into the cross join and see what I can come up with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources