Query on Active/Inactive history table

  • I have a table called account_DeactivateActivateHistory. The objective of this table is to track when an account is activated or inactivated. I could have multiple records for the same account for an account can be activated, deactivated, activated again, and then deactivated again, etc. This table keeps track of the date the Inactive or Active Status occurred.

    My issue is that I need to be able to run historical reports that tell me if an account was active or inactive at any time during a particular date range. How would I write a query that would tell me this?

    Any help would be greatly appreciated.

  • To make sure anyone who helps you is on the same page a little more info is required. If you could provide the DDL (create statement for the table(s)), sample data (in the form of insert statements to the table(s) that can be cut, pasted, and run), and the expected output from the query based on the sample data provided.

    Also, any code that you have already tried to accomplish the same task.

    😎

  • something like this?

    table t(accountid, active, [datetime])

    select *

    from t

    where accountid = 1010100

    and datetime between startdate and dateadd(ms, 86399997, enddate)

    order by [datetime]

    this would return all active/inactive events during a date range for a given account

  • Not exactly... what I need is to be able to pass a date range and account id to a function and it return to me if that account id was active during that date range.

    My data could look similar to the following:

    account_id, active, datetime

    1234, 1, #2/3/2007 14:00#

    1234, 0, #6/15/2007 10:00#

    1234, 1, #11/12/2007 11:00#

    1234, 0, #2/5/2008 17:00#

    I would want to check that data to see if a specific account_id was active during a given date range. For Example, I would want to query if 1234 was active anytime between 7/1/2007 and 12/30/2007.

    I hope this helps.....

  • Is this regardless if it also went inactive during that same time (ie does it matter the order of active/inactive)?

    😎

  • Thanks for your quick response.... Here is the code to create the table and sample data

    CREATE TABLE [dbo].[account_DeactivationReactivationHistory](

    [ID] [bigint] IDENTITY(1,1) NOT NULL,

    [account_id] [int] NOT NULL,

    [Active] [bit] NOT NULL,

    [status_date] [datetime] NOT NULL,

    CONSTRAINT [PK_account_DeactivationReactivationHistory] PRIMARY KEY CLUSTERED ([ID] ASC)

    ) ON [PRIMARY]

    INSERT INTO account_DeactivationReactivationHistory

    (account_id, active, status_date)

    VALUES (1234, 1, '2/3/2007 14:00')

    INSERT INTO account_DeactivationReactivationHistory

    (account_id, active, status_date)

    VALUES (1234, 0, '6/15/2007 10:00')

    INSERT INTO account_DeactivationReactivationHistory

    (account_id, active, status_date)

    VALUES (1234, 1, '11/12/2007 11:00')

    INSERT INTO account_DeactivationReactivationHistory

    (account_id, active, status_date)

    VALUES (1234, 0, '2/5/2008 17:00')

    I'd like to be able to create a function that returns true or false based on if a particular account_id is active during a particular date range. For example in regards to the sample data, I'd like to check to see if account_id 1234 was active during the date range of 7/1/2007 and 12/30/2007.

    Also, take into consideration that some of our users will make a mistake and activate someone and immediately inactivate them again on the same day. I would not want to count that particular as active on that day.

    Thanks

  • It does matter if they went active and inactive on the same day. If the order on that day was active at 10:00am and then inactive at 12:00pm, then I would want to disregard the active at 10:00am and consider that account inactive on that day. We have users making mistakes all the time by making someone inactive or active when they didn't mean to and they immediately change the status back.

  • Does this help you with getting you where you need to go?

    create table #ActiveStatus (

    AcctId int,

    ActiveStatus bit not null, -- 1 = Active 0 = Inactive

    ActivityDate datetime

    );

    go

    insert into #ActiveStatus (AcctId, ActiveStatus, ActivityDate)

    select 1, 1, '2007-11-15' union all

    select 1, 0, '2007-11-25' union all

    select 1, 1, '2007-12-15' union all

    select 2, 1, '2007-10-15' union all

    select 2, 0, '2007-11-15' union all

    select 3, 1, '2007-08-15' union all

    select 3, 0, '2007-09-15' union all

    select 3, 1, '2007-09-16' union all

    select 3, 0, '2007-09-17' union all

    select 3, 1, '2007-11-15' union all

    select 4, 1, '2007-11-15';

    go

    select * from #ActiveStatus;

    go

    declare @StartDate datetime,

    @EndDate datetime,

    @AcctId int;

    set @StartDate = '2007-10-01';

    set @EndDate = '2007-12-31';

    set @AcctId = 1;

    with MaxActivity (

    AcctId,

    ActivityDate

    ) as (

    select

    AcctId,

    max(ActivityDate)

    from

    #ActiveStatus

    where

    AcctId = @AcctId

    and ActivityDate >= @StartDate

    and ActivityDate < @EndDate

    group by

    AcctId

    )

    select

    a.ActiveStatus

    from

    #ActiveStatus a

    inner join MaxActivity ma

    on (a.AcctId = ma.AcctId

    and a.ActivityDate = ma.ActivityDate)

    go

    drop table #ActiveStatus;

    go

    It isn't a function, but it could easily be turned into one.

    😎

  • The with statement is something I've never used before. What a great way to create a quick subset to use in other queries. I think this does it for me.

    Thanks a ton for your help!

  • I am just wondering if simplest is the best option here...

    Get the top record (sorted in date descending order before the query time) for the accountID

    get its active status

    if there is no record (NULL) then it cant have been activated so set activeflag = 0

    create function dbo.isaccountActive(@accountID int, @comparisonTime datetime)

    returns int

    as

    begin

    declare @activeFlag int

    select top 1

    @activeFlag = active

    from

    account_DeactivationReactivationHistory

    where

    account_ID = @accountID and

    statusDate <= @comparisonTime

    order by statusDate desc

    set @activeFlag = isnull(@activeFlag,0)

    return @activeFlag

    end

  • Assumption:

    You have an Account_Master table (and may be some other table(s) you need for the report) as follows

    account_id int,

    account_name varchar(100),

    ..

    etc..

    And you want to run a query for the report where you pass a date value as an input parameter, I would do something like this:

    SELECT T1.ACCOUNT_ID, T1.ACCOUNT_NAME, ....

    , (CASE WHEN T2.ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) as ACCOUNT_STATUS

    FROM Account_Master AS T1

    LEFT OUTER JOIN

    ( SELECT ACCOUNT_ID, ACTIVE FROM

    (SELECT account_id, active, status_date

    , ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY status_date DESC) AS ROWNUM

    FROM account_DeactivationReactivationHistory ) AS T2

    WHERE ROWNUM = 1

    ) AS T2

    ON T1.ACCOUNT_ID = T2.ACCOUNT_ID

    HTH

Viewing 11 posts - 1 through 10 (of 10 total)

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