May 30, 2024 at 5:25 pm
Hello
I have a query that works as a loop but it is slow. How can I do this with a standard query?
IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'LINES')
BEGIN
DROP TABLE LINES
END
GO
CREATE TABLE LINES
(
ID INT IDENTITY(1,1),
CODE NVARCHAR(100),
DATE_ DATETIME,
TIP NVARCHAR(100),
AMOUNT DECIMAL(20,10),
PRICE DECIMAL(20,10),
TUTAR DECIMAL(20,10),
BLANCEAMOUNT DECIMAL(20,10),
OUTCOST DECIMAL(20,10),
)
GO
INSERT INTO LINES (CODE,DATE_, TIP, AMOUNT, PRICE, TUTAR, BLANCEAMOUNT)
VALUES
('0001','2024-11-01', 'IN', 10, 75, 750, 10),
('0001','2024-11-15', 'EXIT', 5, 100, 500, 5),
('0001','2024-11-17', 'IN', 2, 85, 170, 7),
('0001','2024-12-15', 'EXIT', 2, 125, 250, 5),
('0001','2024-12-16', 'IN', 4, 85, 340, 9),
('0001','2024-12-18', 'IN', 2, 95, 190, 11),
('0001','2024-12-19', 'IN', 30, 97, 2910, 41),
('0001','2024-12-20', 'EXIT', 40, 150, 6000, 1)
GO
DECLARE @TOTALAMOUNT DECIMAL(20,10) = 0;
DECLARE @TOTALCOST DECIMAL(20,10) = 0;
DECLARE @KalanAMOUNT DECIMAL(20,10) = 0;
DECLARE @ID INT;
DECLARE @TIP NVARCHAR(100);
DECLARE @AMOUNT DECIMAL(20,10);
DECLARE @PRICE DECIMAL(20,10);
DECLARE @TUTAR DECIMAL(20,10);
-- Cursor tanımlama
DECLARE LINES_cursor CURSOR FOR
SELECT ID, TIP, AMOUNT, PRICE
FROM LINES
ORDER BY DATE_, ID;
OPEN LINES_cursor;
FETCH NEXT FROM LINES_cursor INTO @ID, @TIP, @AMOUNT, @PRICE;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TIP = 'IN'
BEGIN
SET @TOTALAMOUNT = @TOTALAMOUNT + @AMOUNT;
SET @TOTALCOST = @TOTALCOST + (@AMOUNT * @PRICE);
END
ELSE IF @TIP = 'EXIT'
BEGIN
SET @KalanAMOUNT = @TOTALAMOUNT - @AMOUNT;
IF @KalanAMOUNT > 0
BEGIN
SET @TOTALCOST = (@TOTALCOST * (@TOTALAMOUNT - @AMOUNT)) / @TOTALAMOUNT;
SET @TOTALAMOUNT = @KalanAMOUNT;
END
ELSE
BEGIN
SET @TOTALCOST = 0;
SET @TOTALAMOUNT = 0;
END
END
UPDATE LINES
SET OUTCOST = CASE
WHEN @TOTALAMOUNT > 0 THEN @TOTALCOST / @TOTALAMOUNT
ELSE NULL
END
WHERE ID = @ID;
FETCH NEXT FROM LINES_cursor INTO @ID, @TIP, @AMOUNT, @PRICE;
END
CLOSE LINES_cursor;
DEALLOCATE LINES_cursor;
SELECT * FROM LINES
May 31, 2024 at 12:55 am
Is this really SQL Server 2012? Extended support for SQL Server 2012 ended last year. Why are the AMOUNT and PRICE of data type FLOAT? Generally, columns with those names contain deterministic data types, like INT or DECIMAL, while FLOAT is an approximate data type. So there's an immediate loss of precision which can cause rounding errors (forever). Also, what's the expected output?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 31, 2024 at 6:40 am
This code is working slowly on current system 2008, I changed the data type to decimal(20,10)
I am updating data to the OUTCOST field. Since I'm doing this in a loop, it's slow. Actually how can I do this loop with standard query.
My current system is SQL 2012
May 31, 2024 at 6:56 am
I WRITE A QUERY BUT IT RETURNED THE WRONG RESULT
help please
WITH CTE_LINES AS (
SELECT
CODE, DATE_, TIP, AMOUNT, PRICE,OUTCOST,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALINAMOUNT,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT * PRICE ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALOUTCOST,
SUM(CASE WHEN TIP = 'EXIT' THEN AMOUNT ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALEXITAMOUNT
FROM LINES
)
SELECT
CODE, DATE_, TIP, AMOUNT, PRICE, TOTALINAMOUNT, TOTALOUTCOST, TOTALEXITAMOUNT,
CASE
WHEN TIP = 'IN' THEN
(TOTALOUTCOST - COALESCE(LAG(TOTALOUTCOST, 1, 0) OVER (ORDER BY DATE_), 0) + AMOUNT * PRICE)
/ (TOTALINAMOUNT - COALESCE(LAG(TOTALINAMOUNT, 1, 0) OVER (ORDER BY DATE_), 0) + AMOUNT)
ELSE
(TOTALOUTCOST - (TOTALOUTCOST / TOTALINAMOUNT) * AMOUNT)
/ (TOTALINAMOUNT - AMOUNT)
END AS OUTCOST_QUERY_WRONG,
OUTCOST AS OUTCOST_LOOP_TRUE
FROM CTE_LINES
May 31, 2024 at 7:50 am
It would be interesting to understand what this data represents.
As all of the rows have Code 0001, do they all refer to the same object? And yet the price changes from one row to the next, suggesting not.
What do IN and EXIT mean in the TIP column? They sound like opposites, yet there are no negative values in the data.
Can you explain the logic for the calculation of OUTCOST in words?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 31, 2024 at 8:08 am
Hi,
EXIT means exited amount
---
WITH CTE_LINES AS (
SELECT
CODE, DATE_, TIP, AMOUNT, PRICE,OUTCOST,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALINAMOUNT,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT * PRICE ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALOUTCOST,
SUM(CASE WHEN TIP = 'EXIT' THEN AMOUNT ELSE 0 END)
OVER (ORDER BY DATE_) AS TOTALEXITAMOUNT,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT ELSE -AMOUNT END)
OVER (ORDER BY DATE_) AS BALANCE ,
SUM(CASE WHEN TIP = 'IN' THEN AMOUNT ELSE -AMOUNT END)
OVER (ORDER BY DATE_)-(CASE WHEN TIP = 'IN' THEN AMOUNT ELSE -AMOUNT END) AS BALANCEO
FROM LINES
)
SELECT
CODE, DATE_, TIP, AMOUNT,BALANCE,BALANCEO, PRICE, TOTALINAMOUNT, TOTALOUTCOST, TOTALEXITAMOUNT,
CASE
WHEN TIP = 'IN' THEN
(TOTALOUTCOST - COALESCE(LAG(TOTALOUTCOST, 1, 0) OVER (ORDER BY DATE_), 0) + AMOUNT * PRICE)
/ (TOTALINAMOUNT - COALESCE(LAG(TOTALINAMOUNT, 1, 0) OVER (ORDER BY DATE_), 0) + AMOUNT)
ELSE
(TOTALOUTCOST - (TOTALOUTCOST / TOTALINAMOUNT) * AMOUNT)
/ (TOTALINAMOUNT - AMOUNT)
END AS OUTCOST_QUERY_WRONG,
OUTCOST AS OUTCOST_LOOP_TRUE
FROM CTE_LINES
--- IN --->> 2024-11-15 00:00:00.000
--- (10 AMOUNT * 75 PRICE) /BALANCE = 75 OUTCOST
--- EXIT --->> 2024-11-15 00:00:00.000
--- = 75 OUTCOST
--- IN --->> 2024-11-17 00:00:00.000
--- (2 AMOUNT * 85 PRICE)+(5 BALANCEO * 75 )/ BALANCE = OUTCOST 77.8571428571
--- 2024-12-15 00:00:00.000
--- = 77.8571428571 OUTCOST
....
May 31, 2024 at 8:48 am
I tried to follow your logic for the row where ID = 5.
- IN --->> 2024-12-16
- (4 AMOUNT * 85 PRICE)+(5 BALANCEO * 85 )/ (BALANCE 9) = OUTCOST 85
What am I doing wrong?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 31, 2024 at 9:19 am
Thank you
---- 2024-12-16 00:00:00.000
---- (85 PRICE * 4 AMOUNT) + (77.8571428000*5)/ BALANCE=9 OUTCOST = 81.0317460000
---- 2024-12-18 00:00:00.000
---- (95 PRICE * 2 AMOUNT) + (81.0317460000*9)/ BALANCE=11 OUTCOST = 83.5714285455
May 31, 2024 at 10:02 am
IF @TIP = 'IN'
BEGIN
SET @TOTALAMOUNT = @TOTALAMOUNT + @AMOUNT;
SET @TOTALCOST = @TOTALCOST + (@AMOUNT * @PRICE);
END
ELSE IF @TIP = 'EXIT'
BEGIN
SET @KalanAMOUNT = @TOTALAMOUNT - @AMOUNT;
IF @KalanAMOUNT > 0
BEGIN
SET @TOTALCOST = (@TOTALCOST * (@TOTALAMOUNT - @AMOUNT)) / @TOTALAMOUNT;
SET @TOTALAMOUNT = @KalanAMOUNT;
END
ELSE
BEGIN
SET @TOTALCOST = 0;
SET @TOTALAMOUNT = 0;
END
END
This will be really difficult, if not impossible, to do with Window functions as TotalCost is partly dependant on the LAG of TotalCost which is part of a calculation. I would be inclined to do this sort of calculation in the middle tier or in a reporting tool. If you really need to do this in SQL, I would be inclined to look at the Quirky Update. You should read the whole article, and comments, to be aware of the potential problems.
May 31, 2024 at 2:31 pm
I am trying to find a solution in the first code I shared, but it is slow. I think it can be solved with WITH.
I reviewed the link. Thank you. I am working on it.
May 31, 2024 at 7:34 pm
The following Quirky Update solution seems to work with the data provided:
SELECT ISNULL(CODE, '') AS CODE, ISNULL(ID, 0) AS ID, TIP, AMOUNT, PRICE
,PRICE - PRICE AS OUTCOST
INTO #t
FROM LINES;
ALTER TABLE #t
ADD PRIMARY KEY (CODE, ID);
DECLARE @PrevCode nvarchar(100) = ''
,@PrevId int = 0
,@TotalAmount decimal(20,10) = 0
,@TotalCost decimal(20,10) = 0
,@OutCost decimal(20,10) = 0
,@Check int;
UPDATE T
SET @TotalCost = CASE WHEN TIP = 'IN' THEN @TotalCost + (AMOUNT * PRICE) ELSE @TotalCost * (@TotalAmount - AMOUNT) / @TotalAmount END
,@TotalAmount = @TotalAmount + CASE WHEN TIP = 'IN' THEN AMOUNT ELSE -AMOUNT END
,@OutCost =
CASE
WHEN CODE <> @PrevCode
THEN PRICE
ELSE @TotalCost / @TotalAmount
END
,OutCost = @OutCost
,@check =
CASE
WHEN Code > ISNULL(@PrevCode, '')
THEN 1
WHEN Code = @PrevCode AND Id > @PrevId
THEN 1
ELSE 1/0
END
,@PrevId = Id
,@PrevCode = Code
FROM #t T WITH (TABLOCKX)
OPTION (MAXDOP 1);
ALTER TABLE #t
ADD UNIQUE (Id, OutCost);
UPDATE L
SET OutCost = T.OUTCOST
FROM Lines L
JOIN #t T
ON L.ID = T.ID;
select * from LINES;
June 1, 2024 at 8:12 am
Thank you for your interest. When there is a different issue, different values come up. Success is achieved through a single code.
thanks
('0002','2024-11-01', 'IN', 10, 75, 750, 10), ----outcost = 75
('0002','2024-11-15', 'EXIT', 5, 100, 500, 5) ---outcost = 76.6724736667 (75 should be)
June 1, 2024 at 8:36 am
The following will reset the Total variables on the code boundary:
SELECT ISNULL(CODE, '') AS CODE, ISNULL(ID, 0) AS ID, TIP, AMOUNT, PRICE
,PRICE - PRICE AS OUTCOST
INTO #t
FROM LINES;
ALTER TABLE #t
ADD PRIMARY KEY (CODE, ID);
DECLARE @PrevCode nvarchar(100) = ''
,@PrevId int = 0
,@TotalAmount decimal(20,10) = 0
,@TotalCost decimal(20,10) = 0
,@Check int;
UPDATE T
SET @TotalCost = CASE WHEN Code > ISNULL(@PrevCode, '') THEN 0 ELSE @TotalCost END
,@TotalAmount = CASE WHEN Code > ISNULL(@PrevCode, '') THEN 0 ELSE @TotalAmount END
,@TotalCost = CASE WHEN TIP = 'IN' THEN @TotalCost + (AMOUNT * PRICE) ELSE @TotalCost * (@TotalAmount - AMOUNT) / @TotalAmount END
,@TotalAmount = @TotalAmount + CASE WHEN TIP = 'IN' THEN AMOUNT ELSE -AMOUNT END
,OutCost =
CASE
WHEN CODE <> @PrevCode
THEN PRICE
ELSE @TotalCost / @TotalAmount
END
,@check =
CASE
WHEN Code > ISNULL(@PrevCode, '')
THEN 1
WHEN Code = @PrevCode AND Id > @PrevId
THEN 1
ELSE 1/0
END
,@PrevId = Id
,@PrevCode = Code
FROM #t T WITH (TABLOCKX)
OPTION (MAXDOP 1);
ALTER TABLE #t
ADD UNIQUE (Id, OutCost);
UPDATE L
SET OutCost = T.OUTCOST
FROM Lines L
JOIN #t T
ON L.ID = T.ID;
select * from LINES;
June 1, 2024 at 9:58 am
Thank you
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply