SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 tricky sql task
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GloryFades
Starting Member

1 Posts

Posted - 09/27/2013 :  17:27:47  Show Profile  Reply with Quote
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

Malaysia
965 Posts

Posted - 10/03/2013 :  22:52:18  Show Profile  Reply with Quote
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

Edited by - waterduck on 10/03/2013 23:06:30
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000