May 30, 2011 at 2:11 am
Hi,
I need to show the status change indicator in my sql.Its only for a club.
Create Table Script
drop table [d_org_test]
go
CREATE TABLE [d_org_test](
[org_level_name] [nvarchar](30) NOT NULL,
[org_club_id] [int] NULL,
[org_club_name] [nvarchar](150) NULL,
[org_club_status] [nvarchar](20) NULL,
[org_rec_eff_dt] [date] NOT NULL,
)
Insert into script
insert into [d_org_test] values ('Club',2146,'Chatfield','Active','1988-07-01')
go
insert into [d_org_test] values ('Club',2146,'Chatfield','Active','1954-04-12')
go
insert into [d_org_test] values ('Club',2146,'Chatfield','Active','1995-07-01')
go
insert into [d_org_test] values ('Club',2146,'Chatfield','Terminated','2004-07-15')
go
insert into [d_org_test] values ('Club',2146,'Chatfield','Terminated','2009-07-01')
go
I have to show one column "org_status_change_ind" and the values should be 1 or 0 as per the club status as I mentioned in the below description.
I have to show data in a column [org_status_change_ind] as per below description
set the flag to zero (0) for clubs, then change this flag to one (1) when the status of the current record does not match the status of the record right before it chronologically, or if there is no previous record
as see the simple example for my expected result that I have to show...
[org_level_name] [org_club_id] [org_club_name] [org_club_status] [org_rec_eff_dt] [org_status_change_ind]
Club 2146ChatfieldActive 1988-07-01 1
Club 2146ChatfieldActive 1954-04-120
Club2146ChatfieldActive1995-07-010
Club2146ChatfieldTerminated2004-07-151
Club2146ChatfieldTerminated2009-07-010
Request you to please help me out for this concern.
Regards,
Kiran R
May 30, 2011 at 5:37 am
This Should help... Used Jeff Moden's running total method mentioned in the below mentioned article.
http://qa.sqlservercentral.com/articles/T-SQL/68467/
SELECT *,0 AS [ORG_STATUS_CHANGE_IND] INTO #TMP FROM D_ORG_TEST ORDER BY 5
DECLARE @FLAG INT
DECLARE @CLUB_ID INT
DECLARE @STATUS VARCHAR(15)
UPDATE #TMP SET @FLAG=ORG_STATUS_CHANGE_IND=CASE WHEN ORG_CLUB_ID=@CLUB_ID AND ORG_CLUB_STATUS=@STATUS THEN 0 ELSE 1
END,@CLUB_ID= ORG_CLUB_ID,@STATUS= ORG_CLUB_STATUS
FROM #TMP
SELECT * FROM #TMP
May 30, 2011 at 5:51 am
Thanks Sriram,
your result is absolutely correct but I have to display this result by using sql and I have to use that sql as a source for populating my destination table in SSIS
So please give me the simple SQL script for how to get status change indicator.
May 30, 2011 at 6:11 am
if you have the ORG_STATUS_CHANGE_IND column in your table you can directly use it in an Execute SQL task and use a select statement in the oledb source to load it, if not try creating a staging table with the above mentioned query and use that table to populate the destination.
Note. Please run a couple of tests before using this code in actual production, i didn get a chance to run tests for all scenarios.
for further understanding on the query please refer to the link i mentioned before.
June 1, 2011 at 3:36 am
Hi,
please see my updated sql below,
drop table [d_org_test]
go
CREATE TABLE [d_org_test](
[org_level_name] [nvarchar](30) NOT NULL,
[org_club_id] [int] NULL,
[clb_period] int,
[org_club_name] [nvarchar](150) NULL,
[org_club_status] [nvarchar](20) NULL,
[org_rec_eff_dt] [date] NOT NULL,
[org_status_change_ind] bit null)
insert into [d_org_test] values ('Club',2146,8,'Chatfield','Active','1905-01-01',null)
go
insert into [d_org_test] values ('Club',2146,7,'Chatfield','Active','1988-07-01',null)
go
insert into [d_org_test] values ('Club',2146,6,'Chatfield','Active','1995-07-01',null)
go
insert into [d_org_test] values ('Club',2146,5,'Chatfield','Terminated','2004-07-14',null)
go
insert into [d_org_test] values ('Club',2146,4,'Chatfield','Terminated','2005-01-01',null)
go
insert into [d_org_test] values ('Club',2146,3,'Chatfield','Suspended','2010-07-14',null)
go
insert into [d_org_test] values ('Club',2146,2,'Chatfield','Active','2008-01-01',null)
go
insert into [d_org_test] values ('Club',2146,1,'Chatfield','Active','2009-01-01',null)
go
Update SQL Script
update a
set a.org_status_change_ind=1
from [d_org_test] a
where a.[org_rec_eff_dt] in (select min(b.[org_rec_eff_dt]) from [d_org_test] b where b.org_club_id=a.org_club_id)
go
Update a
set a.org_status_change_ind=1
from [d_org_test] a join
[d_org_test] b
on a.org_club_id = b.org_club_id
and a.org_level_name = 'Club'
and b.org_level_name = 'Club'
and a.clb_period = b.clb_period -1
and a.org_club_status <> b.org_club_status
go
update [d_org_test]
set org_status_change_ind=0
where org_status_change_ind is null
Please see my update sql script below and please request you to check my above upadted sql's and if possible provide me modified sql for getting result as like below.
I need to set the earliest record by date should have the status change ind to 1 and other should have status change ind to 0
for sample result I have to show as like below from my update statement
Club 21468ChatfieldActive 1905-01-01 1
Club 21467ChatfieldActive 1988-07-01 0
Club 21466ChatfieldActive 1995-07-01 0
Club 21465ChatfieldTerminated 2004-07-14 1
Club 21464ChatfieldTerminated 2005-01-01 0
Club 21462ChatfieldActive 2008-01-01 1
Club 21461ChatfieldActive 2009-01-01 0
Club 21463ChatfieldSuspended 2010-07-14 1
Please do the needful.
Regards,
Kiran
June 8, 2011 at 6:03 am
Sorry for the delay in reply... u can use the query i posted earlier to achieve the same result... made some modifications to it...
DECLARE @FLAG INT
DECLARE @CLUB_ID INT
DECLARE @STATUS VARCHAR(15)
UPDATE D_ORG_TEST SET @FLAG=ORG_STATUS_CHANGE_IND=CASE WHEN ORG_CLUB_ID=@CLUB_ID AND ORG_CLUB_STATUS=@STATUS THEN 0 ELSE 1
END,@CLUB_ID= ORG_CLUB_ID,@STATUS= ORG_CLUB_STATUS
FROM D_ORG_TEST
SELECT * FROM D_ORG_TEST
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply