Transactional database

Posted on 2012-09-17
Last Modified: 2012-09-27
claimid          lossreserve   losspaid       RunDate
3456             100.00         450.00        2012/12/01
3457             200.00         150.00        2012/12/01
3458             239.00         2000.00      2012/12/01
3459             20.00           50.00          2012/12/01
3456             300.00         156.00        2012/12/02
3457             654.00         456.00        2012/12/02
3458             900.00         213.00        2012/12/02
3459             12.00           34.00          2012/12/02
3461             145.00         29.00          2012/12/02
3456             245.00         98.00          2012/12/03
3457             345.00         987.00        2012/12/03
3458             345.00         567.00        2012/12/03
3459             234.45         123.00        2012/12/03

I need to perform transactional calculation using the current and previous rundate to determine the lossreserve and losspaid.
Question by:olongus
    LVL 12

    Expert Comment

    Hi Olungus,

    Do you ever a claimID process more than once in a single day?

    Will you ever have a claimID that doesn't have a previous run date?

    And just to confirm, loss reserve and loss paid for the last two run dates are just totals of two columns, respectively (?)
    LVL 2

    Expert Comment

    I hope this will help to u.

    select sum(lossreserve),sum(losspaid) from table_name where RunDate<date(now()) and RunDate>date_sub(date(now()), interval 2 day);

    Author Comment

    The issue here is that the claims data comes in everyday and the data that is presented is asof that day. I need to derive transactional information from the data that is why I've appended the data when the data was loaded in order to perform the calculation using the rundate. If a claim occurred yesterday, I want to compare the same claim today to derive what the difference between the two values.
     This is what I have so far.

      ;With cte as
     Select [Claim#]  AS ClaimNumber,(([Indeminity Incurred]+ [Medical Incurred]+ [Property Damage Incurred])
       -([Indeminity Paid]+ [Medical Paid]+ [Property Damage Paid])) AS [Loss Reserve]
      ,([All Incurred])-([All Paid]) AS [ALE Res A&O]
      ,[Outstanding],[Net Incurred (TotalPaid + Outstanding - RecoveryPaid)]
     ,ROW_NUMBER ()Over (Partition by Claim# Order by Rundate) as Rownumber2
    Select  cur.Rownumber2,Cur.ClaimNumber,isnull(cur.[Loss Reserve],0.00) as LossReserve,cur.[ALE Res A&O],isnull(cur.[ALE Res A&O],0.00) as [ALE A&O Reserve]
    ,Isnull(prv.[Loss Reserve],0.00) as PreviousLossReserve
     into #ss
    From cte Cur Left Join cte  Prv
    On cur.ClaimNumber=prv.ClaimNumber
    and Cur.Rownumber2=Prv.RowNumber2+1
    LVL 12

    Accepted Solution

    I think your going to get a lot of use out of the rank function here.

    I wrote this based off of the sample data. It probably isn't the most stream-lined way to do it, but it works.

    The first half is just creating a variable table and inserting your sample data...

    declare @claims table (claimid int,lossreserve money,losspaid money,RunDate datetime)
    insert into @claims values (3456,             100.00,         450.00,        '2012/12/01')
    insert into @claims values (3457,             200.00,         150.00,        '2012/12/01')
    insert into @claims values (3458,             239.00,         2000.00,       '2012/12/01')
    insert into @claims values (3459,             20.00 ,          50.00,        '2012/12/01')
    insert into @claims values (3456,             300.00,         156.00,        '2012/12/02')
    insert into @claims values (3457,             654.00,         456.00,        '2012/12/02')
    insert into @claims values (3458,             900.00,         213.00,        '2012/12/02')
    insert into @claims values (3459,             12.00,           34.00,        '2012/12/02')
    insert into @claims values (3461,             145.00,         29.00,         '2012/12/02')
    insert into @claims values (3456,             245.00,         98.00,         '2012/12/03')
    insert into @claims values (3457,             345.00,         987.00,        '2012/12/03')
    insert into @claims values (3458,             345.00,         567.00,        '2012/12/03')
    insert into @claims values (3459,             234.45,         123.00,        '2012/12/03')
    select b.claimid, c.lossreserve - isnull(d.lossreserve,0) as LossReserve,  c.losspaid - isnull(d.losspaid,0) as LossPaid
    (select max([order]) as today, max([order])-1 as yesterday, claimid from
    select rank() over (partition by claimid order by rundate asc) as [order],
    claimid, lossreserve, losspaid
    from @claims
    ) a
    group by claimid) b
    inner join
    (select rank() over (partition by claimid order by rundate asc) as [order],
    claimid, lossreserve, losspaid
    from @claims) c
    on b.claimid = c.claimid and = c.[order] 
    left outer join 
    (select rank() over (partition by claimid order by rundate asc as [order],
    claimid, lossreserve, losspaid
    from @claims) d
    on b.claimid = d.claimid and b.yesterday = d.[order]

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now