March 27, 2012 at 10:26 am
Hi All,
I have populated the test data as under
DECLARE @My_table AS TABLE (
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[DUE_DTE] [datetime] NULL,
[max_ind] [smallint] NULL,
[next_dte] [datetime] NULL
)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Sep 13 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Sep 14 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Sep 15 2008 12:00:00:000AM',6,'Sep 16 2008 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Sep 29 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Sep 30 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Oct 1 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Oct 2 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Oct 3 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Oct 4 2008 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Oct 5 2008 12:00:00:000AM',7,'Oct 6 2008 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 1 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 2 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 3 2009 12:00:00:000AM',8,'Jan 4 2009 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 25 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 26 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 27 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 28 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 29 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 30 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Jan 31 2009 12:00:00:000AM',9,'Feb 1 2009 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Apr 4 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Apr 5 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('Apr 6 2009 12:00:00:000AM',10,'Apr 7 2009 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 1 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 2 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 3 2009 12:00:00:000AM',11,'May 4 2009 12:00:00:000AM')
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 28 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 29 2009 12:00:00:000AM',NULL,NULL)
INSERT INTO @My_table ([DUE_DTE],[max_ind],[next_dte])VALUES('May 30 2009 12:00:00:000AM',12,'May 31 2009 12:00:00:000AM')
SELECT * FROM @My_table
The result is required to update the columns [max_ind] and [next_dte] from the first NOT NULL values of subsequent row; such as
Row ID 1 and 2 should contain the value of 6 and 2008-09-16 00:00:00.000, the subsequent values selected from row ID 3.
And continuing in same fashion
Row ID 4 - 9 should contain the value of 7 and 2008-10-06 00:00:00.000, the subsequent values selected from row ID 10.
and so on...!
Thank you!
March 27, 2012 at 10:38 am
How about this?
SELECT T1.ID
,T1.DUE_DTE
,max_ind = CASE WHEN T1.max_ind IS NOT NULL THEN T1.max_ind ELSE OtrApp.max_ind END
,next_dte = CASE WHEN T1.next_dte IS NOT NULL THEN T1.next_dte ELSE OtrApp.next_dte END
FROM @My_table T1
OUTER APPLY
(
SELECT TOP 1 InrTab.max_ind , InrTab.next_dte
FROM @My_table InrTab
WHERE InrTab.ID > T1.ID
AND InrTab.max_ind IS NOT NULL
AND InrTab.next_dte IS NOT NULL
ORDER BY InrTab.ID
) OtrApp
March 27, 2012 at 11:33 pm
ColdCoffee (3/27/2012)
How about this?
SELECT T1.ID,T1.DUE_DTE
,max_ind = CASE WHEN T1.max_ind IS NOT NULL THEN T1.max_ind ELSE OtrApp.max_ind END
,next_dte = CASE WHEN T1.next_dte IS NOT NULL THEN T1.next_dte ELSE OtrApp.next_dte END
FROM @My_table T1
OUTER APPLY
(
SELECT TOP 1 InrTab.max_ind , InrTab.next_dte
FROM @My_table InrTab
WHERE InrTab.ID > T1.ID
AND InrTab.max_ind IS NOT NULL
AND InrTab.next_dte IS NOT NULL
ORDER BY InrTab.ID
) OtrApp
[font="Verdana"]
Great and much appreciated 🙂
ColdCoffee, as per official documentation of APPLY operator, this operator will work only for databases with compatibility 90 or plus! But in case i have environment in which the DBMS is SQL Server 2008, but the database is of compatibility 80. :ermm: but the operator is working fine!
I have changed the table variable to a persistent database table, but the APPLY operator is still working fine. :doze:
Any findings please 🙂
And thanks again ![/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply