Query Help - SQL Server Forums

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)

Query Help

Author

Topic

shady4u
Starting Member

4 Posts

Posted - 2012-07-09 : 23:07:21

I have two columns D_START_DATE AND D_END_DATE AS SHOWN BELOW
2011-10-15 00:00:00.000 2011-11-14 00:00:00.000
2011-11-14 00:00:00.000 ~~2011-11-14 00:00:00.000~~2012-05-24 00:00:00.000 NULL

And i want TO UPDATE D_END_DATE, SECOND ROW 2011-11-14 00:00:00.000 WITH 2012-05-24 00:00:00.000 FOR MORE THEN THOUSAND ID'S.

QUERY SHOULD BE DYNAMIC OR IT SHOULD BE ABLE TO FETCH ID VS DATE AUTOMATICALLY.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 23:21:14

sorry your scenario is not clear. do you want to specifically look for only 2011-11-14 values or apply the logic throughout?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shady4u
Starting Member

4 Posts

Posted - 2012-07-09 : 23:27:53

Somehow we have screwed up SCD and we are planning to update in database one by one.

what i have done is i am running this query to find out how many id's are repeating

SELECT columnname FROM tablename GROUP BY columnname HAVING COUNT(columnname) > 2.

to begin with yes, i need to eliminate all strike 2011-11-14 00:00:00.000 where id=31572 and update with 2012-01-10 00:00:00.000.

Only one row will be updated, because as per SCD second row will be expired and third row will be active.

2011-10-15 00:00:00.000 2011-11-14 00:00:00.000 10034
2011-11-14 00:00:00.000 ~~2011-11-14 00:00:00.000~~ 31572
2012-01-10 00:00:00.000 NULL 83786

shady4u
Starting Member

4 Posts

Posted - 2012-07-11 : 05:25:49

any update on this please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:07:16

quote:
Originally posted by shady4u

Somehow we have screwed up SCD and we are planning to update in database one by one.

what i have done is i am running this query to find out how many id's are repeating

SELECT columnname FROM tablename GROUP BY columnname HAVING COUNT(columnname) > 2.

to begin with yes, i need to eliminate all strike 2011-11-14 00:00:00.000 where id=31572 and update with 2012-01-10 00:00:00.000.

Only one row will be updated, because as per SCD second row will be expired and third row will be active.

2011-10-15 00:00:00.000 2011-11-14 00:00:00.000 10034
2011-11-14 00:00:00.000 ~~2011-11-14 00:00:00.000~~ 31572
2012-01-10 00:00:00.000 NULL 83786

isnt it enough to have generic update like

UPDATE table
SET D_END_DATE = '2012-01-10'
WHERE D_START_DATE = D_END_DATE
?

first make it a select and see if it gives you only required rows before you make it an update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources