February 19, 2010 at 8:13 pm
Hi Guys,
This ofcourse is my first post here today but I have spent whole bunch of time in working out one query (Without using loop/cursor) but still can't get it working.
Below is a requirement.
I have a table called Table1
date1 date2
1/1/2009 1/1/2009
1/2/2009
1/3/2009
1/4/2009
1/5/2009 1/5/2009
1/6/2009
1/7/2009
1/8/2009
1/9/2009
1/10/2009 1/10/2009
1/11/2009
1/12/2009
The desired output I need is
Hi Guys,
This ofcourse is my first post here today but I have spent whole bunch of time in working out one query (Without using loop/cursor) but still can't get it working.
Below is a requirement.
I have a table called Table1
date1 date2
1/1/2009 1/1/2009
1/2/2009 1/1/2009
1/3/2009 1/1/2009
1/4/2009 1/1/2009
1/5/2009 1/5/2009
1/6/2009 1/5/2009
1/7/2009 1/5/2009
1/8/2009 1/5/2009
1/9/2009 1/5/2009
1/10/2009 1/10/2009
1/11/2009 1/10/2009
1/12/2009 1/10/2009
Can anyone please help in giving me a clue?
I don't want to user cursors/loop/CTE
I just need to have this achieved with a complex sub-query.
February 19, 2010 at 8:23 pm
To receive relatively quick assistance please post your table definition, sample data / desired output and what work you have attempted as per the directions in the first link in my signature block
February 19, 2010 at 8:45 pm
hi there, below are the steps as per your instructions. Please see if you can help me out
if OBJECT_ID('tempdb..#tmp_Dates') is not null
drop table #tmp_Dates
-- TABLE CREATION
create table #tmp_Dates
(
value_date_intermediate datetime
,value_date_minus_1 datetime
)
-- VALUE INSERTION
INSERT INTO #tmp_Dates VALUES ('1/1/2009','1/1/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/2/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/3/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/4/2009')
INSERT INTO #tmp_Dates VALUES ('1/5/2009','1/5/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/6/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/7/2009')
INSERT INTO #tmp_Dates VALUES ('1/8/2009','1/8/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/9/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/10/2009')
INSERT INTO #tmp_Dates VALUES (NULL,'1/11/2009')
INSERT INTO #tmp_Dates VALUES ('1/12/2009','1/12/2009')
Desired Output
#sampleData('1/1/2009','1/1/2009')
#sampleData('1/1/2009','1/2/2009')
#sampleData('1/1/2009','1/3/2009')
#sampleData('1/1/2009','1/4/2009')
#sampleData('1/5/2009','1/5/2009')
#sampleData('1/5/2009''1/6/2009')
#sampleData('1/5/2009','1/7/2009')
#sampleData('1/8/2009','1/8/2009')
#sampleData('1/8/2009','1/9/2009')
#sampleData('1/8/2009','1/10/2009')
#sampleData('1/8/2009','1/11/2009')
#sampleData('1/12/2009','1/12/2009')
Below is a query i have written but the problem is that it start updating from the second maximum value not the first.
UPDATE t1
SET t1.value_date_intermediate = t2.maxdate
from #tmp_Dates t1
INNER JOIN
(
SELECT MAX(value_date_minus_1) as maxdate, value_date_minus_1 FROM #tmp_Dates
group by value_date_minus_1
)t2
on
t2.maxdate >=t1.value_date_minus_1
February 19, 2010 at 8:47 pm
create table #temp
(
date1 datetime null,
date2 datetime null
)
insert into #temp (date1,date2)
select '2009-01-01','2009-01-01' union all
select '2009-01-02',null union all
select '2009-01-03',null union all
select '2009-01-04',null union all
select '2009-01-05','2009-01-05' union all
select '2009-01-06',null union all
select '2009-01-07',null union all
select '2009-01-08',null union all
select '2009-01-09',null union all
select '2009-01-10','2009-01-10' union all
select '2009-01-11',null union all
select '2009-01-12',null union all
select '2009-01-13',null union all
select '2009-01-14',null union all
select '2009-01-15','2009-01-15' union all
select '2009-01-16',null union all
select '2009-01-17',null union all
select '2009-01-18',null union all
select '2009-01-19',null
declare @datetime datetime
set @datetime = null
update #temp
set @datetime = date2 = (case when date2 is not null then date2 else @datetime end)
select * from #temp
February 19, 2010 at 8:53 pm
Ohh buddy - that worked like a charm. I am such a fool not to think from basics.
February 19, 2010 at 9:01 pm
Hey guys... Please read this article[/url] for all the rules in using this form of the update statement - they must be followed or you can get erroneous results!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 19, 2010 at 11:36 pm
WayneS (2/19/2010)
Hey guys... Please read this article[/url] for all the rules in using this form of the update statement - they must be followed or you can get erroneous results!
Wayne is absolutely right - you must make sure you follow the rules and fully understand this method.
The example arun.sas posted happens to work in this case, but is absolutely not guaranteed to work in other situations.
The posted code relies on rows being processed in a certain order. It just so happens that SQL Server returns rows from a heap in insertion order (as long as no data modification operations have ever occurred). This is a side-effect, not documented, supported, or recommended.
You can mitigate the risks by following the advice in the link Wayne posted.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 20, 2010 at 2:35 am
DROP table #temp
create table #temp
(
date1 datetime null,
date2 datetime null
)
insert into #temp (date1,date2)
select '2009-01-01','2009-01-01' union all
select '2009-01-02',null union all
select '2009-01-03',null union all
select '2009-01-04',null union all
select '2009-01-05','2009-01-05' union all
select '2009-01-06',null union all
select '2009-01-07',null union all
select '2009-01-08',null union all
select '2009-01-09',null union all
select '2009-01-10','2009-01-10' union all
select '2009-01-11',null union all
select '2009-01-12',null union all
select '2009-01-13',null union all
select '2009-01-14',null union all
select '2009-01-15','2009-01-15' union all
select '2009-01-16',null union all
select '2009-01-17',null union all
select '2009-01-18',null union all
select '2009-01-19',null
UPDATE #temp SET date2 = d.date2
FROM #temp t
INNER JOIN(
-- run this part to see what it does
SELECT
date1,
date2 AS date2old,
date2 = (SELECT MAX(date2) FROM #temp WHERE date2 <= t.date1)
FROM #temp t
-- /run this part to see what it does
) d ON d.date1 = t.date1
SELECT * FROM #temp
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2010 at 2:49 am
Hi,
nice move with inner join.
But the situation always not with max date
UPDATE t
SET t.date2 = (SELECT MAX(date2) FROM #temp WHERE date2 <= t.date1)
from #temp t
However, you should see the article posted by Wayne or at least put the identity to make sure the correct sort order.
February 20, 2010 at 3:09 am
arun.sas (2/20/2010)
Hi,nice move with inner join.
But the situation always not with max date
UPDATE tSET t.date2 = (SELECT MAX(date2) FROM #temp WHERE date2 <= t.date1)
from #temp t
However, you should see the article posted by Wayne or at least put the identity to make sure the correct sort order.
The results exactly match the OP's requirements using either his/her sample data set or yours.
No index or "sort order" is required for this simple query to operate correctly. The running totals update is dependant upon row order so it's best to assume that a clustered index over the desired "sort order" is essential in every case - and even then there are caveats particularly with Enterprise edition and partitioned tables. If in doubt, use a recursive CTE instead, it's surprisingly fast and has the advantage that an UPDATE isn't required.
The OP has posted in the SS2K8 section - SS2K8 can perform tricks with correlated subqueries which substantially improve their performance over previous versions.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2010 at 3:36 am
Here's a CTE solution to this problem - just the SELECT, but easy enough to use as the input for an UPDATE FROM:
;WITH NumberedSet AS (
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY date1), date1, date2
FROM #temp),
OutputSet AS (
SELECT n.RowNum,
n.date1,
n.date2,
CarryOver = date2
FROM NumberedSet n
WHERE RowNum = 1
UNION ALL
SELECT n.RowNum,
n.date1,
date2 = ISNULL(n.date2, o.CarryOver),
CarryOver = ISNULL(n.date2, o.CarryOver)
FROM OutputSet o
INNER JOIN NumberedSet n ON n.RowNum = o.RowNum + 1)
SELECT date1, date2
FROM OutputSet
It's quite a bit more complex than the correlated subquery or running totals solutions, but well investing some time on.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2010 at 3:40 am
Hi,
I know this, friend!
The tips/advice made based on the OP query.
I puzzling in some body say it have not guaranteed to work in other situations.
February 20, 2010 at 3:58 am
arun.sas (2/20/2010)
However, you should see the article posted by Wayne or at least put the identity to make sure the correct sort order.
Are you suggesting that a column with the IDENTITY property guarantees something about sort order?
I certainly hope not, since that would be entirely wrong 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 20, 2010 at 4:05 am
Hi,
Certainly Paul,
The situations are varying from the OP.
For the OP issue, I felt its enough, it’s my point.
February 20, 2010 at 4:09 am
Hi Arun
Follow the link to Jeff Moden's article in Wayne's post above. The article explains in great detail how to perform the quirky update and also identifies those situations where the method requires modification to work properly. There's a link in paragraph 3 to an article by Lynn Pettis which describes how he solved the problems observed when the quirky update is run against partitioned tables.
The point is this: the quirky update is not guaranteed to work without at least a clustered index over the required processing order.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply