I currently have two tables, a Transaction table and a Discount table:
CREATE TABLE #Transactions (
[Transaction Date] date,
[Customer Name] nvarchar(255),
[Account] nvarchar(255),
[Room] nvarchar(255)
)
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2016-09-15', 'John', '1111', 'A')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2017-09-15', 'John', '1111', 'A')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2018-09-15', 'John', '1111', 'A')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2018-10-24', 'John', '1111', 'B')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2018-11-03', 'John', '1111', 'C')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2019-10-24', 'John', '1111', 'B')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2019-11-03', 'John', '1111', 'C')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2020-10-24', 'John', '1111', 'B')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2020-11-03', 'John', '1111', 'C')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room])
VALUES ('2021-11-03', 'John', '1111', 'C')
CREATE TABLE #Discounts (
[Customer Name] nvarchar(255),
[Transaction Date] date,
[Account] nvarchar(255),
[Discount Type] nvarchar(255),
[Discount Rank] nvarchar(255),
[Used Flag] nvarchar(255)
)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2016-09-15', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2016-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2017-09-15', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2017-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-09-15', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-10-24', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-10-24', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-11-03', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-11-03', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-10-24', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-10-24', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-11-03', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-11-03', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-10-24', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-10-24', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-11-03', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-11-03', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2021-11-03', '1111', '75% Off', 1, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2021-11-03', '1111', '40% Off', 2, 0)
The Transaction table shows the transaction history for the customer, John. The Discount table shows the discounts that are available to the customer on Transaction Date along with `Discount Rank which ranks the discounts with the best discount shown as '1'. Better ranked discounts were used first as long as it had not been already used.
So in my sample data, John has 2 discounts available to use. I added Used Flag in the Discount
table to indicate whether the discount had been used. The table starts with Used Flag = 0
indicating the discount had not been used. Then on 2016-09-15, the Transaction table shows
he leased Room A. Since this was his first room, the best discount, "75% Off" would be used
until he stopped leasing it. Because the discount was now being used, the Used Flag needed to
be updated from 0 to 1.
The last time he leased Room A was 2019-09-15, and he changed to Room B on 2019-10-24. The "75% Off" was now being applied to Room B.
Then on 2019-11-03, John leased an additional Room C. Since the "75% Off" had already been used by Room B, Room C got the next best discount which was "40% Off".
In the end, John last renewed his lease on Room B on 2021-10-24, so the next time he leased
Room C, the "75% Off" discount was available to use and replaced the "40% Off", so the
Used Flag changed back to 0.
Ultimately, I am looking for something like this:
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2016-09-15', 'John', '1111', 'A', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2017-09-15', 'John', '1111', 'A', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2018-09-15', 'John', '1111', 'A', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2018-10-24', 'John', '1111', 'B', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2018-11-03', 'John', '1111', 'C', '40% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2019-10-24', 'John', '1111', 'B', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2019-11-03', 'John', '1111', 'C', '40% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2020-10-24', 'John', '1111', 'B', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2020-11-03', 'John', '1111', 'C', '75% Off')
INSERT INTO #Transactions ([Transaction Date], [Customer Name], [Account], [Room], [Discount Used])
VALUES ('2021-11-03', 'John', '1111', 'C', '75% Off')
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2016-09-15', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2016-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2017-09-15', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2017-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-09-15', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-09-15', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-10-24', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-10-24', '1111', '40% Off', 2, 0)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-11-03', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2018-11-03', '1111', '40% Off', 2, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-10-24', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-10-24', '1111', '40% Off', 2, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-11-03', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2019-11-03', '1111', '40% Off', 2, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-10-24', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-10-24', '1111', '40% Off', 2, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-11-03', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2020-11-03', '1111', '40% Off', 2, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2021-11-03', '1111', '75% Off', 1, 1)
INSERT INTO #Discounts ([Customer Name], [Transaction Date], [Account], [Discount Type], [Discount Rank], [Used Flag])
VALUES ('John', '2021-11-03', '1111', '40% Off', 2, 0)
I know something like this can be done with Python, but I have to use SQL for this and I don't know how to even begin.
Thank you.