SQLTeam.com | Weblogs | Forums

Account Status History

sql2012

#1
Good day people

Am working on the 2 datasets. The first keeps the status history all the account. It keeps the movement from A to I to D and back to A etc

Please Note: A = Active, I = Inactive, D = Dormant

See table structure here

DECLARE @accountstatus_hist TABLE (ACID varchar(20), FORACID VARCHAR(20), old_status varchar(1), current_status varchar(1), [current_status_date] DATE, prev_status_date date)
INSERT INTO @accountstatus_hist					
select '423156568', '2xxxxxxxx1','A','I','2014-06-30','2013-07-22' union all
select '423156568', '2xxxxxxxx1','I','A','2014-12-31','2014-06-30' 

select * from @accountstatus_hist

The second is the transaction history table.

DECLARE @fact_hist TABLE (FullDate date, FORACID VARCHAR(20), ActualBalance decimal(32,5))
INSERT INTO @fact_hist
SELECT '2014-01-31','2xxxxxxxx1', 2595.53000 union all 
SELECT '2014-01-31','2xxxxxxxx1', 311.46360 union all 
SELECT '2014-01-31','2xxxxxxxx1', 77.86590 union all 
SELECT '2014-02-28','2xxxxxxxx1', 77.99790 union all 
SELECT '2014-02-28','2xxxxxxxx1', 311.99160 union all 
SELECT '2014-02-28','2xxxxxxxx1', 2599.93000 union all 
SELECT '2014-03-31','2xxxxxxxx1', 312.47040 union all 
SELECT '2014-03-31','2xxxxxxxx1', 2603.92000 union all 
SELECT '2014-03-31','2xxxxxxxx1', 78.11760 union all 
SELECT '2014-04-30','2xxxxxxxx1', 78.28800 union all 
SELECT '2014-04-30','2xxxxxxxx1', 2609.60000 union all 
SELECT '2014-04-30','2xxxxxxxx1', 313.15200 union all 
SELECT '2014-05-31','2xxxxxxxx1', 2618.10000 union all 
SELECT '2014-05-31','2xxxxxxxx1', 78.54300 union all 
SELECT '2014-05-31','2xxxxxxxx1', 314.17200 union all 
SELECT '2014-06-30','2xxxxxxxx1', 78.78300 union all 
SELECT '2014-06-30','2xxxxxxxx1', 2626.10000 union all 
SELECT '2014-06-30','2xxxxxxxx1', 315.13200 union all 
SELECT '2014-07-31','2xxxxxxxx1', 395.00250 union all 
SELECT '2014-07-31','2xxxxxxxx1', 79.00050 union all 
SELECT '2014-07-31','2xxxxxxxx1', 2633.35000 union all 
SELECT '2014-08-31','2xxxxxxxx1', 79.24590 union all 
SELECT '2014-08-31','2xxxxxxxx1', 396.22950 union all 
SELECT '2014-08-31','2xxxxxxxx1', 2641.53000 union all 
SELECT '2014-09-30','2xxxxxxxx1', 2648.05000 union all 
SELECT '2014-09-30','2xxxxxxxx1', 397.20750 union all 
SELECT '2014-09-30','2xxxxxxxx1', 79.44150 union all 
SELECT '2014-10-31','2xxxxxxxx1', 398.21850 union all 
SELECT '2014-10-31','2xxxxxxxx1', 2654.79000 union all 
SELECT '2014-10-31','2xxxxxxxx1', 79.64370 union all 
SELECT '2014-11-30','2xxxxxxxx1', 399.23400 union all 
SELECT '2014-11-30','2xxxxxxxx1', 2661.56000 union all 
SELECT '2014-11-30','2xxxxxxxx1', 79.84680 union all 
SELECT '2014-12-31','2xxxxxxxx1', 533.58000 union all 
SELECT '2014-12-31','2xxxxxxxx1', 80.03700 union all 
SELECT '2014-12-31','2xxxxxxxx1', 2667.90000 union all 
SELECT '2015-01-31','2xxxxxxxx1', 80.24760 union all 
SELECT '2015-01-31','2xxxxxxxx1', 534.98400 union all 
SELECT '2015-01-31','2xxxxxxxx1', 2674.92000 union all 
SELECT '2015-02-28','2xxxxxxxx1', 536.34800 union all 
SELECT '2015-02-28','2xxxxxxxx1', 2681.74000 union all 
SELECT '2015-02-28','2xxxxxxxx1', 80.45220 union all 
SELECT '2015-03-31','2xxxxxxxx1', 2687.91000 union all 
SELECT '2015-03-31','2xxxxxxxx1', 537.58200 union all 
SELECT '2015-03-31','2xxxxxxxx1', 80.63730 union all 
SELECT '2015-04-30','2xxxxxxxx1', 538.95200 union all 
SELECT '2015-04-30','2xxxxxxxx1', 80.84280 union all 
SELECT '2015-04-30','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-05','2xxxxxxxx1', 404.21400 union all 
SELECT '2015-05-05','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-05','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-11','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-11','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-11','2xxxxxxxx1', 404.21400 union all 
SELECT '2015-05-17','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-17','2xxxxxxxx1', 404.21400 union all 
SELECT '2015-05-17','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-18','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-18','2xxxxxxxx1', 404.21400 union all 
SELECT '2015-05-18','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-19','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-19','2xxxxxxxx1', 2694.76000 union all 
SELECT '2015-05-19','2xxxxxxxx1', 404.21400 


select * from @fact_hist order by 1 asc

I want to capture the AccountStatus as the day or period it happened, using the Transaction Date (FullDate). This will enable me to know status as the day of transaction.

Please see below my desired table, using the example abouve to populate the AccountStatus field in transaction table.

DECLARE @fact_hist_ext TABLE (FullDate date, FORACID VARCHAR(20), ActualBalance decimal(32,5), accountstatus varchar(1))

INSERT INTO @fact_hist_ext
SELECT '2014-01-31','2xxxxxxxx1', 2595.53000,'A' union all 
SELECT '2014-01-31','2xxxxxxxx1', 311.46360,'A' union all 
SELECT '2014-01-31','2xxxxxxxx1', 77.86590,'A' union all 
SELECT '2014-02-28','2xxxxxxxx1', 77.99790,'A' union all 
SELECT '2014-02-28','2xxxxxxxx1', 311.99160,'A' union all 
SELECT '2014-02-28','2xxxxxxxx1', 2599.93000,'A' union all 
SELECT '2014-03-31','2xxxxxxxx1', 312.47040,'A' union all 
SELECT '2014-03-31','2xxxxxxxx1', 2603.92000,'A' union all 
SELECT '2014-03-31','2xxxxxxxx1', 78.11760,'A' union all 
SELECT '2014-04-30','2xxxxxxxx1', 78.28800,'A' union all 
SELECT '2014-04-30','2xxxxxxxx1', 2609.60000,'A' union all 
SELECT '2014-04-30','2xxxxxxxx1', 313.15200,'A' union all 
SELECT '2014-05-31','2xxxxxxxx1', 2618.10000,'A' union all 
SELECT '2014-05-31','2xxxxxxxx1', 78.54300,'A' union all 
SELECT '2014-05-31','2xxxxxxxx1', 314.17200,'A' union all 
SELECT '2014-06-30','2xxxxxxxx1', 78.78300,'A' union all 
SELECT '2014-06-30','2xxxxxxxx1', 2626.10000,'I' union all 
SELECT '2014-06-30','2xxxxxxxx1', 315.13200,'I' union all 
SELECT '2014-07-31','2xxxxxxxx1', 395.00250,'I' union all 
SELECT '2014-07-31','2xxxxxxxx1', 79.00050,'I' union all 
SELECT '2014-07-31','2xxxxxxxx1', 2633.35000,'I' union all 
SELECT '2014-08-31','2xxxxxxxx1', 79.24590,'I' union all 
SELECT '2014-08-31','2xxxxxxxx1', 396.22950,'I' union all 
SELECT '2014-08-31','2xxxxxxxx1', 2641.53000,'I' union all 
SELECT '2014-09-30','2xxxxxxxx1', 2648.05000,'I' union all 
SELECT '2014-09-30','2xxxxxxxx1', 397.20750,'I' union all 
SELECT '2014-09-30','2xxxxxxxx1', 79.44150,'I' union all 
SELECT '2014-10-31','2xxxxxxxx1', 398.21850,'I' union all 
SELECT '2014-10-31','2xxxxxxxx1', 2654.79000,'I' union all 
SELECT '2014-10-31','2xxxxxxxx1', 79.64370,'I' union all 
SELECT '2014-11-30','2xxxxxxxx1', 399.23400,'I' union all 
SELECT '2014-11-30','2xxxxxxxx1', 2661.56000,'I' union all 
SELECT '2014-11-30','2xxxxxxxx1', 79.84680,'I' union all 
SELECT '2014-12-31','2xxxxxxxx1', 533.58000,'A' union all 
SELECT '2014-12-31','2xxxxxxxx1', 80.03700,'A' union all 
SELECT '2014-12-31','2xxxxxxxx1', 2667.90000,'A' union all 
SELECT '2015-01-31','2xxxxxxxx1', 80.24760,'A' union all 
SELECT '2015-01-31','2xxxxxxxx1', 534.98400,'A' union all 
SELECT '2015-01-31','2xxxxxxxx1', 2674.92000,'A' union all 
SELECT '2015-02-28','2xxxxxxxx1', 536.34800,'A' union all 
SELECT '2015-02-28','2xxxxxxxx1', 2681.74000,'A' union all 
SELECT '2015-02-28','2xxxxxxxx1', 80.45220,'A' union all 
SELECT '2015-03-31','2xxxxxxxx1', 2687.91000,'A' union all 
SELECT '2015-03-31','2xxxxxxxx1', 537.58200,'A' union all 
SELECT '2015-03-31','2xxxxxxxx1', 80.63730,'A' union all 
SELECT '2015-04-30','2xxxxxxxx1', 538.95200,'A' union all 
SELECT '2015-04-30','2xxxxxxxx1', 80.84280,'A' union all 
SELECT '2015-04-30','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-05','2xxxxxxxx1', 404.21400,'A' union all 
SELECT '2015-05-05','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-05','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-11','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-11','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-11','2xxxxxxxx1', 404.21400,'A' union all 
SELECT '2015-05-17','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-17','2xxxxxxxx1', 404.21400,'A' union all 
SELECT '2015-05-17','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-18','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-18','2xxxxxxxx1', 404.21400,'A' union all 
SELECT '2015-05-18','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-19','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-19','2xxxxxxxx1', 2694.76000,'A' union all 
SELECT '2015-05-19','2xxxxxxxx1', 404.21400,'A' 


select * from @fact_hist_ext order by 1 asc

Thanks All.


#2

To insert records in fact_hist_ext from fact_hist table along with status from accountstatus_hist table,
This should do it

INSERT INTO @fact_hist_ext
SELECT fhist.*,ahist.current_status FROM @fact_hist fhist
LEFT JOIN @accountstatus_hist ahist
ON fhist.FORACID = ahist.FORACID AND cast(fhist.FullDate as date) = cast(ahist.current_status_date as DATE)