Home:ALL Converter>SQL Server : Debit Card Conditional Transactions

SQL Server : Debit Card Conditional Transactions

Ask Time:2018-06-08T22:04:42         Author:Craig

Json Formatter

I work for a telecommunications company that offers multiple packages to our customers. Some of those packages include having a set number of free long-distance minutes they can use each month.

Currently, the free minutes packages have a flaw in them that allows the customer to get way more free minutes than the package really allows. At the same time, the current system can sometimes prevent the customer from being able to use up all their free minutes in the right order.

I've already figured out how to modify the process to make sure the packages are being properly assigned to the customer's call records. Now, I'm trying to alter the way we process these records to prevent them from using more than is in their free minutes package.

I'm trying to solve this issue using a combination of CTEs, window functions, and update statements. The idea is that a customer with a free minutes package can use that package like a debit card so that if the call duration is less than the amount of minutes they have left, it covers the call and subtracts the amount of minutes left from the amount of available free minutes; but it doesn't cover the call if the duration exceeds the number of available free minutes. I've gotten almost all of it figured out, but I'm stuck because I cannot figure out how to look up a value calculated in a previous row by a window function.

Here is an example of the results that I am getting using my test table (note: Duration is in seconds, so it gets converted to minutes):

CustID  Duration    FrMinsAvailable NewAvMins   MinutesBilled   PrevAvMins
---------------------------------------------------------------------------
14000   250000      4250.2          83.5        4166.7          NULL
14000   9000        4250.2          -66.5       150             83.5
14000   4800        4250.2          -146.5      80              -66.5
14000   450         4250.2          -154        7.5             -146.5
14000   335         4250.2          -159.6      5.6             -154
14000   200         4250.2          -162.9      3.3             -159.6
14000   65          4250.2          -164        1.1             -162.9
14000   45          4250.2          -164.7      0.8             -164
14000   32          4250.2          -165.3      0.5             -164.7
14000   25          4250.2          -165.7      0.4             -165.3
14000   21          4250.2          -166        0.4             -165.7
14000   5           4250.2          -166.1      0.1             -166

Here is the result I would like to obtain:

CustID      Duration    FrMinsAvailable NewAvMins   MinutesBilled   PrevAvMins
-------------------------------------------------------------------------------
14000       250000      4250.2          83.5        4166.7          NULL
14000       9000        4250.2          83.5        150             83.5
14000       4800        4250.2          3.5         80              83.5
14000       450         4250.2          3.5         7.5             3.5
14000       335         4250.2          3.5         5.6             3.5
14000       200         4250.2          0.2         3.3             3.5
14000       65          4250.2          0.2         1.1             0.2
14000       45          4250.2          0.2         0.8             0.2
14000       32          4250.2          0.2         0.5             0.2
14000       25          4250.2          0.2         0.4             0.2
14000       21          4250.2          0.2         0.4             0.2
14000       5           4250.2          0.1         0.1             0.2

Finally, here is the test code I was using:

DECLARE @testDuration TABLE (CustID INT, Duration INT)

INSERT INTO @testDuration(CustID, Duration)
VALUES (14005, 65), (14005, 200), (14005, 4800), (14005, 25),
       (14005, 5), (14005, 450), (14005, 21), (14005, 32),
       (14005, 335), (14005, 45), (14005, 9000), (14005, 250000);

WITH my_cte AS 
(
    SELECT
        d.CustID,
        d.Duration,
        fm.FrMinsAvailable,
        ROUND((fm.FrMinsAvailable-SUM(CAST(d.Duration AS FLOAT) / 60)
                OVER (PARTITION BY d.CustID ORDER BY d.Duration DESC)), 1) NewAvMins,
        ROUND((CAST(d.Duration AS FLOAT) / 60), 1) BillMins
    FROM 
        (SELECT '14000' CustID, '4250.2' FrMinsAvailable) fm
    INNER JOIN 
        @testDuration   ON fm.CustID = d.SerialNoID
    GROUP BY 
        d.CustID, d.Duration, fm.FrMinsAvailable
)
SELECT 
    my_cte.*,
    (LAG(my_cte.NewAvMins) OVER (PARTITION BY my_cte.CustID ORDER BY my_cte.Duration DESC)) PrevAvMins
FROM 
    my_cte

What I ultimately intend to do is to use these results to set a value that allows the customer to get free minutes for that call where MinutesBilled <= PrevAvMins. If you're asking why I don't just create a bunch of tables to accomplish this, it's really that I'm trying to reduce the load on the server running this process by minimizing reads and writes because the whole procedure to process all the records received each day already involves a lot of records, takes several hours to complete, and the server has other procedures to run, as well.

The solution doesn't have to involve window functions and CTEs, but that was the best solution I came up with. I'd really appreciate some good feedback! :)

Thanks!

P.S. I'm using SQL Server 2014.

Author:Craig,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762428/sql-server-debit-card-conditional-transactions
yy