Translate Excel business logic to T-SQL

Question

I need to 'translate' some business logic from Excel to T-SQL, I'm having a hard time with it.

It's about figures from gates that count how many customers go IN and OUT of the stores.

All DATA you need is in the following table:

CREATE TABLE #ResultsTable 
(
    Datum DATETIME,
    window CHAR(10),
    countersOUT INT,
    countersIN INT,
    RESULT INT
)

INSERT INTO #ResultsTable 
VALUES ('20180104 08:30:00.000', '08:30', 0, 0, 0),
       ('20180104 09:00:00.000', '09:00', 2, 1, 1),
       ('20180104 09:30:00.000', '09:30', 1, 0, 2),
       ('20180104 10:00:00.000', '10:00', 25, 9, 18),
       ('20180104 10:30:00.000', '10:30', 45, 41, 22),
       ('20180104 11:00:00.000', '11:00', 38, 37, 23),
       ('20180104 11:30:00.000', '11:30', 50, 51, 22),
       ('20180104 12:00:00.000', '12:00', 21, 24, 19),
       ('20180104 12:30:00.000', '12:30', 12, 19, 12),
       ('20180104 13:00:00.000', '13:00', 25, 18, 19),
       ('20180104 13:30:00.000', '13:30', 35, 27, 27),
       ('20180104 14:00:00.000', '14:00', 81, 9, 52),
       ('20180104 14:30:00.000', '14:30', 113, 18, 70),
       ('20180104 15:00:00.000', '15:00', 116, 34, 71),
       ('20180104 15:30:00.000', '15:30', 123, 36, 54),
       ('20180104 16:00:00.000', '16:00', 127, 35, 50),
       ('20180104 16:30:00.000', '16:30', 103, 19, 47),
       ('20180104 17:00:00.000', '17:00', 79, 31, 27),
       ('20180104 17:30:00.000', '17:30', 50, 16, 26),
       ('20180104 18:00:00.000', '18:00', 28, 11, 17),
       ('20180104 18:30:00.000', '18:30', 16, 15, 2),
       ('20180104 19:00:00.000', '19:00', 0, 2, 0),
       ('20180104 19:30:00.000', '19:30', 0, 0, 0),
       ('20180104 20:00:00.000', '20:00', 0, 0, 0),
       ('20180104 20:30:00.000', '20:30', 0, 0, 0),
       ('20180104 21:00:00.000', '21:00', 0, 0, 0),
       ('20180104 21:30:00.000', '21:30', 0, 0, 0),
       ('20180104 22:00:00.000', '22:00', 0, 0, 0)

select * from #ResultsTable

'RESULT' is the column that should be calculated, based on 'countersOUT and 'countersIN'.

'countersOUT and 'countersIN' is the INPUT data you need for the calculation.

The business user made a help column in Excel to make the calculation (column AA) . . . . . . .From business point of view: this is the number of customers at the end of each half hour present in the shop.

Then, the actual calculation below: (screenshot also from Excel)

Note: the calculation makes use of the help column (col. AA)

Now my task is to make over this calculation in T-SQL.

The business user its only input data is 'countersIN' and 'countersOUT', in other words it should be achievable in T-SQL. Only, I don't manage, that's why I came to ask my question.

Lastly, I can advise to have a look at the Excel file (mediafire link) http://www.mediafire.com/file/mtdvlgmmbj3f8dd/Example_20190725_SQLforum.xlsx/file

Thanks a lot in advance for any help

. . You need to try to explain the logic that is being implemented. — Gordon Linoff, Jul 29 '19 at 14:09
@laurens It is a variation of capping running total. It is perfectly achieveable with T-SQL but you have to use recursive CTE to adjust logic from Excel. [Conditional SUM on Oracle](https://stackoverflow.com/a/52936314/5070879) — Lukasz Szozda, Aug 01 '19 at 09:57
@LukaszSzozda : this is interesting information, thanks, I'll look into that — laurens, Aug 01 '19 at 10:34
the data you have give in the form of a sql table has the counterout and counterin columns interchanged. Is this correct? — Rahul, Aug 01 '19 at 14:13
Explain this to me. Row 11 has result as 27, row 12 - 81 come in and 9 go out so shouldnt the result be 27 + 81 - 9? — Rahul, Aug 01 '19 at 14:30

score 2 · Answer 1 · answered Aug 01 '19 at 15:22

select *, ROW_NUMBER() over (order by datum) as rw 
into #temp 
from #ResultsTable 
order by datum

select a.datum, a.window, a.countersout, a.countersin, countersout-countersin as result, rw
into #temp1 from #temp a 

select a.datum, a.window, a.countersOUT, a.countersIN, 
case when isnull(b.result,0) + a.countersOUT - a.countersIN < 0 then a.countersIN
else (case when (isnull(b.result,0) +a.result) + a.countersOUT - a.countersIN > isnull(c.countersIN,0) + isnull(d.countersIN,0) then  isnull(c.countersIN,0) + isnull(d.countersIN,0) + a.countersIN -  (isnull(b.result,0) +a.result)
else a.countersOUT end)
end as Result, a.result + b.RESULT as A88
from #temp1 a left join #temp b
 on a.rw =b.rw + 1
 left join #temp c 
 on a.rw + 2 = c.rw
 left join #temp d 
 on a.rw + 3 = d.rw
 order by a.datum

I have copied your logic as is. But your logic stops making sense after line 12. If you can explain how that makes sense, I will edit this to give you what you want or you can tweak it yourself.

Please check my solution based on the excel file, you can see that even after row 11 the results are as the requester expected, and yes Im with you in the part that sql can do it without cursor. — Ali Al-Mosawi, Aug 02 '19 at 19:57

score 1 · Accepted Answer · answered Aug 01 '19 at 13:27

Here's a solution using a Cursor. No good style but effective, since you move through the rows. The attribut [RESULT_by_Cursor] is the computed one in comparison to your target value.

Btw: Your SQL-example has an error, the in and out - columns are twisted.

CREATE TABLE #ResultsTable 
(
    Datum DATETIME,
    window CHAR(10),
    countersIN INT,--countersOUT INT,
    countersOUT INT,--countersIN INT,
    RESULT INT,
    RESULT_by_Cursor INT,
    countersIN_corrected INT
);


INSERT INTO #ResultsTable 
VALUES ('20180104 08:30:00.000', '08:30', 0, 0, 0, NULL, NULL),
       ('20180104 09:00:00.000', '09:00', 2, 1, 1, NULL, NULL),
       ('20180104 09:30:00.000', '09:30', 1, 0, 2, NULL, NULL),
       ('20180104 10:00:00.000', '10:00', 25, 9, 18, NULL, NULL),
       ('20180104 10:30:00.000', '10:30', 45, 41, 22, NULL, NULL),
       ('20180104 11:00:00.000', '11:00', 38, 37, 23, NULL, NULL),
       ('20180104 11:30:00.000', '11:30', 50, 51, 22, NULL, NULL),
       ('20180104 12:00:00.000', '12:00', 21, 24, 19, NULL, NULL),
       ('20180104 12:30:00.000', '12:30', 12, 19, 12, NULL, NULL),
       ('20180104 13:00:00.000', '13:00', 25, 18, 19, NULL, NULL),
       ('20180104 13:30:00.000', '13:30', 35, 27, 27, NULL, NULL),
       ('20180104 14:00:00.000', '14:00', 81, 9, 52, NULL, NULL),
       ('20180104 14:30:00.000', '14:30', 113, 18, 70, NULL, NULL),
       ('20180104 15:00:00.000', '15:00', 116, 34, 71, NULL, NULL),
       ('20180104 15:30:00.000', '15:30', 123, 36, 54, NULL, NULL),
       ('20180104 16:00:00.000', '16:00', 127, 35, 50, NULL, NULL),
       ('20180104 16:30:00.000', '16:30', 103, 19, 47, NULL, NULL),
       ('20180104 17:00:00.000', '17:00', 79, 31, 27, NULL, NULL),
       ('20180104 17:30:00.000', '17:30', 50, 16, 26, NULL, NULL),
       ('20180104 18:00:00.000', '18:00', 28, 11, 17, NULL, NULL),
       ('20180104 18:30:00.000', '18:30', 16, 15, 2, NULL, NULL),
       ('20180104 19:00:00.000', '19:00', 0, 2, 0, NULL, NULL),
       ('20180104 19:30:00.000', '19:30', 0, 0, 0, NULL, NULL),
       ('20180104 20:00:00.000', '20:00', 0, 0, 0, NULL, NULL),
       ('20180104 20:30:00.000', '20:30', 0, 0, 0, NULL, NULL),
       ('20180104 21:00:00.000', '21:00', 0, 0, 0, NULL, NULL),
       ('20180104 21:30:00.000', '21:30', 0, 0, 0, NULL, NULL),
       ('20180104 22:00:00.000', '22:00', 0, 0, 0, NULL, NULL)




-- PDO: Apply Cursor to run through datasets


DECLARE @Datum DATETIME,
    @window CHAR(10),
    @countersOUT INT,           -- U
    @countersIN INT,            -- V
    @countersOUT_next INT,          -- V + 1 row
    @countersOUT_nextnext INT,          -- V + 2 rows
    @countersIN_corrected INT,
    @RESULT_by_Cursor INT;         --AA

DECLARE C_Tag CURSOR FAST_FORWARD FOR 
    SELECT Datum,
           window,
           countersOUT,
           countersIN

    FROM #ResultsTable
    ORDER BY Datum ASC
    ;


-- PDO: Cursor open and first fetch
SET @countersIN_corrected=0;
SET @RESULT_by_Cursor=0;

OPEN C_Tag

    FETCH NEXT FROM C_Tag INTO @Datum,
                               @window,
                               @countersOUT,
                               @countersIN

                               ;

    WHILE @@FETCH_STATUS=0

        BEGIN

            -- PDO: Get upcoming data in case we need them
            SET @countersOUT_next = ISNULL((SELECT TOP 1 r.countersOUT
                FROM #ResultsTable r
                WHERE r.Datum > @Datum
                ORDER BY r.Datum
                ),0)
            ;


            SET @countersOUT_nextnext = ISNULL((SELECT TOP 1 r.countersOUT
                FROM #ResultsTable r
                WHERE r.Datum > (SELECT TOP 1 r2.Datum
                                FROM #ResultsTable r2
                                WHERE r2.Datum > @Datum
                                ORDER BY r2.Datum
                                )
                ORDER BY r.Datum
                ),0)
            ;

            -- PDO: Compute correction according to Formula
            SET @countersIN_corrected=IIF(@RESULT_by_Cursor + @countersIN - @countersOUT < 0 ,

                                            @countersOUT ,

                                            IIF(@RESULT_by_Cursor + @countersIN - @countersOUT > (@countersOUT_next + @countersOUT_nextnext)    ,

                                                @countersOUT_next + @countersOUT_nextnext + @countersOUT - @RESULT_by_Cursor ,

                                                @countersIN

                                            )

                                        );



            -- PDO: Compute Result by cursor

            SET @RESULT_by_Cursor=@RESULT_by_Cursor + @countersIN_corrected - @countersOUT;



            -- PDO: Update Table with computed result

            UPDATE #ResultsTable
            SET RESULT_by_Cursor=@RESULT_by_Cursor,
                countersIN_corrected=@countersIN_corrected
            WHERE Datum=@Datum
            ;



            FETCH NEXT FROM C_Tag INTO @Datum,
                                       @window,
                                       @countersOUT,
                                       @countersIN

                                       ;


        END -- @@Fetch_Status C_Tag

CLOSE C_Tag;
DEALLOCATE C_Tag;



-- PDO: Clean Up

select * from #ResultsTable;


DROP TABLE #ResultsTable;

score 0 · Answer 3 · answered Jul 31 '19 at 14:49

0

     select r.*,
      isnull(LAG(result)  over (order by datum) ,0) - countersOUT + countersIN as AA
    from #ResultsTable r

answered Jul 31 '19 at 14:49

Christian L.

290
3
9

score 0 · Answer 4 · answered Aug 01 '19 at 18:59

0

(Not enough reputation to comment yet.)

The approach from Rahul is promising, but will not work in this special case. Any solution requires some sort of iteration - to compute the value of step n you need to use the computed value of step n-1. (With an initial value for n=0 given as 0 in the Excel file.) This is owed to the correction term. While using static data from each record respectively, the task cannot be solved.

Recursive CTE proposed by Lukasz Szozda implies an iteration and might do the job with some effort.

answered Aug 01 '19 at 18:59

Nick Oetjen

129
4

I am achieving the same thing through iterative joins. In most cases a cursor can be replaced by some joins and/or dynamic sql. If you look at what your cursor is doing and what the query i have proposed is doing, they are pretty much the same thing. Also, can you explain how 52 is the result on row 12. – Rahul Aug 02 '19 at 13:03
If excel can do it, sql can as well without using cursors. – Rahul Aug 02 '19 at 13:09
@Rahul: I am not able to reproduce the wanted results with your code. As far as I can see the problem with your solution is the term " a.result " , which is computed static upfront and not dynamic with iterations. I'd be happy if you prove me being wrong with this. Row 12 is the first row where the correction term kicks. You may want to consult the excel file. – Nick Oetjen Aug 02 '19 at 15:20
Can you please explain how does the file get 52 in AA? Maybe i didnt get the logic, but i am totally lost. Because in screenshot 1 it says AA88 + U89 - V89. If you consider the next screenshot (different column it should V13 (the value), – Rahul Aug 02 '19 at 15:43
@Rahul: It is 27 + (34 - 9 ) = 52 . You need column W instead of V from row 2 onward. W is computed using a correction term including the prior result, which is why an iteration is needed. Such an interation can be a with / Recursive CTE-clause (being nothing but a loop) or a cursor (also a loop). A cursor should be used with a unique name and preferably `with (nolock)` , avoiding the major problems. On the other hand it is easier to understand for anyone administrating it later. – Nick Oetjen Aug 05 '19 at 10:32
Thanks for the explanation! I think it can be done with joins as i have mentioned. I will try to edit my reply when i get the opportunity. Appreciate it! – Rahul Aug 05 '19 at 16:11

score 0 · Answer 5 · answered Aug 01 '19 at 19:26

The challenge here is to get the values for the next row and the values for the row next to the next row and the values for the prev row. so to get the values for the next and next next row we will use LEAD, for the prev row we cant use LAG as its calculated step by step so I will use CTE with recursive join to go row by row. My results match your expected result all the way. :)

please see the below,

;with excel as (
select 
    Datum,window,countersOUT [U5],countersIN [T5],
        LEAD(countersIN,1) over (order by Datum) [T6],
        LEAD(countersIN,2) over (order by Datum) [T7],
        RESULT
    from #ResultsTable 
),prev as (
select top(1) *
    ,IIF(U5-T5<0,T5,U5) [V] --first row equation := IF(U4-T4<0,T4,U4)
    ,IIF(U5-T5<0,T5,U5)-T5 [Z]  --first row equation :=V4-T4
    from excel order by Datum
union all
    select cur.*
    --=IF(Z4+U5-T5<0,T5,IF(Z4+U5-T5>T6+T7,T6+T7+T5-Z4,U5))
    ,IIF((prev.Z+cur.U5-cur.T5)<0,cur.T5,IIF((prev.Z+cur.U5-cur.T5)>(cur.T6+cur.T7),(cur.T6+cur.T7+cur.T5-prev.Z),cur.U5))  [V]
    --:=Z4+V5-T5 (V from the above line)
    ,prev.Z+IIF((prev.Z+cur.U5-cur.T5)<0,cur.T5,IIF((prev.Z+cur.U5-cur.T5)>(cur.T6+cur.T7),(cur.T6+cur.T7+cur.T5-prev.Z),cur.U5))-cur.T5 [Z] 
    from prev inner join excel cur on cur.Datum=dateadd(MINUTE,30,prev.Datum)
)
select Datum,window,U5 countersOUT,T5 countersIN,RESULT,Z [CalResult] from prev

The first CTE is to make our life easier by naming the columns to something similar to excel so mapping the function will be easier, anyhow in the end I return them back , my results are as below:-

Datum   window  countersOUT countersIN  RESULT  CalResult
2018-01-04 08:30:00.000 08:30       0   0   0   0
2018-01-04 09:00:00.000 09:00       2   1   1   1
2018-01-04 09:30:00.000 09:30       1   0   2   2
2018-01-04 10:00:00.000 10:00       25  9   18  18
2018-01-04 10:30:00.000 10:30       45  41  22  22
2018-01-04 11:00:00.000 11:00       38  37  23  23
2018-01-04 11:30:00.000 11:30       50  51  22  22
2018-01-04 12:00:00.000 12:00       21  24  19  19
2018-01-04 12:30:00.000 12:30       12  19  12  12
2018-01-04 13:00:00.000 13:00       25  18  19  19
2018-01-04 13:30:00.000 13:30       35  27  27  27
2018-01-04 14:00:00.000 14:00       81  9   52  52
2018-01-04 14:30:00.000 14:30       113 18  70  70
2018-01-04 15:00:00.000 15:00       116 34  71  71
2018-01-04 15:30:00.000 15:30       123 36  54  54
2018-01-04 16:00:00.000 16:00       127 35  50  50
2018-01-04 16:30:00.000 16:30       103 19  47  47
2018-01-04 17:00:00.000 17:00       79  31  27  27
2018-01-04 17:30:00.000 17:30       50  16  26  26
2018-01-04 18:00:00.000 18:00       28  11  17  17
2018-01-04 18:30:00.000 18:30       16  15  2   2
2018-01-04 19:00:00.000 19:00       0   2   0   0
2018-01-04 19:30:00.000 19:30       0   0   0   0
2018-01-04 20:00:00.000 20:00       0   0   0   0
2018-01-04 20:30:00.000 20:30       0   0   0   0
2018-01-04 21:00:00.000 21:00       0   0   0   0
2018-01-04 21:30:00.000 21:30       0   0   0   0
2018-01-04 22:00:00.000 22:00       0   0   0   0

You can see that your result match the Cal Result. Hope this helps.

If you flipped In with out in your #ResultsTable table, you can flip U5 with T5 on the 3rd line of my query and things will work normally. — Ali Al-Mosawi, Aug 01 '19 at 19:39
Hi Ali, I will have a look & test it later today. Many thanks for your reply — laurens, Aug 06 '19 at 07:25

score -2 · Answer 6 · answered Jul 29 '19 at 10:30

-2

You seem to want cumulative sums:

select r.*,
       sum(countersout - countersin) over (order by datum) as result
from #ResultsTable r;

Here is a db<>fiddle.

answered Jul 29 '19 at 10:30

Gordon Linoff

1,242,037
58
646
786

2

Hi Gordon, thanks for your proposal. I did about the same thing, it works for the first 10 rows but then it's not correct anymore. Please have a look at the excel file (mediafire link) or the screenshots above. – laurens Jul 29 '19 at 13:24
@laurens . . . Based on your description -- *From business point of view: this is the number of customers at the end of each half hour present in the shop* -- this should be doing what you want. Your counter values don't seem correct. – Gordon Linoff Jul 29 '19 at 14:10
1

That's the explanation of the help column. The actual calculation is in column 'W' – laurens Jul 29 '19 at 19:34

Translate Excel business logic to T-SQL

6 Answers6