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.
| 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 |
|
|
|
|
|
|
|
|
Update Data Group Wise in a Table