Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query on Active/Inactive history table Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 11, 2011 12:13 PM
Points: 5, Visits: 34
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.
Post #526215
Posted Monday, June 30, 2008 2:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 22,525, Visits: 30,290
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.




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)
Post #526220
Posted Monday, June 30, 2008 2:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 03, 2012 7:36 AM
Points: 127, 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
Post #526225
Posted Monday, June 30, 2008 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 11, 2011 12:13 PM
Points: 5, Visits: 34
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.....
Post #526230
Posted Monday, June 30, 2008 2:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 22,525, Visits: 30,290
Is this regardless if it also went inactive during that same time (ie does it matter the order of active/inactive)?




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)
Post #526237
Posted Monday, June 30, 2008 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 11, 2011 12:13 PM
Points: 5, Visits: 34
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
Post #526238
Posted Monday, June 30, 2008 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 11, 2011 12:13 PM
Points: 5, Visits: 34
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.
Post #526240
Posted Monday, June 30, 2008 2:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:44 PM
Points: 22,525, Visits: 30,290
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.




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)
Post #526246
Posted Monday, June 30, 2008 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 11, 2011 12:13 PM
Points: 5, Visits: 34
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!
Post #526248
Posted Monday, June 30, 2008 3:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 24, 2011 12:19 PM
Points: 89, 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

Post #526256
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse