mapping Redeemed miles to accrual bucket
-
There are different accrual dates for miles and each redemption of mile is associated with accrual bucket. Now suppose another table is having only redemption date and redeemed miles. How to match that redemption with each accrual bucket according to FIFO method.
If accrual bucket is short of miles then miles will go to next accrual bucket order by expiry month asc. -
What do you mean by "suppose another table"? Does this mean the table does not exist at this moment in time and we need to imagine that one does exist? I'm not really sure I understand what your goal is here.
Have a look at the link in my signature, which links to a article by Jeff on how to post T-SQL question. Unlike some things, in T-SQL a picture does not mean a thousand words; in all actuality it probably means none. We can't work with data when it's a picture, so we need it in a consumable format. That means DDL and DLM that we can simply copy and paste into our own environments and run.
When providing your DDL and DLM, ensure you provide what you want your expected output to look like. If this other table you haven't created needs to be considered, then provide details of that table; it's DDL will be especially important and if required DLM as well (even if that data isn't what might be used in the end, we have something to work with). We need all the pieces of the puzzle and a picture of the end product to provide an answer, but we need you to provide those pieces and picture first. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks Thoms for replying and sorry about the picture posted above. Newbie errors. 🙂
drop table #Breakage_Merge
create table #Breakage_Merge(rowid int,flyer_id bigint,Accrual_month date null,Accruals bigint,Expected_Expiry_month date null,
Actual_red_exp_month date null,Expired_miles bigint,Redeemed_miles bigint,Outstanding_miles bigint,Running_total bigint)insert into #Breakage_Merge
values(1,100001,'01/01/2014',31546,'01/01/2017','10/01/2014',0,31546,0,0),
(2,100001,'02/01/2014',6650,'02/01/2017','10/01/2014',0,6650,0,0),
(3,100001,'03/01/2014',31546,'03/01/2017','10/01/2014',0,31546,0,0),
(4,100001,'07/01/2014',19098,'07/01/2017','10/01/2015',0,30779,-11681,-11681),
(5,100001,'07/01/2014',12448,'07/01/2017','10/01/2014',0,767,11681,0),
(6,100001,'08/01/2014',9975,'08/01/2017','10/01/2015',0,9975,0,0),
(7,100001,'10/01/2014',3325,'08/01/2017','10/01/2015',0,3325,0,0),
(8,100001,'09/01/2014',12448,'09/01/2017','02/01/2016',0,12876,-428,-428),
(9,100001,'10/01/2014',2401,'09/01/2017','10/01/2015',0,2401,0,-428),
(10,100001,'10/01/2014',8990,'09/01/2017','02/01/2016',0,8990,0,-428),
(11,100001,'09/01/2014',19098,'09/01/2017','10/01/2015',0,18670,428,0),
(12,100001,'04/01/2015',25057,'01/01/2018','02/01/2016',0,25057,0,0),
(13,100001,'01/01/2015',69394,'01/01/2018','02/01/2016',0,69394,0,0),
(14,100001,'03/01/2015',-1000,'02/01/2018',NULL,0,0,-1000,-1000),
(15,100001,'04/01/2015',4802,'02/01/2018','02/01/2016',0,4802,0,-1000),
(16,100001,'02/01/2015',14300,'02/01/2018','02/01/2016',0,14300,0,-1000),
(17,100001,'03/01/2015',3325,'03/01/2018','02/01/2016',0,6089,-2764,-3764),
(18,100001,'04/01/2015',4802,'03/01/2018','02/01/2016',0,4802,0,-3764),
(19,100001,'05/01/2015',4802,'03/01/2018','02/01/2016',0,4802,0,-3764),
(20,100001,'05/01/2015',14502,'03/01/2018','07/01/2016',0,14502,0,-3764),
(21,100001,'04/01/2015',14502,'03/01/2018','07/01/2016',0,14502,0,-3764),
(22,100001,'03/01/2015',47743,'03/01/2018','07/01/2016',0,44979,2764,-1000),
(23,100001,'07/01/2015',28221,'07/01/2018','07/01/2016',0,28221,0,-1000),
(24,100001,'09/01/2015',32410,'09/01/2018','07/01/2016',0,40934,-8524,-9524),
(25,100001,'09/01/2015',74326,'09/01/2018','08/01/2017',0,47828,26498,16974),
(26,100001,'09/01/2015',39418,'09/01/2018',NULL,0,0,39418,56392),
(27,100001,'02/01/2016',14406,'01/01/2019',NULL,0,0,14406,70798),
(28,100001,'01/01/2016',85486,'01/01/2019',NULL,0,0,85486,156284),
(29,100001,'05/01/2016',332,'02/01/2019',NULL,0,0,332,156616),
(30,100001,'02/01/2016',9236,'02/01/2019',NULL,0,0,9236,165852),
(31,100001,'03/01/2016',11080,'02/01/2019',NULL,0,0,11080,176932),
(32,100001,'04/01/2016',45564,'03/01/2019',NULL,0,0,45564,222496),
(33,100001,'03/01/2016',124058,'03/01/2019',NULL,0,0,124058,346554),
(34,100001,'04/01/2016',33294,'04/01/2019',NULL,0,0,33294,379848),
(35,100001,'05/01/2016',40304,'04/01/2019',NULL,0,0,40304,420152),
(36,100001,'05/01/2016',73598,'05/01/2019',NULL,0,0,73598,493750),
(37,100001,'07/01/2016',-8380,'07/01/2019',NULL,0,0,-8380,485370),
(38,100001,'08/01/2016',30841,'08/01/2019',NULL,0,0,30841,516211),
(39,100001,'09/01/2016',81124,'09/01/2019',NULL,0,0,81124,597335),
(40,100001,'03/01/2017',87686,'03/01/2020',NULL,0,0,87686,685021),
(41,100001,'05/01/2017',87616,'05/01/2020',NULL,0,0,87616,772637),
(42,100001,'08/01/2017',36642,'08/01/2020',NULL,0,0,36642,809279),
(43,100001,'09/01/2017',43808,'09/01/2020',NULL,0,0,43808,853087),drop table #Redemption_sample
create table #Redemption_sample(flyer_id bigint,Actual_red_exp_month date,Expired_miles bigint,Redeemed_miles bigint,unique_red_seq bigint)insert into #Redemption_sample values('100001','10/01/2017',0,20000,12345678)
The output could be anything. It could be numbers update in same table or a new table.
I want to link each new redemption from #Redemption_sample to the #Breakage_Merge table on FIFO basis.Row_id 25: there are still 16,974 miles available from previous transaction. So from New redemption of 20k, 16974 will deduct from row_id 25 and rest 3026 will go to row_id 26. So new outstanding will be 36,392 for row_id 26.
And running total will change accordingly.Thanks in advance.
-
I think I see what you're trying to do, but I have no idea why you want to do things that way. It seems tremendously inefficient to hold onto a running total that you would then need to update in place. This would make much more sense as a transactional setup, where each transaction would affect only the grand total miles remaining and the earliest accrual buckets necessary to allocate all the redeemed miles. Once that update is complete you can easily enough generate a report that shows all the buckets and their respective balances. Also a tad hard to understand is how you can ever allow redeemed miles to give you a negative balance. Every airline miles program I've ever seen has no ability to "borrow miles", so to speak\, which is what this appears to be doing. We appear to be looking at a solution that you basically want to work backward from. Updating a temp table that represents all the buckets makes no sense because it involves massive data manipulation that is completely unnecessary. However, maybe I'm missing something, as I'm not real clear on why this is the objective. Seems to me it makes a lot more sense to update a source table within a transaction and then re-generate the running total data only when you actually need it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks Steve for your time.
The accrual, redemption and expired miles i have at transaction level in old system. The issue is, we are moving to new system where we don't have accrual date of a particular redeemed or expired mile. Also the new system will not show the redeemed/expired miles came from which accrual bucket (we have to assume and deduct on FIFO basis).
This is actually an issue of the outstanding miles in old system, where we have to map the redemption/ expired to old system accruals and then calculate outstanding (accruals-red-exp=outstanding) until that become 0.
Because I don't have any common key that's why I decided to map it month level.
I hope this makes sense. If you provide me some SQL code in which I can deduct mile from one row and then move to next row to deduct or add remaining miles, (like offset)Thanks
Ashish -
I usually try to help people, but when the situation as described just doesn't make any sense, and the organization is an airline that ought to know better from an IT perspective, I lose interest. This is the kind of project that should be in the hands of database people that already know how to solve this kind of problem and know how to influence people to change the design when it's that kind of problematic. I just can't justify my continued involvement in this one, as there really aren't enough details, and this also quite likely qualifies as more work than a volunteer should be taking on. Airline miles are too important a feature for an airline to let be handled the way this appears to be being handled. I'm going to have to let this go. The airline really should be bringing in an experienced consultant to handle this kind of project.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I understand your concern Steve.
And trust me we have systems to calculate this thing. It's just I am not able to get the scripts of how the real calculation is going on in the system. It's for something that I am creating. So trying to understand how we can deduct something from the miles bucket and then move to next immediate row. At final stage I will check how much off I am from reality.
Do you think I should remove this post and rewrite again by using inventory management scenario?
Thanks a lot for your time. 🙂@anyone: Please can you tell me which function I should use to manipulate data in one row based on some condition and then move on to next row to do another manipulation. I will google other things related t the function. Just advice function. thank you.
-
My only suggestion here would be to load this into a table and add a row_number(), which will give you some ordering. As this appears to be a one time thing, you could then start to work with each row using the row number as a handle. Other than that, I'm not completely sure what you are doing here, as the explanation doesn't quite seem to make sense and I agree with Steve, this is a bit more than I'd like to dig into.
-
Sorry a bit late to returning, works been busy.
Unfortunately (possibly unlike Steve) I'm struggling to realise what your requirement is. What, for example, is determining your FIFO. I thought, maybe, it was the first row witout a Actual_red_exp_month, however, row 14 doesn't have one. Is it the first row with a date less than the date in Redemption Sample in Descending order, perhaps? I asusme that this is meant to be :sick: iterative if you have multiple value at one time...?
It would be good to have more than one example. Give multiple and say how you want each one to work. An expected output would be very useful as well.
Edit: Ahh, both Steves have posted! Now you don't know what you I'm talking about! One became many! 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply