Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

sql server 2012: reward point grappler

Techies--
I've gone as far as I can get. I've reached a stalemate. I am attaching the build script with the progressive t-sql. The primary goal I have is to calculate a member's reward point balances. Points accrued in 2012 expire in 2013. Points accrued in 2013 expire in 2014.  Any redemptions are subtracted first from last year's carry over, then the rest from the current year. If you see a better way to do things in the script, please add it to your advice/solution/direction.

The pic is the desired result set.

Intended result set for reward pointsSumTestScript.sql
0
Paula DiTallo
Asked:
Paula DiTallo
  • 7
  • 5
2 Solutions
 
PortletPaulCommented:
Hi. I have got this far but am a bit lost with the carry forward logic and don't understand the logic that requires. Can you describe (in words) how the carry forward column gets 250 1050 1028 212 then zero.  

Result at this point:
| POSYEAR | POSMONTH | TOTALEARNED | TOTALREDEEMED | ROLLINGBALANCE | EXPYEAR | EXPMONTH | EXPROLLINGBAL_2013 | C/F BUT WRONG | EXPROLLINGBAL_2014 |
|---------|----------|-------------|---------------|----------------|---------|----------|--------------------|---------------|--------------------|
|    2012 |       11 |         350 |           100 |            250 |    2013 |       12 |                250 |        (null) |                  0 |
|    2012 |       12 |        1000 |           200 |           1050 |    2013 |       12 |               1050 |        (null) |                  0 |
|    2013 |        1 |        1491 |            22 |           2519 |    2014 |       12 |                  0 |          1028 |               2519 |
|    2013 |        3 |        1040 |           816 |           2743 |    2014 |       12 |                  0 |        (null) |               2743 |
|    2013 |        4 |           0 |           440 |           2303 |    2014 |       12 |                  0 |        (null) |               2303 |
|    2013 |        8 |         470 |           250 |           2523 |    2014 |       12 |                  0 |        (null) |               2523 |
|    2013 |       12 |         700 |             0 |           3223 |    2014 |       12 |                  0 |        (null) |               3223 |

Open in new window

produced by:
SELECT
        POSYear
      , POSMonth
      , TotalDebit AS TotalEarned
      , TotalCredit AS TotalRedeemed
      , RollingBalance
      , EXPYear
      , EXPMonth
      , case when EXPYear = 2013 then RollingBalance else 0 end as EXPRollingBal_2013

, case when prevEXPYear <> EXPYear then
  lag(RollingBalance,1) over (ORDER BY POSYear ,POSMonth) - TotalCredit
  end as "C/F but wrong"

      , case when EXPYear = 2014 then RollingBalance else 0 end as EXPRollingBal_2014
FROM (
      SELECT
              POSYear
            , POSMonth
            , TotalDebit
            , TotalCredit
            , sum(TotalDebit - TotalCredit) over (ORDER BY POSYear ,POSMonth) AS RollingBalance
            , EXPYear
            , EXPMonth
            , lag(EXPYear,1) over (ORDER BY POSYear ,POSMonth) as prevEXPYear
      FROM (
            SELECT
                    year(posdate) AS POSYear
                  , month(posdate) AS POSMonth
                  , sum(debit) AS TotalDebit
                  , sum(credit) AS TotalCredit
                  , CASE WHEN expires IS NULL THEN year(posdate) + 1 ELSE year(expires) END AS EXPYear
                  , CASE WHEN expires IS NULL THEN 12 ELSE month(expires) END AS EXPMonth
            FROM SumTest
            GROUP BY
                    year(posdate)
                  , month(posdate)
                  , CASE WHEN expires IS NULL THEN year(posdate) + 1 ELSE year(expires) END
                  , CASE WHEN expires IS NULL THEN 12 ELSE month(expires) END
           ) as d1
      ) as d2
;

--http://sqlfiddle.com/#!6/c69b5/26 

Open in new window

0
 
Paula DiTalloIntegration developerAuthor Commented:
@PortletPaul, first and foremost thank you so much for working on this.  I can understand why the math is confusing in the EXPCarryForwardBal column.

The EXPCarryForwardBal column represents last year's point balance as the redemptions in the current year spend down old points. So 2012/11 and 2012/12 are the 2 months in this sampling which will carry through until 2013/12.  

In  2012/12 the end of year balance of 1050 was reduced by the first 22 TotalRedeemed points in 2013/1. The reduction is subtracted from last year's roll forward of 1050, leaving the rolling balance in EXPCarryForwardBal @ 1028. Since the redemptions are counted against the points from the previous year, the TotalEarnedPoints with an expiration of 2014/12 retain that value in the EXPRollingBal_2014 column.

The 2012 balance spends down to 0. Which occurs in this sampling on 2013/4. Since the TotalRedeemed exceeded the EXPCarryForwardBal of 212, the remainder of the 400 redemption points count against the current year, recorded under EXPRollingBal_2014. When this happens, the RollingBalance and the EXPRollingBal_2014 "catch up" (match), while the EXPCarryForwardBal remains @ zero.
0
 
PortletPaulCommented:
unknown logicI can get the 1028, and the roll-over from year to year is identifiable - but 212 and beyond - that is not clear to me. While I can arrive at 212 it only works in one cell so I'm not getting it so far.unknown logicQ-28282566-points-tally.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Paula DiTalloIntegration developerAuthor Commented:
@PortletPaul, again thank you for all your effort!  I promised a "grappler" -- and that -- this is!

Yes, you have the C/F roll and the expRollingBal_2014 balances correct in that last spreadsheet.
0
 
PortletPaulCommented:
Mmm, you seem to misunderstand, I cannot figure out how C/F works, all the other columns are fine I can do those in sql.

I know the spreadsheet has the right figures, you gave them to me - but I cannot fathom the logic. sorry for the false impression.
0
 
Paula DiTalloIntegration developerAuthor Commented:
No worries! :)

This sort of spreadsheet logic simply may not translate well into SQL. Here's how I can express what's happening.  I'll use row #6 of the spreadsheet ( when the C/F balance flips to 0)

In cell# I6 (C/F bal)

=IF((H3+((D4+D5+D6)-1))<0, 0, "")
0
 
PortletPaulCommented:
agreed, spreadsheet formula don't always translate, but thanks it helps
I think that cell (I6) would use:

=IF((H4-((D4+D5+D6)-1))<0, 0, I5-D6)

or
=IF((H4-((D4+D5+D6)-1))<0, 0, (H4-D5)-D6)

But what this is revealing is that there is a 3 month cycle D4, D5, D6
(this month plus previous 2 months)

Q1:
Is this a 3 month cycle?  or does it increase in range as we proceed through the year?

e.g. at I7 the range is D4, D5, D6, D7

----------
cell I5 however, is a different logic, as it at the year rollover
I think I have this one ok

-----------
Now cells I3 and I4
Q2:
why do these simply repeat H3 & H4 ?
0
 
Paula DiTalloIntegration developerAuthor Commented:
@ PortletPaul,
Thank you again for working on this.  You have been very generous.

This sampling is a single member-- with nothing tied to quarterly activity other than the member's individual pattern of use. I'm attaching a spreadsheet. Columns N and O from the 4th row down are more descriptive in establishing an arithmetic pattern.

The real trick to all this is determining when the accrued points from the previous year that are set to expire are all consumed prior to applying any earned points for the current year when there are redemptions.  This could happen @ any time, or not at all depending on the member's choice. It's possible that the member loses all accrued points on 12/31/2013 -- or -- that the member spends all accrued points for the previous year on the 1st day of the current year.
ExampleOfMembersExpiringPoints.xlsx
0
 
Paula DiTalloIntegration developerAuthor Commented:
@PortletPaul, thank you again--you've been very helpful.  I've given up on trying to arm wrestle SQL to the end! :) I know cursors are "bad" when it comes to SQL, but in this case I just don't see a way around it! This code works. Let me know if you see a way around a cursor.  I will have to execute something very similar to this code for over 3 million members--making a cursor solution an overall unattractive one.



CREATE TABLE #baseExpiring(
	Year int,
	Month int,
	ExpireYear int,
	ExpireMonth int,
	Earned money NOT NULL,
	Redeemed money NOT NULL,
	RollingBalance money NULL,
	ExpRollingBalance2013 money NULL,
	ExpCarryOverBalance2013 money NULL,
	ExpRollingBalance2014 money NULL,
	Primary Key(Year, Month, ExpireYear, ExpireMonth)
);

Insert Into #baseExpiring(Year, Month, Earned, Redeemed, ExpireYear, ExpireMonth)
Select Year(PosDate) As Year, Month(PosDate) As Month,
 Sum(debit) As Earned, Sum(credit) As Redeemed,
 Case When expires is Null Then Year(PosDate) + 1 Else Year(expires) End As ExpireYear,
 Case When expires is Null Then 12 Else Month(expires) End As ExpireMonth
