September 30, 2014 at 8:50 am
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
September 30, 2014 at 9:32 am
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
September 30, 2014 at 10:02 am
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
September 30, 2014 at 11:29 am
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.
September 30, 2014 at 12:40 pm
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/
October 1, 2014 at 3:23 am
@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