Status Movement

• Olalekan Adewale

  Right there with Babe

  Points: 767

  More actions

  May 21, 2015 at 6:17 am

  #304132

  Good day good 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.

• Dohsan

  SSCrazy

  Points: 2972

  More actions

  May 21, 2015 at 2:29 pm

  #1799839

  I'm not sure your expected out come matches fully with what you've provided in the history table. You seem to have a mix of statuses on 2014-06-30 and then a status switch after 2014-12-31 when there is no log in the history table?

  SELECTFH.FullDate,

  FH.FORACID,

  FH.ActualBalance,

  AH.old_status

  FROM@fact_hist AS FH

  INNER

  JOIN@accountstatus_hist AS AH

  ONFH.FullDate <= AH.current_status_date;