Fill in with $0 balance when period has no records

[awaria]

Working on a report that will show gl summary data by account, per year, per month.
Issue is when an account has no activity for a specified year and month, it doesn’t display since there is no records. However, I want to show a record with $0 balance for that specified year and month.
Line Item detail account data table has following columns;
account, trxdate, trxAmt, Year
If account 123 has records for Oct and Dec, but none in Nov, I want three records to render for account 123, sum by month(trxdate) and Year
Account | TrxDate | TrxAmt | Year
123 Oct $100 2011
123 Nov $0 2011
123 Dec $100 2011

How do I get a sum of TrxAmt by month(period) for months with no activity?

Thanks for your assistance.
awaria

 

[gmmastros]

Can you show some sample input data and your current query?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

you Will need a table of month and years and left join sum of line item to dates table

 

[gmmastros]

PWise,

I was originally thinking left join too, but that won't get you the account number. May need a full join here, which is kinda scary, so I thought I would ask for sample data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

I'm thinking Left Join and Coalesce.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[awaria]

I did create a table with year and period combinations, however, when I joined that to the data activity table, there still would not provide a blank record for a year and period(month) combination if the activity table did not havea record for that year and period. When a joined the two tables ON year, the reulst were a one-to-many situation. And when I added the period to the year, then the results were one-to-one, but there was no blank period since Period was used in the join ON and the activity table had no records for that period.

I will look again at my joins, but I think I attempted most alternatives.

 

[awaria]

I added account to my table with year ans period and now my join is resulting in a one-to-one record set with a blank record for periods without transactions.

Still have a one problem. Periodid does not exist in trx data table. Trx Date does. We are on fiscal year of July through June so July is our period 1. Need way to convert [trx date] betewen '2011-07-01' to '2011-07-31' to periodid of 1 and so on.
Month(trxdate) yields results of 7.
Added blank field of Periodid to trx data table. Need help in scripting to populate that field with periodid based upon review of [trx date] field so that year and periodid fields will match between tables.

July 2011 has year of 2012 and periodid of 1 based upon our fiscal calendar.

Open to suggestions of other strategies for resolving this issue.

Thanks,

awaria

 

[Onyxpurr]

I'm sure someone has a much better solution, but this is what I'd use:

create table #customers (Account int, TrxDate date, TrxAmt decimal(9,2))
insert into #customers values (123, '10/02/2011', 48.00)
insert into #customers values (123, '10/18/2011', 52.00)
insert into #customers values (123, '12/05/2011', 100.00)

create table #customertotals (account int, trxmonth date, totaltrxamt decimal(9,2))

declare @int int
set @int = 1
declare @month date
declare @endmonth date
set @month = DateAdd(Month, DateDiff(Month, 0, (select min(trxdate) from #customers)), 0)
set @endmonth = DateAdd(Month, DateDiff(Month, 0, @month)+1, 0)-1

select @month, @endmonth

while @int < 4
begin
insert into #customertotals
select
c.account
, @month
, case when TrxAmt is null then 0 else TrxAmt end as TotalAmt
from
(select distinct Account from #customers) c
left join
(select account, sum(trxamt) as trxamt
from #customers
where TrxDate >= @month and TrxDate <= @endmonth
group by account) c1
on c.account = c1.account

set @month = DateAdd(Month, DateDiff(Month, 0, @month)+1, 0)
set @endmonth = DateAdd(Month, DateDiff(Month, 0, @endmonth)+2, 0)-1

select @month, @endmonth
set @int = @int + 1
end

select *
from #customertotals

 

[markros]

The common solution of this problem is to have a cross join (yes, cross join) of all distinct accounts and dates. Then LEFT JOIN into your actual table based on Account and Date.

Here is a link explaining the concept

http://archive.msdn.microsoft.com/S...itle=FindMissingListItems&referringTitle=Home

PluralSight Learning Library

 

[davejam]

Depends on how you want to display the data, you could always use case to bring the details as columns

FOR EXAMPLE

select ACCOUNT,
sum(case WHEN trxDate >= convert(datetime,'01/07/2011',103) AND trxDate < convert(datetime,'01/08/2011',103) then trxAmount end )as Period01,
sum(case WHEN trxDate >= convert(datetime,'01/08/2011',103) AND trxDate < convert(datetime,'01/09/2011',103) then trxAmount end )as Period02
from YourTable
WHERE trxDate >= convert(datetime,'01/07/2011',103) and < convert(datetime,'01/07/2012',103)
GROUP BY ACCOUNT


and so on for the year... then you can add a pass year and work out equation from that so it will work work year on year!!

just a thought, i may have missed the idea of what you need!

The sum() may be in the wrong place, also you may need to create a table variable with values then do a sum from that!

cheers

daveJam

easy come, easy go!!!