October 7, 2011 at 2:39 am
Hi,
I have two tables:
-- holding some values for dates
CREATE TABLE vals (dt DATETIME, val INT)
-- holding differences which must be applied to values in Vals table starting by dt date.
CREATE TABLE diffs (dt DATETIME, diff INT)
Content of the tables look following:
Vals table:
1.1.20122
2.1.20124
3.1.20125
4.1.20126
5.1.20127
6.1.20128
7.1.20125
8.1.20123
Diffs table:
4.1.20120,1
8.1.20120,5
I need to update Vals without recursion or cursor or loops to contain following data:
New content of Val table
1.1.20122
2.1.20124
3.1.20125
4.1.20126,1original value + difference from 4.1.2012 (0,1)
5.1.20127,1original value + difference from 4.1.2012
6.1.20128,1original value + difference from 4.1.2012
7.1.20125,1original value + difference from 4.1.2012
8.1.20123,6original value + difference from 4.1.2012 + difference from 8.1.2012 (0,5)
Do you have any idea how to do it without cursors or looping?
Thanks
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
October 7, 2011 at 3:39 am
something like this should work, it may need some twekaing for large data sets.
CREATE Table #vals (dt DateTime, val int)
Create table #diff (dt DateTime, diff Int)
Insert into #vals
VALUES ('1-JAN-2012', 2)
Insert into #vals
VALUES('2-JAN-2012', 4)
Insert into #vals
VALUES('3-JAN-2012', 5)
Insert into #vals
VALUES('4-JAN-2012', 6)
Insert into #vals
VALUES('5-JAN-2012', 7)
Insert into #vals
VALUES('6-JAN-2012', 8)
Insert into #vals
VALUES('7-JAN-2012', 5)
Insert into #vals
VALUES('8-JAN-2012', 3)
Insert into #diff
VALUES('4-JAN-2012', 1)
Insert into #diff
VALUES('8-JAN-2012', 5)
Select
*
,val + isNull((Select SUM(diff)
From #diff d1
Where d1.dt<=v.dt),0)
From
#vals v
LEFT JOIN #diff d on v.dt=d.dt
Update v
Set
val=val + isNull((Select SUM(diff)
From #diff d1
Where d1.dt<=v.dt),0)
From
#vals v
LEFT JOIN #diff d on v.dt=d.dt
Select * from #vals
drop table #vals
drop table #diff
PS : The selects before and after the update are just to show its working as expected.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 3:58 am
Thanks very much!
I wrote exactly same query in the meantime. Only thing I would change for metioned large datasets is adding WHERE clause to last update which filters only rows which have some difference in Diff table. It's just because that correlated subquery in SET will be probably expensive for large sets.
Thanks again.
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
October 7, 2011 at 4:43 am
I agree, though I dont think you can do that as its not just the rows that have values against them, you have to sum up all the previous ones.
Edit : Code removed wouldnt have worked.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 5:44 am
For such types of query , it could be proceeded like below ..
However if the DIffs table contain quite much data entity ,then just we would dispense Temp varialbles below and supersed them by temp table but using Create temptable command not delcare temptable commands to get advantage of temp clustered / nonclustered indexes as well for no longer usage of any Loops or Cursosrs ...etc for the optimal performance hoped for such business logics
declare @diff1 int
declare @diff2 int
select @diff1 = DIFF from diffs where dt ='4.1.2012'
select @diff2 = DIFF from diffs where dt ='8.1.2012'
update vals set val = case when vals.dt>'3.1.2012' and vals.dt<'8.1.2012' then newvalue1 when vals.dt='8.1.2012' then newvalue2 else orgval end from
(select (val+@diff1) as newvalue1 ,( val+@diff1+@diff2) as newvalue2, val as orgval, dt from vals ) temp inner join vals on temp.dt=vals.dt
Please let me know your input
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 7, 2011 at 7:20 am
Performace Guard (Shehap) (10/7/2011)
For such types of query , it could be proceeded like below ..However if the DIffs table contain quite much data entity ,then just we would dispense Temp varialbles below and supersed them by temp table but using Create temptable command not delcare temptable commands to get advantage of temp clustered / nonclustered indexes as well for no longer usage of any Loops or Cursosrs ...etc for the optimal performance hoped for such business logics
declare @diff1 int
declare @diff2 int
select @diff1 = DIFF from diffs where dt ='4.1.2012'
select @diff2 = DIFF from diffs where dt ='8.1.2012'
update vals set val = case when vals.dt>'3.1.2012' and vals.dt<'8.1.2012' then newvalue1 when vals.dt='8.1.2012' then newvalue2 else orgval end from
(select (val+@diff1) as newvalue1 ,( val+@diff1+@diff2) as newvalue2, val as orgval, dt from vals ) temp inner join vals on temp.dt=vals.dt
Please let me know your input
I'm not sure what your Temp table would look like, or if it would be any different to the Diffs table.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 7:27 am
It could be like Diffs so no need to create that temp table like our current case
or subset of Diffs according if it contains much data entity there.
This is just to cover more generic view point about such business cases.
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 7, 2011 at 7:38 am
Any chance you could provide the code for the temp table method you're suggestion as I'm always looking for alternatives to existing methods.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 7, 2011 at 7:45 am
Jakub Dvorak (10/7/2011)
...I need to update Vals without recursion or cursor or loops...
Why not? This is a running totals update. The fastest way to do it is a quirky update, the second fastest is a recursive CTE. The method you're exploring in more detail is a triangular join which will grind your server into the ground on larger data sets. A cursor-driven method would almost certainly be much faster.
Tell us a little more about the problem. How many rows in the two tables? Do you really need to write back to the vals table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 9, 2011 at 7:25 pm
Jakub Dvorak (10/7/2011)
Thanks very much!I wrote exactly same query in the meantime. Only thing I would change for metioned large datasets is adding WHERE clause to last update which filters only rows which have some difference in Diff table. It's just because that correlated subquery in SET will be probably expensive for large sets.
Thanks again.
Yes, it can be quite expensive. See the following article for why...
http://www.sqlservercentral.com/articles/T-SQL/61539/
I have to disagree with some of my colleagues... recursive CTE's are also quite expensive and can be just as bad or worse than a Cursor and While Loop or a Temp Table and Loop.
How many rows are we talking about this possibly growing to in the next 2 years in each table?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply