Trying to replace cursor

  • Hello

    I'm currently tying to optimize some queries that i have.

    Now I'm stuck with one that i don't see if it's possible to replace the cursor...

    Here it's some sample schema and data.

    -- First Table prj and typ are unique, fk from other tables

    DECLARE @First AS TABLE (Prj INT, Typ INT, UsedQT NUMERIC)

    INSERT INTO @First

    SELECT 1,1,10 UNION ALL

    SELECT 1,2,11 UNION ALL

    SELECT 1,3,12

    -- Second Table can have several PRJ+Typ combinations with different Start Dates

    DECLARE @Second AS TABLE (id INT, Prj INT, Typ INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME, AvailableQT NUMERIC)

    INSERT INTO @Second

    SELECT 1,1,1,'01/09/2014','01/09/2015',5 UNION ALL

    SELECT 2,1,1,'02/09/2014','01/09/2015',10 UNION ALL

    SELECT 3,1,1,'03/09/2014','01/09/2015',100 UNION ALL

    SELECT 4,1,2,'01/09/2014','01/09/2015',11 UNION ALL

    SELECT 5,1,3,'01/09/2014','01/09/2015',2

    I need to interact with each row from the second table, and then do this:

    - If the UsedQt < AvailableQT or doesnt exists, gets the difference AvailableQt - UsedQt

    - If the UsedQt = AvailableQt doesnt return nothing

    - If the UsedQt > AvailableQt, i need to update the UsedQt that is the UsedQt-AvailableQt to compare against the next row

    I easily do this in a Cursor, but i was thinking if it's possible to do without it?

    Thanks

  • Threw this together quickly so may not be 100% but hopefully gives you a start:

    SELECTCASEWHEN f.UsedQT < COALESCE(s1.AvailableQT, s2.AvailableQT) THEN COALESCE(s1.AvailableQT, s2.AvailableQT) - f.UsedQT

    WHEN f.UsedQT = COALESCE(s1.AvailableQT, s2.AvailableQT) THEN NULL

    WHEN f.UsedQT > COALESCE(s1.AvailableQT, s2.AvailableQT) THEN f.UsedQT - COALESCE(s1.AvailableQT, s2.AvailableQT)

    END

    FROM @First f

    LEFT JOIN @Second s1

    on f.Prj = s1.prj

    AND f.Typ = s1.Typ

    LEFT JOIN @Second s2

    on s1.Prj = s2.prj

    AND s1.Typ = s2.Typ

    AND s2.id = (s1.id + 1)

    MCITP SQL 2005, MCSA SQL 2012

  • To literal...

    When i say next row, i mean all the rows, not necessary the next id...

    In this case, i have 5 + 10 + 100 = 115, and i have a usedQt of 10 so i should interact with all rows of the same ptj + typ until the usedQt = 0 or there isn't more AvailableQt. The process should be something like this:

    5 - (usedQt 10) -> 0 (usedQt = 5)

    10 -(usedQT 5) -> 5 (usedQt = 0)

    100 - (usedQt 0) -> 100

    Sample output with cursor and temp table:

    DECLARE @Third AS TABLE(Typ INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME, Remain NUMERIC)

    DECLARE @id INT

    ,@prj INT

    ,@typ INT

    ,@qt NUMERIC(18,2)

    ,@sd SMALLDATETIME

    ,@ed SMALLDATETIME

    ,@qtUsed AS NUMERIC(18,2)

    DECLARE cur1 CURSOR FAST_FORWARD READ_ONLY FOR

    SELECT id ,

    Prj ,

    Typ ,

    StartDate ,

    EndDate,

    AvailableQT

    FROM @Second

    ORDER BY StartDate

    OPEN cur1

    FETCH NEXT FROM cur1 INTO @id,@prj,@typ,@sd,@ed,@qt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @qtUsed = 0

    SELECT @qtUsed = ISNULL(UsedQT,0)

    FROM @First

    WHERE Prj = @prj AND Typ = @typ

    PRINT CAST(@qtUsed AS NVARCHAR) + ' ---- ' + CAST(@qt AS NVARCHAR)

    IF @qtUsed = 0 OR @qtUsed < @QT -- Available QT > 0

    BEGIN

    INSERT INTO @Third

    ( Typ, StartDate, EndDate, Remain )

    VALUES (@typ,@sd,@ed,@qt-@qtUsed)

    DELETE FROM @First WHERE Prj = @prj AND Typ = @typ

    END

    ELSE IF @qtUsed = @QT

    DELETE FROM @First WHERE Prj = @prj AND Typ = @typ

    ELSE IF @qtUsed > @QT

    UPDATE @First

    SET UsedQT = @qtUsed - @QT

    WHERE Prj = @prj AND Typ = @typ

    FETCH NEXT FROM cur1 INTO @id,@prj,@typ,@sd,@ed,@qt

    END

    CLOSE cur1

    DEALLOCATE cur1

    SELECT * FROM @Third

  • I'm still a little unsure on what it is you're trying to get at, but you may want to look research Quirky Updates.

    A word of caution: please note that this is an undocumented feature of SQL Server. When properly implemented it will reliably work, but if you are not familiar with what they do or the nuances of them, I strongly suggest you read up on how they work. Jeff Moden has a great post about how they work.

    http://www.sqlservercentral.com/articles/T-SQL/68467/

    Often a properly written quirky update will be the fastest way to serially move through a table, modifying values on one row and then using them on the next.

    Executive Junior Cowboy Developer, Esq.[/url]

  • So what should the output be based on your sample data?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • @JeeTree

    Thanks for the info and for the links.

    @sean Lange

    The output in the sample it's the content of the @Third table.

    I'll try to put the problem in another perspective.

    Assuming that prj = worker and typ = work order

    So the first table lets say that's the amount of hours already used by one worker in each work order type.

    The second table it's like the kind of schedule of work orders that some worker must do, so for the same kind of work i can have several wos, and each one has a start date and a due date.

    What i need to get it's the work orders that still have hours to use, so i need to run through every wos, the work orders are order by date to use the old ones first, and subtract the amount of hours that the worker already used...

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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