Solved

sql server 2012: reward point grappler

Posted on 2013-10-31
12
346 Views
Last Modified: 2013-11-09
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
Comment
Question by:ditallop
  • 7
  • 5
12 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 39615644
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
 

Author Comment

by:ditallop
ID: 39616118
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616253
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
 

Author Comment

by:ditallop
ID: 39616490
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616596
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
 

Author Comment

by:ditallop
ID: 39616906
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618223
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
 

Author Comment

by:ditallop
ID: 39621838
@ 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
 

Accepted Solution

by:
ditallop earned 0 total points
ID: 39623088
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39623216
I fear you may be correct about the cursor approach, sorry I haven't been much use except to ask questions.
0
 

Author Comment

by:ditallop
ID: 39623259
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
 

Author Closing Comment

by:ditallop
ID: 39635515
For now, this is the best possible approach.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

744 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