February 22, 2012 at 7:49 am
Hi,
My requirement is to update the column org_status_change_dt as like below.
Please look only two date columns org_rec_eff_dt and org_status_change_dt.
org_id org_level_name org_club_id org_club_status org_rec_eff_dt org_status_change_dt
7271Club 7271 Active 1988-07-01 1988-07-01
7271Club 7271 Active 1995-07-01 1988-07-01
7271Club 7271 Terminated 1996-01-13 1996-01-13
7271Club 7271 Active 1997-03-13 1997-03-13
7271Club 7271 Active 1999-06-30 1997-03-13
7271Club 7271 Terminated 1999-10-01 1999-10-01
7271Club 7271 Active 2009-07-01 2009-07-01
I want to update column org_status_change_dt as like above.
below is my SQL create table and insert into table
drop table tmp_d_org_test
go
CREATE TABLE [dbo].[tmp_d_org_test](
[org_seq_nbr] [smallint] NOT NULL,
[org_id] [int] NOT NULL,
[org_level_name] [nvarchar](30) NOT NULL,
[org_club_id] [int] NULL,
[org_club_status] [nvarchar](20) NULL,
[org_rec_eff_dt] [date] NOT NULL,
[org_rec_end_dt] [date] NOT NULL,
[org_club_status_change_dt] [date] NULL)
insert into tmp_d_org_test values (1,7271,'Club',7271,'Active','1988-07-01','1995-06-30',null)
insert into tmp_d_org_test values (2,7271,'Club',7271,'Active','1995-07-01','1996-01-12',null)
insert into tmp_d_org_test values (3,7271,'Club',7271,'Terminated','1996-01-13','1997-03-12',null)
insert into tmp_d_org_test values (4,7271,'Club',7271,'Active','1997-03-13','1999-06-29',null)
insert into tmp_d_org_test values (5,7271,'Club',7271,'Active','1999-06-30','1999-09-30',null)
insert into tmp_d_org_test values (6,7271,'Club',7271,'Terminated','1999-10-01','2009-06-30',null)
insert into tmp_d_org_test values (7,7271,'Club',7271,'Active','2009-07-01','2079-06-06',null)
Please provide me how to write update SQL for the table tmp_d_org_test for updating column org_club_status_change_dt.
Best Regards,
Kiran
February 22, 2012 at 8:07 am
Kiran,
Thank you for providing DDL code, INSERT statements, and a expected result, but I don't know your data well enough to understand what you mean by "modify".
Please be more clear with your expected results / sample data and tell us what you're updating that date with.
February 22, 2012 at 8:30 am
Hi,
The first two Active records having two dofferent dates i.e.
org_club_status org_rec_eff_dt org_status_change_dt
Active 1988-07-01 1988-07-01
Active 1995-07-01 1988-07-01
but in the column org_status_change_dt, I have to show the first effective date i.e. 1988-07-01 in both the Active rows.
but when the satus will be change the date would be same as effective date,
see the example below,
org_club_status org_rec_eff_dt org_status_change_dt
Terminated 1996-01-13 1996-01-13
Active 1997-03-13 1997-03-13
Active 1999-06-30 1997-03-13
Terminated 1999-10-01 1999-10-01
Active 2009-07-01 2009-07-01
February 22, 2012 at 9:31 am
kiran.rajenimbalkar (2/22/2012)
Hi,The first two Active records having two dofferent dates i.e.
org_club_status org_rec_eff_dt org_status_change_dt
Active 1988-07-01 1988-07-01
Active 1995-07-01 1988-07-01
but in the column org_status_change_dt, I have to show the first effective date i.e. 1988-07-01 in both the Active rows.
but when the satus will be change the date would be same as effective date,
see the example below,
org_club_status org_rec_eff_dt org_status_change_dt
Terminated 1996-01-13 1996-01-13
Active 1997-03-13 1997-03-13
Active 1999-06-30 1997-03-13
Terminated 1999-10-01 1999-10-01
Active 2009-07-01 2009-07-01
Unfortunately, that doesn't clarify things much for me. I can't see the difference between your first sample and your second.
Are you saying you want
org_club_status org_rec_eff_dt org_status_change_dt
Active 1988-07-01 1988-07-01
Active 1995-07-01 1988-07-01
to be
org_club_status org_rec_eff_dt org_status_change_dt
Active 1988-07-01 1988-07-01
Active 1988-07-01 1988-07-01
?
There is no clear differentiation between your club records. All records have the same ID info, except for your org_seq_nbr. So how does one know to update line 2 instead of line 5 in the above sample?
Any code I would provide you would end up updating all Active records to the minimum date. I'm sure that's not what you want.
February 22, 2012 at 10:14 am
Im also a little confused. Does the table have a primary key or unique column that we cannot see?
Are you trying to iteratively move down the "list" and if there are 2 consecutive active rows setting the date to the minimum value?
Still not clear enough to give a definitive answer.
February 23, 2012 at 2:29 am
Hi,
I write the code to update the table, but I am facing problem i.e.
when I am going to execute the below SQL it taking long time while updating the table inside the cursor block.
So please suggest me how to improve the performance. I have just provide one ID as an example but in my table there are 22 million rows and I have to update these records by using cursor but it’s taking long time for updating.
So request you just Provide me the solution or what should I do in the cursor for taking less time while updating.
Create Table and Insert Table Script
--------------------------------------------
drop table tmp_d_org_test
go
CREATE TABLE [dbo].[tmp_d_org_test](
[org_seq_nbr] [smallint] NOT NULL,
[org_id] [int] NOT NULL,
[org_level_name] [nvarchar](30) NOT NULL,
[org_club_id] [int] NULL,
[org_club_status] [nvarchar](20) NULL,
[org_rec_eff_dt] [date] NOT NULL,
[org_rec_end_dt] [date] NOT NULL,
[org_club_status_change_dt] [date] NULL)
insert into tmp_d_org_test values (1,7271,'Club',7271,'Active','1988-07-01','1995-06-30',null)
insert into tmp_d_org_test values (2,7271,'Club',7271,'Active','1995-07-01','1996-01-12',null)
insert into tmp_d_org_test values (3,7271,'Club',7271,'Terminated','1996-01-13','1997-03-12',null)
insert into tmp_d_org_test values (4,7271,'Club',7271,'Active','1997-03-13','1999-06-29',null)
insert into tmp_d_org_test values (5,7271,'Club',7271,'Active','1999-06-30','1999-09-30',null)
insert into tmp_d_org_test values (6,7271,'Club',7271,'Terminated','1999-10-01','2009-06-30',null)
insert into tmp_d_org_test values (7,7271,'Club',7271,'Active','2009-07-01','2079-06-06',null)
----------------------------------------------------------------------------------------------
UPDATE [tmp_d_org_test]
SET org_club_status_change_dt = NULL;
SELECT *
INTO #[tmp_d_org_test]
FROM [tmp_d_org_test]
WHERE org_id = 7271
ORDER BY org_id,org_seq_nbr;
alter table #[tmp_d_org_test] add org_club_status_seq_nbr INT;
update #[tmp_d_org_test]
set org_club_status_seq_nbr = 1
where org_seq_nbr = 1;
DECLARE @org_id INT
DECLARE @org_seq_nbr INT
DECLARE @org_club_status NVARCHAR(20)
DECLARE @org_id_old INT
DECLARE @org_seq_nbr_old INT
DECLARE @org_club_status_old NVARCHAR(20)
DECLARE db_cursor CURSOR FOR
select org_id,org_seq_nbr,org_club_status from #[tmp_d_org_test]
OPEN db_cursor
FETCH next FROM db_cursor INTO @org_id,@org_seq_nbr,@org_club_status;
WHILE @@fetch_status = 0
BEGIN
SET @org_id_old = @org_id
SET @org_seq_nbr_old = @org_seq_nbr
SET @org_club_status_old = @org_club_status
FETCH next FROM db_cursor INTO @org_id,@org_seq_nbr,@org_club_status
IF @org_club_status = @org_club_status_old
update #[tmp_d_org_test]
set org_club_status_seq_nbr = (select MAX(org_club_status_seq_nbr) from #[tmp_d_org_test] where org_id=@org_id)
where org_id = @org_id
and org_seq_nbr = @org_seq_nbr
and org_club_status_seq_nbr is null
ELSE
update #[tmp_d_org_test]
set org_club_status_seq_nbr = (select MAX(org_club_status_seq_nbr) + 1 from #[tmp_d_org_test] where org_id=@org_id)
where org_id = @org_id
and org_seq_nbr = @org_seq_nbr
and org_club_status_seq_nbr is null
END
CLOSE db_cursor
DEALLOCATE db_cursor
Regards,
Kiran
February 23, 2012 at 3:12 am
Hi,
I write the code to update the table, but I am facing problem i.e.
when I am going to execute the below SQL it taking long time while updating the table inside the cursor block.
So please suggest me how to improve the performance. I have just provide one ID as an example but in my table there are 22 million rows and I have to update these records by using cursor but it’s taking long time for updating.
So request you just Provide me the solution or what should I do in the cursor for taking less time while updating.
Create Table and Insert Table Script
--------------------------------------------
drop table tmp_d_org_test
go
CREATE TABLE [dbo].[tmp_d_org_test](
[org_seq_nbr] [smallint] NOT NULL,
[org_id] [int] NOT NULL,
[org_level_name] [nvarchar](30) NOT NULL,
[org_club_id] [int] NULL,
[org_club_status] [nvarchar](20) NULL,
[org_rec_eff_dt] [date] NOT NULL,
[org_rec_end_dt] [date] NOT NULL,
[org_club_status_change_dt] [date] NULL)
insert into tmp_d_org_test values (1,7271,'Club',7271,'Active','1988-07-01','1995-06-30',null)
insert into tmp_d_org_test values (2,7271,'Club',7271,'Active','1995-07-01','1996-01-12',null)
insert into tmp_d_org_test values (3,7271,'Club',7271,'Terminated','1996-01-13','1997-03-12',null)
insert into tmp_d_org_test values (4,7271,'Club',7271,'Active','1997-03-13','1999-06-29',null)
insert into tmp_d_org_test values (5,7271,'Club',7271,'Active','1999-06-30','1999-09-30',null)
insert into tmp_d_org_test values (6,7271,'Club',7271,'Terminated','1999-10-01','2009-06-30',null)
insert into tmp_d_org_test values (7,7271,'Club',7271,'Active','2009-07-01','2079-06-06',null)
----------------------------------------------------------------------------------------------
UPDATE [tmp_d_org_test]
SET org_club_status_change_dt = NULL;
SELECT *
INTO #[tmp_d_org_test]
FROM [tmp_d_org_test]
WHERE org_id = 7271
ORDER BY org_id,org_seq_nbr;
alter table #[tmp_d_org_test] add org_club_status_seq_nbr INT;
update #[tmp_d_org_test]
set org_club_status_seq_nbr = 1
where org_seq_nbr = 1;
DECLARE @org_id INT
DECLARE @org_seq_nbr INT
DECLARE @org_club_status NVARCHAR(20)
DECLARE @org_id_old INT
DECLARE @org_seq_nbr_old INT
DECLARE @org_club_status_old NVARCHAR(20)
DECLARE db_cursor CURSOR FOR
select org_id,org_seq_nbr,org_club_status from #[tmp_d_org_test]
OPEN db_cursor
FETCH next FROM db_cursor INTO @org_id,@org_seq_nbr,@org_club_status;
WHILE @@fetch_status = 0
BEGIN
SET @org_id_old = @org_id
SET @org_seq_nbr_old = @org_seq_nbr
SET @org_club_status_old = @org_club_status
FETCH next FROM db_cursor INTO @org_id,@org_seq_nbr,@org_club_status
IF @org_club_status = @org_club_status_old
update #[tmp_d_org_test]
set org_club_status_seq_nbr = (select MAX(org_club_status_seq_nbr) from #[tmp_d_org_test] where org_id=@org_id)
where org_id = @org_id
and org_seq_nbr = @org_seq_nbr
and org_club_status_seq_nbr is null
ELSE
update #[tmp_d_org_test]
set org_club_status_seq_nbr = (select MAX(org_club_status_seq_nbr) + 1 from #[tmp_d_org_test] where org_id=@org_id)
where org_id = @org_id
and org_seq_nbr = @org_seq_nbr
and org_club_status_seq_nbr is null
END
CLOSE db_cursor
DEALLOCATE db_cursor
Regards,
Kiran
February 23, 2012 at 3:25 am
kiran.rajenimbalkar (2/23/2012)
..So request you just Provide me the solution or what should I do in the cursor for taking less time while updating.
Is is not possible using cursor.
If you provide better explanation of expected results, then we may help you to write set-based version of your cursor which will fly in compare to your cursor one.
February 23, 2012 at 5:07 am
Hi,
I have provided the sample in my first reply.
February 23, 2012 at 7:12 am
Your requirements are not very clearly defined in any of your post, that is why you question is not attracting much attention on the forum. Also, the code you've included contains syntax errors and it is not runable!
If I understand you right (and it's probably because I'm not native English speaker myself), the way you need to go is to use "running total" concept (you can find great article about it on this site from Jeff Moden).
The first pre-req will be to have clustered index which will guarantee orderly update.
Based on your setup this index should be defined as:
create unique clustered index ix_#tmp_d_org_test on #tmp_d_org_test(org_id, org_seq_nbr)
The next one will update your data in one set based operation which will outperform any cursor:
declare @seqnbr int,
@eff_dt datetime,
@status varchar(50),
@orgid int
update #tmp_d_org_test
set @eff_dt = case when @status is null then org_rec_eff_dt
when @status != org_club_status
or @orgid != org_id then org_rec_eff_dt
else @eff_dt
end
,@seqnbr = case when @status is null then org_seq_nbr
when @status != org_club_status
or @orgid != org_id then org_seq_nbr
else @seqnbr
end
,org_club_status_change_dt = @eff_dt
,org_club_status_seq_nbr = @seqnbr
,@status = org_club_status
,@orgid = org_id
from #tmp_d_org_test
option (maxdop 1)
Please note: option (maxdop 1) is important!
February 23, 2012 at 10:43 am
kiran.rajenimbalkar (2/23/2012)
I have provided the sample in my first reply.
Unfortunately, you haven't explained your problem well enough for us to understand what you want to do. I think the language barrier may be causing us a bit of an issue. You know what you're saying, but you're not saying it using all the words we need in order to understand you.
Did you see my reply asking about the results? This one here: http://qa.sqlservercentral.com/Forums/FindPost1256101.aspx
Could you please answer that question?
If you don't know what I'm asking, please say so. Then I can try to find a better way to phrase it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply