SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query on Active/Inactive history table


Query on Active/Inactive history table

Author
Message
Cory-358746
Cory-358746
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40798 Visits: 38567
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.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
bcronce
bcronce
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 517
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
Cory-358746
Cory-358746
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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.....
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40798 Visits: 38567
Is this regardless if it also went inactive during that same time (ie does it matter the order of active/inactive)?

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Cory-358746
Cory-358746
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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
Cory-358746
Cory-358746
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40798 Visits: 38567
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.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Cory-358746
Cory-358746
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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!
john.henderson-787186
john.henderson-787186
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 48
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search