From sumtest
Group By Year(PosDate), Month(PosDate),
   Case When expires is Null Then Year(PosDate) + 1 Else Year(expires) End,
   Case When expires is Null Then 12 Else Month(expires) End
go

select * from #baseExpiring

Declare ExpiringCursor Cursor Local Forward_Only Keyset
  For Select Year, Month, Earned, Redeemed, ExpireYear, ExpireMonth,
     RollingBalance, ExpRollingBalance2013, ExpCarryOverBalance2013, ExpRollingBalance2014 
     From #baseExpiring
     Order By Year, Month, Earned, Redeemed, ExpireYear, ExpireMonth
  For Update;
Open ExpiringCursor
Declare @Year int,
	@Month int,
	@ExpireYear int,
	@ExpireMonth int,
	@Earned money,
	@Redeemed money,
	@RollingBalance money = 0,
	@ExpRollingBalance2013 money  = 0,
	@ExpCarryOverBalance2013 money  = 0,
	@ExpRollingBalance2014 money  = 0,
	@WSRollingBalance money = 0,
	@WSExpRollingBalance2013 money  = 0,
	@WSExpCarryOverBalance2013 money  = 0,
	@WSExpRollingBalance2014 money  = 0;
  Fetch Next From ExpiringCursor
  Into @Year,
	@Month,
	@Earned,
	@Redeemed,
	@ExpireYear,
	@ExpireMonth,
	@RollingBalance,
        @ExpRollingBalance2013,
	@ExpCarryOverBalance2013,
	@ExpRollingBalance2014;
While @@FETCH_STATUS = 0
Begin
  Set @WSRollingBalance = @WSRollingBalance + @Earned - @Redeemed;
  If @ExpireYear = 2013 
    Set @WSExpRollingBalance2013 = @WSExpRollingBalance2013 + @Earned - @Redeemed
  Else
	Set @WSExpRollingBalance2013 = 0;
  If @ExpireYear = 2013
    Set @WSExpCarryOverBalance2013 = @WSExpCarryOverBalance2013 + @Earned - @Redeemed
  Else
  Begin
    If @Redeemed <= @WSExpCarryOverBalance2013
	Begin
      Set @WSExpRollingBalance2014 = @WSExpRollingBalance2014 + @Earned
      Set @WSExpCarryOverBalance2013 = @WSExpCarryOverBalance2013 - @Redeemed
    End 
	Else
	Begin
      Set @WSExpRollingBalance2014 = @WSExpRollingBalance2014 + @Earned - @Redeemed + @WSExpCarryOverBalance2013
	  Set @WSExpCarryOverBalance2013 = 0
	End
  End;
  Update #baseExpiring 
  Set RollingBalance = @WSRollingBalance,
    ExpRollingBalance2013 = @WSExpRollingBalance2013,
	ExpCarryOverBalance2013 = @WSExpCarryOverBalance2013,
	ExpRollingBalance2014 = @WSExpRollingBalance2014
  Where Current Of ExpiringCursor;
  Fetch Next From ExpiringCursor
  Into @Year,
	@Month,
	@Earned,
	@Redeemed,
	@ExpireYear,
	@ExpireMonth,
	@RollingBalance,
        @ExpRollingBalance2013,
	@ExpCarryOverBalance2013,
	@ExpRollingBalance2014;
End
 
Close ExpiringCursor   
Deallocate ExpiringCursor

Select Year, Month, Earned, Redeemed, ExpireYear, ExpireMonth,
   RollingBalance, ExpRollingBalance2013, ExpCarryOverBalance2013, ExpRollingBalance2014 
From #baseExpiring
Order By Year, Month, Earned, Redeemed, ExpireYear, ExpireMonth; 

Open in new window

0
 
PortletPaulCommented:
I fear you may be correct about the cursor approach, sorry I haven't been much use except to ask questions.
0
 
Paula DiTalloIntegration developerAuthor Commented:
No worries--I knew this task would be painful, although I had no idea just how painful. We shouldn't feel awful about it though--I just ran the vendor's "solution" sql which neither gave the right balances ....nor completed (sys out of memory error) ! : )
0
 
Paula DiTalloIntegration developerAuthor Commented:
For now, this is the best possible approach.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now