Doubt about logic in T-SQL
-
Hey All.
I need some help with T-SQL. I've been struggling for many many hours with it and it is pissing me up.
I can't use loops, cursors, etc. This is because I need high performance.
I would be very grateful if you may help me.
Here is my problem:
I have a table called __tt_Freight_Product which has the following columns:
dt_reference_date, id_contract, qtt_terminal_loaded
And a table called Product which has the following columns:
dt_reference_date, id_contract, id_fixing, qtt_fixing, qtt_terminal
There are NO foreign key between them, yet id_contract and dt_reference_date are supposed to be the same.
Example data on __tt_Freight_Product:
('2015-02-25', '0000006-t12', 200000)
('2015-02-26', '0000006-t12', 200000)
('2015-02-28', '0000006-t12', 100000)
That data means that 200,000 TONS was loaded on contract '0000006-t12' into terminal on '2015-02-25', and so on
Example data on Product:
('2015-02-24', '0000006-t12', 1, 300000, 0)
('2015-02-25', '0000006-t12', 1, 300000, 0)
('2015-02-26', '0000006-t12', 1, 300000, 0)
('2015-02-27', '0000006-t12', 1, 300000, 0)
('2015-02-28', '0000006-t12', 1, 300000, 0)
('2015-02-29', '0000006-t12', 1, 300000, 0)
('2015-02-24', '0000006-t12', 2, 200000, 0)
('2015-02-25', '0000006-t12', 2, 200000, 0)
('2015-02-26', '0000006-t12', 2, 200000, 0)
('2015-02-27', '0000006-t12', 2, 200000, 0)
('2015-02-28', '0000006-t12', 2, 200000, 0)
('2015-02-29', '0000006-t12', 2, 200000, 0)
What I need to accomplish is to split the loaded volumes that are on __tt_Freight_Product into the Product table, per dt_reference_date/id_contract/id_fixing. That splited volume will be updated on the Product table.
It's kinda we had to divide the volume per "id_fixing".
So, considering the example data on __tt_Freight_Product we will update:
dt_reference_date: '2015-02-24'
Nothing will be updated as the cargo loaded just con 25th
dt_reference_date: '2015-02-25'
qtt_terminal of id_fixing "1" will be updated to 200,000
qtt_terminal of id_fixing "2" won't change
dt_reference_date: '2015-02-26'
qtt_terminal of id_fixing "1" will be updated to 300,000
qtt_terminal of id_fixing "2" will be updated to 100,000
dt_reference_date: '2015-02-27'
qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 27th OR considering we already have id_fixing '1' "*full allocated*"
qtt_terminal of id_fixing "2" will be updated to 100,000, as we had cargo on the previous day and no loaded cargo on 27th
dt_reference_date: '2015-02-28'
qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 27th OR considering we already have id_fixing '1' "*full allocated*"
qtt_terminal of id_fixing "2" will be updated to 200,000
dt_reference_date: '2015-02-29'
qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 29th OR considering we already have id_fixing '1' "*full allocated*"
qtt_terminal of id_fixing "2" will be updated to 200,000, as we had cargo on the previous day and no loaded cargo on 29th
Any ideas?
-
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/ -
Like Sean, I could do with more of a "code" explanation. I knocked up your post in to sample data, ready to play with: -
-- CREATE sample "__tt_Freight_Product"IF object_id('tempdb..#__tt_Freight_Product') IS NOT NULL
BEGIN
DROP TABLE #__tt_Freight_Product;
END;
SELECT a.dt_reference_date,
a.id_contract,
a.qtt_terminal_loaded
INTO #__tt_Freight_Product
FROM ( VALUES ( '2015-02-25', '0000006-t12', 200000),
( '2015-02-26', '0000006-t12', 200000),
( '2015-02-28', '0000006-t12', 100000) ) a ( dt_reference_date, id_contract, qtt_terminal_loaded );
-- CREATE sample "Product"IF object_id('tempdb..#Product') IS NOT NULL
BEGIN
DROP TABLE #Product;
END;
SELECT a.dt_reference_date,
a.id_contract,
a.id_fixing,
a.qtt_fixing,
a.qtt_terminal
INTO #Product
FROM ( VALUES ( '2015-02-24', '0000006-t12', 1, 300000, 0),
( '2015-02-25', '0000006-t12', 1, 300000, 0),
( '2015-02-26', '0000006-t12', 1, 300000, 0),
( '2015-02-27', '0000006-t12', 1, 300000, 0),
( '2015-02-28', '0000006-t12', 1, 300000, 0),
( '2015-02-29', '0000006-t12', 1, 300000, 0),
( '2015-02-24', '0000006-t12', 2, 200000, 0),
( '2015-02-25', '0000006-t12', 2, 200000, 0),
( '2015-02-26', '0000006-t12', 2, 200000, 0),
( '2015-02-27', '0000006-t12', 2, 200000, 0),
( '2015-02-28', '0000006-t12', 2, 200000, 0),
( '2015-02-29', '0000006-t12', 2, 200000, 0) ) a ( dt_reference_date, id_contract, id_fixing, qtt_fixing, qtt_terminal );
But I don't understand what your expected result is. Are you planning on updating "Product", or selecting data out?
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Cadavre,
Thank you for your help.
What I expect as a result is to update the "Product" table, on the column "qtt_terminal".
Here is a picture with example output data based on input data:
-
I see no alternative other than a loop for this. Where the heck is Itzik Ben-Gan when you need him!
-- CREATE sample "__tt_Freight_Product"
IF object_id('tempdb..#__tt_Freight_Product') IS NOT NULL
BEGIN
DROP TABLE #__tt_Freight_Product;
END;
SELECT a.dt_reference_date,
a.id_contract,
a.qtt_terminal_loaded
INTO #__tt_Freight_Product
FROM ( VALUES ( '2015-02-25', '0000006-t12', 200000),
( '2015-02-26', '0000006-t12', 200000),
( '2015-02-28', '0000006-t12', 100000) ) a ( dt_reference_date, id_contract, qtt_terminal_loaded );
-- CREATE sample "Product"
IF object_id('tempdb..#Product') IS NOT NULL
BEGIN
DROP TABLE #Product;
END;
SELECT a.dt_reference_date,
a.id_contract,
a.id_fixing,
a.qtt_fixing,
a.qtt_terminal
INTO #Product
FROM ( VALUES ( '2015-02-24', '0000006-t12', 1, 300000, 0),
( '2015-02-25', '0000006-t12', 1, 300000, 0),
( '2015-02-26', '0000006-t12', 1, 300000, 0),
( '2015-02-27', '0000006-t12', 1, 300000, 0),
( '2015-02-28', '0000006-t12', 1, 300000, 0),
( '2015-02-29', '0000006-t12', 1, 300000, 0),
( '2015-02-24', '0000006-t12', 2, 200000, 0),
( '2015-02-25', '0000006-t12', 2, 200000, 0),
( '2015-02-26', '0000006-t12', 2, 200000, 0),
( '2015-02-27', '0000006-t12', 2, 200000, 0),
( '2015-02-28', '0000006-t12', 2, 200000, 0),
( '2015-02-29', '0000006-t12', 2, 200000, 0) ) a ( dt_reference_date, id_contract, id_fixing, qtt_fixing, qtt_terminal );
SELECT * FROM #__tt_Freight_Product;
DECLARE @max_id_fixing INT = (SELECT MAX(id_fixing) FROM #Product)
,@id_fixing INT = 0;
WHILE @id_fixing < @max_id_fixing
BEGIN
SELECT @id_fixing = @id_fixing + 1;
WITH AlreadyLoaded AS
(
SELECT dt_reference_date, id_contract, qtt_terminal_loaded
,al=ISNULL(
(
SELECT SUM(qtt_terminal)
FROM #Product a
WHERE a.id_contract = b.id_contract AND a.dt_reference_date = b.dt_reference_date AND
a.id_fixing < @id_fixing
), 0)
,rt=SUM(qtt_terminal_loaded) OVER
(
PARTITION BY id_contract
ORDER BY dt_reference_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM #__tt_Freight_Product b
)
UPDATE a
SET qtt_terminal = CASE SIGN(b.rt - al) WHEN -1 THEN 0 ELSE b.rt - al END
FROM #Product a
JOIN AlreadyLoaded b
ON a.id_contract = b.id_contract AND a.dt_reference_date = b.dt_reference_date
WHERE a.id_fixing = @id_fixing;
--SELECT * FROM #Product;
WITH Product AS
(
SELECT dt_reference_date, id_contract, id_fixing, qtt_fixing
,qtt_terminal
,qtt=SUM(qtt_terminal) OVER
(
PARTITION BY a.id_contract
ORDER BY a.dt_reference_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM #Product a
WHERE a.id_fixing = @id_fixing
)
UPDATE a
SET qtt_terminal = CASE WHEN qtt > qtt_fixing THEN qtt_fixing ELSE qtt END
FROM Product a;
END
SELECT * FROM #Product;
GO
DROP TABLE #Product;
DROP TABLE #__tt_Freight_Product;
Basically what this does is it allocates the incoming freight to one of your "id_fixing" terminals (?) per iteration, while throwing away that which has already been allocated to one of the terminals.
Not sure what would happen if you end up with more incoming freight than your terminals will hold, and presumably you need some additional transactions to deplete what's already been stored. But we'll leave that for another day.
Edit: Oh yes. And thanks to Cadavre (haven't seen you in a long time sir!) for setting up the test data!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
marco alves (3/24/2015)
Cadavre,Thank you for your help.
What I expect as a result is to update the "Product" table, on the column "qtt_terminal".
Here is a picture with example output data based on input data:
Will there only ever be two "id_fixing" values? If not, how many are there or could be?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/24/2015)
Will there only ever be two "id_fixing" values? If not, how many are there or could be?I hate it when you ask questions like that. Makes me think I overlooked something obvious.
My solution assumes you could have as many as you want.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (3/24/2015)
I see no alternative other than a loop for this. Where the heck is Itzik Ben-Gan when you need him!Speak and he shall appear. The OP posted this same question over at SO and guess who provided an answer?
http://stackoverflow.com/questions/29235423/struggling-with-logic-in-t-sql?noredirect=1
_______________________________________________________________
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/ -
Sean Lange (3/25/2015)
dwain.c (3/24/2015)
I see no alternative other than a loop for this. Where the heck is Itzik Ben-Gan when you need him!Speak and he shall appear. The OP posted this same question over at SO and guess who provided an answer?
http://stackoverflow.com/questions/29235423/struggling-with-logic-in-t-sql?noredirect=1
And of course it puts my solution to shame!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (3/25/2015)
Jeff Moden (3/24/2015)
Will there only ever be two "id_fixing" values? If not, how many are there or could be?I hate it when you ask questions like that. Makes me think I overlooked something obvious.
My solution assumes you could have as many as you want.
As Itzik pointed out in his post, his code isn't optimized (but it sure does give food for thought). Prior to seeing that, this looked to me like a temporal bin stacking problem. It seemed to me that it might be simpler to attack the problem horizontally than vertically but to do that without dynamic SQL, there needs to be a fixed number of horizontal bins and that's why I wanted to know if there were only 2 id_Fixing values.
Of course, Marco took Cadavre's datapost to another forum, hasn't replied back here, got an answer from Itzik that does work (even if it isn't optimized with a 57 read work table and a bit of a cross join), and didn't say thanks or anything. Itzik's sort on the Product table is where I had gotten to just by playing with the setup but stopped for the id_Fixing question. I'll save the problem as an intellectual curiosity for later on when I'm not so hammered by work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/25/2015)
dwain.c (3/25/2015)
Jeff Moden (3/24/2015)
Will there only ever be two "id_fixing" values? If not, how many are there or could be?I hate it when you ask questions like that. Makes me think I overlooked something obvious.
My solution assumes you could have as many as you want.
As Itzik pointed out in his post, his code isn't optimized (but it sure does give food for thought). Prior to seeing that, this looked to me like a temporal bin stacking problem. It seemed to me that it might be simpler to attack the problem horizontally than vertically but to do that without dynamic SQL, there needs to be a fixed number of horizontal bins and that's why I wanted to know if there were only 2 id_Fixing values.
Of course, Marco took Cadavre's datapost to another forum, hasn't replied back here, got an answer from Itzik that does work (even if it isn't optimized with a 57 read work table and a bit of a cross join), and didn't say thanks or anything. Itzik's sort on the Product table is where I had gotten to just by playing with the setup but stopped for the id_Fixing question. I'll save the problem as an intellectual curiosity for later on when I'm not so hammered by work.
I thought it was a bin-packing problem too at first, hence why I thought it couldn't be done without a loop.
Interesting that a new post just popped up with nearly the same requirement:
http://www.sqlservercentral.com/Forums/Topic1671558-3077-1.aspx
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply