InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 21:06:27 GMT20http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxAssumption: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:[code]SELECT T1.ACCOUNT_ID, T1.ACCOUNT_NAME, .... , (CASE WHEN T2.ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) as ACCOUNT_STATUSFROM Account_Master AS T1LEFT 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[/code]HTHTue, 01 Jul 2008 19:34:10 GMTKaushalhttp://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxI 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 accountIDget its active statusif there is no record (NULL) then it cant have been activated so set activeflag = 0create function dbo.isaccountActive(@accountID int, @comparisonTime datetime)returns intasbegindeclare @activeFlag intselect top 1 @activeFlag = activefrom account_DeactivationReactivationHistorywhere account_ID = @accountID and statusDate <= @comparisonTimeorder by statusDate descset @activeFlag = isnull(@activeFlag,0)return @activeFlagendMon, 30 Jun 2008 15:28:31 GMTjohn.henderson-787186http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxThe 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!Mon, 30 Jun 2008 15:05:29 GMTCory-358746http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxDoes this help you with getting you where you need to go?[code]create table #ActiveStatus ( AcctId int, ActiveStatus bit not null, -- 1 = Active 0 = Inactive ActivityDate datetime);goinsert into #ActiveStatus (AcctId, ActiveStatus, ActivityDate)select 1, 1, '2007-11-15' union allselect 1, 0, '2007-11-25' union allselect 1, 1, '2007-12-15' union allselect 2, 1, '2007-10-15' union allselect 2, 0, '2007-11-15' union allselect 3, 1, '2007-08-15' union allselect 3, 0, '2007-09-15' union allselect 3, 1, '2007-09-16' union allselect 3, 0, '2007-09-17' union allselect 3, 1, '2007-11-15' union allselect 4, 1, '2007-11-15';goselect * from #ActiveStatus;godeclare @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 #ActiveStatuswhere AcctId = @AcctId and ActivityDate >= @StartDate and ActivityDate < @EndDategroup by AcctId)select a.ActiveStatusfrom #ActiveStatus a inner join MaxActivity ma on (a.AcctId = ma.AcctId and a.ActivityDate = ma.ActivityDate)godrop table #ActiveStatus;go[/code]It isn't a function, but it could easily be turned into one.:cool:Mon, 30 Jun 2008 14:56:54 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxIt 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.Mon, 30 Jun 2008 14:46:58 GMTCory-358746http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxThanks for your quick response.... Here is the code to create the table and sample dataCREATE 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.ThanksMon, 30 Jun 2008 14:45:00 GMTCory-358746http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxIs this regardless if it also went inactive during that same time (ie does it matter the order of active/inactive)?:cool:Mon, 30 Jun 2008 14:42:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxNot 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, datetime1234, 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..... Mon, 30 Jun 2008 14:36:29 GMTCory-358746http://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxsomething like this?table t(accountid, active, [datetime])select * from twhere accountid = 1010100and 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 accountMon, 30 Jun 2008 14:27:51 GMTbcroncehttp://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxTo 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:Mon, 30 Jun 2008 14:18:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic526215-149-1.aspxI 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.Mon, 30 Jun 2008 14:10:12 GMTCory-358746