December 27, 2009 at 7:51 pm
Hi,
I am very much thankful to Jeff for his kind help in calculating running totals.
Now i am facing another problem i need to calcuate missing periods.
My requirement is i need to check whether each account has transactions in each month. if a account had no transactions in a month then i need to update that months credit and debit with 0 and opening balance and ending balance will be previous month's opening and ending balance correspondingly.
I tried with cursor it is taking so much time. Could you please help me in this problem
Here is my table called transactions
IF OBJECT_ID('TempDB..#transactions','U') IS NOT NULL
DROP TABLE #transactions
--===== Create and populate the test table with data from the post.
CREATE TABLE #transactions
(
Dept INTEGER,
Acct INTEGER,
Date SMALLDATETIME,
Credit DECIMAL(9,2),
Debit DECIMAL(9,2),
OpeningBalance DECIMAL(9,2),
EndingBalance DECIMAL(9,2)
)
INSERT INTO #transactions
(Dept,Acct,Date,Credit,Debit,OpeningBalance,EndingBalance)
SELECT '1','1','2/2/09','100','50' ,'0.00','50.00' UNION ALL
SELECT '1','1','3/2/09','150','120','50.00','80.00' UNION ALL
SELECT '1','1','4/4/09','280','120','80.00','240.00' UNION ALL
SELECT '2','1','2/2/09','300','130','0.00','170.00' UNION ALL
SELECT '2','1','3/2/09','200','110','170.00','260.00' UNION ALL
SELECT '2','2','2/2/09','200','80' ,'0.00','120.00'
December 28, 2009 at 7:30 am
Search the web for: gaps islands ("transact sql" OR tsql). Itzik Ben-Gan has some very good coverage on that topic in a book of his. He also has articles in SQLMag.com. Many other useful links to be had as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 28, 2009 at 8:17 am
This one looks like homework, and I usually don't answer those, but it intrigued me enough to give it a shot.
You have a Dept column, and Acct 1 has entries for Dept 1 and Dept 2 for some months. Your definition of the problem didn't include anything about the Dept column. Is it applicable? If so, the below won't do what you need. If not, see if this does what you're looking for:
;with
Accts (Acct) as
(select distinct Acct
from #transactions),
Months (SDate, EDate) as
(select dateadd(month, number-1, '1/1/2009'),
dateadd(month, number-1, '1/31/2009')
from dbo.Numbers
where Number between 1 and 12
and dateadd(month, number-1, '1/1/2009') < getdate())
select Acct, SDate, EDate, coalesce(Credit, 0) as Credit,
coalesce(Debit, 0) as Debit,
coalesce(Balances.OpeningBalance, PrevBalances.OpeningBalance, 0) as Opening,
coalesce(Balances.EndingBalance, PrevBalances.EndingBalance, 0) as Ending
from Accts
cross join Months
outer apply
(select Credit, Debit, OpeningBalance, EndingBalance
from #transactions
where Acct = Accts.Acct
and Date between SDate and EDate) as Balances
outer apply
(select OpeningBalance, EndingBalance
from #transactions
where Acct = Accts.Acct
and Date between dateadd(month, -1, SDate) and dateadd(month, -1, EDate)) as PrevBalances;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply