Balance Snapshot and Lag Window Function

Posted on 2013-06-18
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)

insert into DimDate values

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

insert into DimAccount values

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)

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);

create view FactBalanceSnapshot as

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

select * from FactBalanceSnapshot.

Open in new window

Question by:jflanner
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 48

Expert Comment

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

        , 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
            DimDate d
        cross join 
            DimAccount a
        left outer join (
                        , 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:!6/8a56a/5
LVL 48

Accepted Solution

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?
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:
       , a.AccountKey 
       , c.Balance
       , SUM(c.balance)over (partition by a.AccountKey order by d.DateKey) runsum
            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!6/8a56a/19

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 50
SQL Server maintenance plan 8 64
MSSQL Convert Char to Date Time 5 36
SQL Query Across Multiple Tables - Help 5 48
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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