Menu
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
670
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.
96
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With