Solved

Balance Snapshot and Lag Window Function

Posted on 2013-06-18
2
219 Views
Last Modified: 2014-04-01
Having read the books - I understand that a daily snapshot should - by definition - be taken every day.  I am in the specialty credit card space.  Ours is not a daily use card.  Typically - our card is used only once or twice per month.

So I can't give it up.  I don't want to insert 31 rows where only a small number of changes have occurred.  I want to implement the snapshot as a view.  I would insert into a FactChange table only when a change in balance is observed.


For the below example uses cross join, left outer join and lag to accomplish ALMOST exactly what I want - a 20 row result set addressing 2 accounts over 10 days.  The lag gets the previous value and I can coalesce for the FIRST day of inactivity.  It fails on the second, third, yada yada because the previous day as null.

What I need is lag(c.Balance, LastNonNull)  That LastNonNull is the part I can't get my head around.  Can anyone help??


create table DimDate
(
    DateKey                  int not null,
    constraint PK_Date primary key clustered (DateKey)
);
go

insert into DimDate values
(20130601),
(20130602),
(20130603),
(20130604),
(20130605),
(20130606),
(20130607), 
(20130608),
(20130609),
(20130610);
go

create table DimAccount
(
    AccountKey                  int not null,
    constraint PK_Account primary key clustered (AccountKey)
);
go

insert into DimAccount values
(1),
(2);
go

create table FactBalanceChange
(
    DateKey                  int not null,
    AccountKey               int not null,
    Balance                  money not null,
    constraint PK_BalanceChange primary key clustered (DateKey, AccountKey),
    constraint FK_BalanceChange_DateKey foreign key (DateKey) references DimDate(DateKey),
    constraint FK_BalanceChange_AccountKey foreign key (AccountKey) references DimAccount(AccountKey)
);
go

insert into FactBalanceChange values
(20130601, 1, 10.00),
(20130601, 2, 10.00),
(20130604, 1, 5.00),
(20130607, 2, 5.00),
(20130609, 1, 4.00),
(20130609, 2, 4.00);
go

create view FactBalanceSnapshot as

    select 
        d.DateKey, 
        a.AccountKey, 
        coalesce(c.Balance, lag(c.Balance, 1) over (partition by a.AccountKey order by d.DateKey)) as Balance
    from 
            DimDate d
        cross join 
            DimAccount a
        left outer join
            FactBalanceChange c
                on d.DateKey = c.DateKey
               and a.AccountKey = c.AccountKey;  
go 


select * from FactBalanceSnapshot.
 

Open in new window

0
Comment
Question by:jflanner
  • 2
2 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39258305
does this help your thinking? Here I've placed the lag() function into a subquery on FactBalanceChange which avoids the issue of non-matching dates I believe.

I've left the query as displaying 2 columns so you may 'see' what's going on
--create view FactBalanceSnapshot as

    select 
          d.DateKey
        , a.AccountKey
        --, coalesce(c.Balance, lag(c.Balance, 1) over (partition by a.AccountKey order by d.DateKey)) as Balance
        , c.balance
        , c.lag_balance
    from 
            DimDate d
        cross join 
            DimAccount a
        left outer join (
                        select
                          fbc.dateKey
                        , fbc.AccountKey
                        , fbc.Balance
                        , lag(fbc.Balance, 1) over (partition by fbc.AccountKey order by fbc.DateKey ASC)
                            as lag_balance
                        from FactBalanceChange as fbc
                        ) as c
                on d.DateKey = c.DateKey
               and a.AccountKey = c.AccountKey;  
;

Open in new window

thanks for the tables and data - very helpful.
you can play with that data and this query at: http://sqlfiddle.com/#!6/8a56a/5
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39258921
on reflection I think the above comment isn't going to help - sorry, so back to questions I'm afraid. A 'running sum' (column 4) can be added quite simply - but I think you are after column 5 - can you confirm if this is the expected result please?
DATEKEY	ACCOUNTKEY	BALANCE	RUNSUM	Required?
20130601	1	10	10	10
20130602	1	(null)	10	10
20130603	1	(null)	10	10
20130604	1	5	15	5
20130605	1	(null)	15	5
20130606	1	(null)	15	5
20130607	1	(null)	15	5
20130608	1	(null)	15	5
20130609	1	4	19	4
20130610	1	(null)	19	4
				
20130601	2	10	10	10
20130602	2	(null)	10	10
20130603	2	(null)	10	10
20130604	2	(null)	10	10
20130605	2	(null)	10	10
20130606	2	(null)	10	10
20130607	2	5	15	5
20130608	2	(null)	15	5
20130609	2	4	19	4
20130610	2	(null)	19	4

Open in new window

runsum is produced by:
    select 
         d.DateKey 
       , a.AccountKey 
       , c.Balance
       , SUM(c.balance)over (partition by a.AccountKey order by d.DateKey) runsum
    from 
            DimDate d
        cross join 
            DimAccount a
        left outer join
            FactBalanceChange c
                on d.DateKey = c.DateKey
               and a.AccountKey = c.AccountKey

Open in new window

http://sqlfiddle.com/#!6/8a56a/19
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question