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 2012 Forums
 Transact-SQL (2012)
 tricky sql task

Author  Topic 

GloryFades
Starting Member

1 Post

Posted - 2013-09-27 : 17:27:47
Hello guys,

So I have an interesting problem, of taking a dataset of accounts, balances, and dates, and then creating a new data set, is basically creating cascades of start dates to do an analysis of when the balances go to zero, assuming we ignore new accounts after the analysis' start date, and not letting balances of current accounts increase. So I need to extrapolate the initial dataset by calculating minimum balances from one day to the previous day, and then cascading analysis paths (analysis can start from any day, and each cascade is independent of eachother) I realize this is probably a confusing explanation, to better illustrate here is a simplistic example:

Here is the starting table of accounts/balances/dates:
________________________
ACCT CurBal Date
001 100 day1
002 90 day1
003 50 day1
001 80 day2
003 110 day2
004 100 day2
________________________

Here is the table I would like to create with sql queries:

_______________________________________
ACCT CurBal Date MinBal tenure path_start
001 100 day1 100 1 day1
002 90 day1 90 1 day1
003 50 day1 50 1 day1
001 80 day2 80 2 day1
003 110 day2 50 2 day1
001 80 day2 80 1 day2
003 110 day2 110 1 day2
004 100 day2 100 1 day2
________________________________________________

Tenure being the number of days the account has been in the analysis (i.e. acct: 001 has tenure 1 on day 1 for start-path 1, and tenure 2 on day 2 for start-path 1, but has tenure 1 for day2 if start-path=2 (because start-path designates the cascade -- the balance trajectory from that date, and any previous cascades are ignored)

Please let me know if anyone has any suggestions, or if I am being too confusing and need to explain better. Thank you so much!



waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 22:52:18
look ugly

DECLARE @Sample Table(ACCT char(3), CurBal int, [Date] varchar(4))
INSERT INTO @Sample VALUES ('001',100,'day1'), ('002',90,'day1'), ('003',50,'day1'), ('001',80,'day2'), ('003',110,'day2'), ('004',100,'day2')

;WITH CTE AS(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ACCT ORDER BY [DATE]) RN
FROM @Sample
)
SELECT
a.ACCT,
CASE WHEN a.CurBal <> b.CurBal THEN b.CurBal ELSE a.CurBal END,
b.Date,
CASE WHEN a.CurBal < b.CurBal THEN a.CurBal WHEN b.CurBal < a.CurBal THEN b.CurBal ELSE a.CurBal END,
b.RN - a.RN + 1,
a.Date
FROM CTE a
CTE b
WHERE a.ACCT = b.ACCT
AND a.RN <= b.RN

OUTER APPLY(
SELECT *
FROM CTE b
WHERE a.ACCT = b.ACCT
AND a.RN <= b.RN)b
ORDER BY 3, 6, 1
Go to Top of Page
   

- Advertisement -