Update Data Group Wise in a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Data Group Wise in a Table

Author

Topic

MaveriK
Starting Member

7 Posts

Posted - 2015-03-04 : 12:04:42

Hi,

I have to write an update script on a table with million records. The required scenario is as follows:
CREATE TABLE Test_Table(
TID INT,
TDESC VARCHAR(10),
F_DT DATE,
T_DT DATE)
The Test_Table has below data:
INSERT INTO Test_Table VALUES(1,'Desc 1.0','01-02-2012',NULL)
INSERT INTO Test_Table VALUES(1,'Desc 1.2','01-05-2012',NULL)
INSERT INTO Test_Table VALUES(1,'Desc 1.3','01-02-2013',NULL)
INSERT INTO Test_Table VALUES(2,'Desc 2.0','01-02-2012',NULL)
INSERT INTO Test_Table VALUES(2,'Desc 2.1','01-02-2014',NULL)

I need to write a script to update(basically SCD Type 2 update) the T_DT column as below:

1,'Desc 1.0','01-02-2012','30-04-2012'
1,'Desc 1.2','01-05-2012','31-01-2013'
1,'Desc 1.3','01-02-2013','31-12-9999'
2,'Desc 2.0','01-02-2012','31-01-2014'
2,'Desc 2.1','01-02-2014','31-12-9999'

I need to generate rank by grouping on the TID column(one identifier column) with order by on the F_DT and update the T_DT based on the next records F_DT-1day(1 day less).

Can someone please help me with the scripting part.

TIA.

Thanks & Regards
MaveriK

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-05 : 01:46:22

[code]
SET DATEFORMAT DMY

IF OBJECT_ID ('tempDB..#Test_Table') IS NOT NULL
BEGIN
DROP TABLE #Test_Table;
END

CREATE TABLE #Test_Table(
TID INT,
TDESC VARCHAR(10),
F_DT DATE,
T_DT DATE)

INSERT INTO #Test_Table VALUES(1,'Desc 1.0','01-02-2012',NULL)
INSERT INTO #Test_Table VALUES(1,'Desc 1.2','01-05-2012',NULL)
INSERT INTO #Test_Table VALUES(1,'Desc 1.3','01-02-2013',NULL)
INSERT INTO #Test_Table VALUES(2,'Desc 2.0','01-02-2012',NULL)
INSERT INTO #Test_Table VALUES(2,'Desc 2.1','01-02-2014',NULL)

UPDATE A
SET
A.T_DT = ISNULL(B.T_DT_NEW,'99991231')
FROM #Test_Table A
OUTER APPLY
(
SELECT TOP(1)
DATEADD(day,-1 , B.F_DT ) AS T_DT_New
FROM #Test_Table B
WHERE
A.TID = B.TID
AND A.F_DT < B.F_DT
ORDER BY B.F_DT ASC
) B
SELECT * FROM #Test_Table
[/code]

[code]
TID TDESC F_DT T_DT
1 Desc 1.0 2012-02-01 2012-04-30
1 Desc 1.2 2012-05-01 2013-01-31
1 Desc 1.3 2013-02-01 9999-12-31
2 Desc 2.0 2012-02-01 2014-01-31
2 Desc 2.1 2014-02-01 9999-12-31
[/code]

sabinWeb MCP

MaveriK
Starting Member

7 Posts

Posted - 2015-03-05 : 10:47:54

Thank you so much for your help.
But would like to know if there is another way without using APPLY operator. would probably implement the logic using HANA SQL so hoping for an alternative.

Thanks & Regards
MaveriK

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-06 : 01:09:01

[code]
UPDATE A
SET
T_DT = ISNULL(
(SELECT TOP(1) DATEADD(day,-1 , B.F_DT ) AS T_DT_New
FROM #Test_Table B
WHERE
A.TID = B.TID
AND A.F_DT < B.F_DT
ORDER BY B.F_DT ASC)
,'99991231')
FROM
#Test_Table A

SELECT * FROM #Test_Table
[/code]

sabinWeb MCP

MaveriK
Starting Member

7 Posts

Posted - 2015-03-06 : 02:56:58

Thank you again.

Thanks & Regards
MaveriK

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-06 : 03:09:46

Welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources