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.