June 30, 2008 at 2:10 pm
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.
June 30, 2008 at 2:19 pm
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.
😎
June 30, 2008 at 2:27 pm
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
June 30, 2008 at 2:36 pm
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.....
June 30, 2008 at 2:42 pm
Is this regardless if it also went inactive during that same time (ie does it matter the order of active/inactive)?
😎
June 30, 2008 at 2:45 pm
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
June 30, 2008 at 2:46 pm
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.
June 30, 2008 at 2:56 pm
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.
😎
June 30, 2008 at 3:05 pm
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!
June 30, 2008 at 3:28 pm
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
July 1, 2008 at 7:34 pm
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