Need to show status change indicator

  • 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

  • This Should help... Used Jeff Moden's running total method mentioned in the below mentioned article.

    http://www.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

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

  • 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.

  • 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.

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

  • 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

  • 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

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply