Running Total with a twist... - 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)

Running Total with a twist...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-08-19 : 18:31:17

Hi there

I urgently need some assistance in determining the number of weeks for an invoice which was not paid.

i.e.
CREATE TABLE Table_Test (Invoice varchar(10), DatediffWeek int, RowNum int, Balance_diff int)
INSERT INTO Table_Test VALUES('E2011','1','1','0')
INSERT INTO Table_Test VALUES('E2011','1','2','0')
INSERT INTO Table_Test VALUES('E2011','0','3','0')
INSERT INTO Table_Test VALUES('E2011','2','4','0')
INSERT INTO Table_Test VALUES('E2011','1','5','98')
INSERT INTO Table_Test VALUES('E2012','1','1','0')
INSERT INTO Table_Test VALUES('E2012','1','2','0')
INSERT INTO Table_Test VALUES('E2012','1','3','0')
INSERT INTO Table_Test VALUES('E2012','1','4','5')
INSERT INTO Table_Test VALUES('E2012','2','5','0')
INSERT INTO Table_Test VALUES('E2012','1','6','10')
INSERT INTO Table_Test VALUES('E2013','1','1','0')
INSERT INTO Table_Test VALUES('E2013','1','2','0')
INSERT INTO Table_Test VALUES('E2013','1','3','0')
INSERT INTO Table_Test VALUES('E2013','1','4','0')
INSERT INTO Table_Test VALUES('E2013','0','5','13')
SELECT * FROM Table_Test

I need to work out per Invoice, the sum of the running total of the DatediffWeeks where the no there was no change to the balance. i.e my desired results would be (column:TotalWeeksofNoPayments) :


Invoice	 DateDiffWeek	RowNum	Balance_diff	TotalWeeksofNoPayments
E2011		1	1	0	
E2011		1	2	0		
E2011		0	3	0	
E2011		2	4	0	
E2011		1	5	98		4
E2012		1	1	0	
E2012		1	2	0	
E2012		1	3	0	
E2012		1	4	5		3
E2012		2	5	0	
E2012		1	6	10		2
E2013		1	1	0	
E2013		1	2	0	
E2013		1	3	0	
E2013		1	4	0	
E2013		0	5	13		4

As you can see For Invoice E2011, there were 4 Invoices where the balance did not change, but on RowNum 5, the balance did change and so the sum of the DateDiffWeek invoices prior to that where the balance did not change needs to be displayed in the Totalweeks ofNoPayments field.

Now, I have attempted various cross joins, COALESCE and loop statements but to no avail. My example is a bit more complex than the examples you can google...

The full table has over 1.5 million records, so any specific code that would help solve the above problem, would enable me to apply this to the full table.

Thanks!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-19 : 22:45:03

*bump*
wish to know the answer also

ps.wasn't able to get the duration if there is more than 1 balance_diff <> 0

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 23:04:56

[code]
; with cte (Invoice, DatediffWeek, RowNum, Balance_diff, TotalWeeksofNoPayments)
as
(
select Invoice, DatediffWeek, RowNum, Balance_diff,
TotalWeeksofNoPayments = case when Balance_diff = 0 then DatediffWeek else 0 end
from Table_Test
where RowNum = 1

union all

select t.Invoice, t.DatediffWeek, t.RowNum, t.Balance_diff,
TotalWeeksofNoPayments = case when t.Balance_diff = 0
then
case when c.Balance_diff = 0
then c.TotalWeeksofNoPayments + t.DatediffWeek
else t.DatediffWeek
end
else c.TotalWeeksofNoPayments
end
from Table_Test t
inner join cte c on t.Invoice = c.Invoice
and t.RowNum = c.RowNum + 1
)
select Invoice, DatediffWeek, RowNum, Balance_diff,
TotalWeeksofNoPayments = case when Balance_diff <> 0 then TotalWeeksofNoPayments else 0 end
from cte
order by Invoice, RowNum
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-20 : 03:11:46

thx for teaching sifu ^^

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 04:42:12

you are welcome.

dnf999's requirement might be much more complicated that this. The Table_Test posted looks like is a result of the query not the full table with 1.5 millions records

KH
[spoiler]Time is always against us[/spoiler]

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-08-25 : 11:12:51

Hi khtan,

Thanks for your post, although when I run this (on a large sample of data) I get the following error:

Msg 240, Level 16, State 1, Line 4
Types don't match between the anchor and the recursive part in column "TotalWeeksofNoPayments" of recursive query "cte"

I have tried casting this field to various data types, but I continue to get that error. Any ideas?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources