Adapt the query to 2012

  • 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

     

  • 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

  • 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

  • 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

  • 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?

     

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

    ....
  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • Excel

    Attachments:
    You must be logged in to view attached files.
  • meryemkurs072 wrote:

        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.

     

  • 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.

  • 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;
  • 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)


  • 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;
  • Thank you

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply