InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:23:04 GMT20http://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspx[quote][b]sql1411 (3/8/2013)[/b][hr]Never mind. I just had to add "<=" sign. :) Thanks again :)[/quote]Glad you figured it out. This brings up a comment. If you store time information in your table you may want to modify your code slightly. Otherwise you will not the results you want if you pass in.@SearchDate = '2012-05-20 11:11:00'It really somewhat depends on what you want to do here. If you want to capture data from anytime on that day just modify the where clause a little to[code]where ActivityDate < dateadd(day, 1, @SearchDate)[/code]Fri, 08 Mar 2013 15:04:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxNever mind. I just had to add "<=" sign. :) Thanks again :)Fri, 08 Mar 2013 14:51:56 GMTsql1411http://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxHi Sean,There seems to be a small problem I guess. The query seems to work for a April 04,2012. However, if I pick any ActivityDate as @SearchDate then it gives the previous status and not the status on that given date. For instance, if I set @SearchDate = '2012-05-20 00:00:00' , then for CustomerID 101 I get the Status as 'INACTIVE'.Is there a way we can fix this??ThanksFri, 08 Mar 2013 14:47:55 GMTsql1411http://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxThanks Sean .....This should do it :)Thanks againFri, 08 Mar 2013 13:40:25 GMTsql1411http://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspx@SQL_FS...... You got it right. Thanks for catching my mistake. Yes, for 101 the LatestStatus should be INACTIVE on April04,2012, @Sean......thanks for your query. Your query can give me the LatestStatus. Sorry, if I confused you from my example as I pointed out by "SQL_FS". I want to know the LatestStatus for a member on a given date and I want to a parameter in this date so that the user can select any date and not just the latest date.Let me know if I need to clarify further.Thanks.Fri, 08 Mar 2013 13:37:55 GMTsql1411http://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxI guess I missed the bit about the date part. Just add a where clause to your query and you should be good to go.[code]declare @SearchDate datetime = '2012-04-01 00:00:00'select * from( select *, ROW_NUMBER() over (partition by ConsumerID order by ActivityDate desc) as RowNum from ConsumerActivity where ActivityDate < @SearchDate) x where x.RowNum = 1[/code]Fri, 08 Mar 2013 13:36:20 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxI'm confused by your expected results.You said you want to get the "latest status as of April 1, 2012." And state that you would expect ConsumerID 101 to be returned as "ACTIVE." Yet the dates in your sample data indicate that ConsumerID 101 is first inserted on January 10th, 2012 with an "INACTIVE" status, and the new status of "ACTIVE" is not inserted until May 20, 2012 (after your "as-of" report date). So I would have thought you would have expected ConsumerID 101 to be returned as "INACTIVE" based on your requirements.Can you clarify on this point?Fri, 08 Mar 2013 13:18:24 GMTSQL_FShttp://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxExcellent job posting ddl, sample data and an explanation. This should work based on your sample.[code]select * from( select *, ROW_NUMBER() over (partition by ConsumerID order by ActivityDate desc) as RowNum from ConsumerActivity) x where x.RowNum = 1[/code]Fri, 08 Mar 2013 12:50:10 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428730-392-1.aspxHi there,I've a ConsumerActivity table that records all the activities. Now, I need to find out the latest status for each Consumers on a given day. For example, I need to write a query that gives me ConsumerID and latest Status on '2012-04-01 00:00:00'.In this table, there is no activity on this date and as such I won't get any result. However, I want to get the result as: ConsumerID Status 101 ACTIVE 102 PREMIUMBelow is my simple scenario and query:USE [Sample]GO-- Create TableCREATE TABLE [dbo].[ConsumerActivity]( [ConsumerID] [varchar](10) NOT NULL, [ActivityDate] [datetime] NULL, [Status] [varchar](10) NULL) ON [PRIMARY]GO-- Insert Some Data into this tableGOINSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, Status)SELECT 101, '2012-01-10 00:00:00', 'INACTIVE'UNION ALLSELECT 101, '2012-05-20 00:00:00', 'ACTIVE'UNION ALLSELECT 102, '2012-02-10 00:00:00', 'BASE'UNION ALLSELECT 102, '2012-03-15 00:00:00', 'PREMIUM'GOThanks.Fri, 08 Mar 2013 12:22:31 GMTsql1411