InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 08:54:51 GMT20http://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspx[code="sql"]SELECT R1.Report_ID, R1.[ReportHistoryID], R1.modified_by, R1.last_modified, R2.[ReportHistoryID], R2.modified_by, R2.last_modified FROM [dbo].[Reporthistory] R1 INNER JOIN (SELECT report_ID, MAX(last_modified) LastModified From [dbo].[Reporthistory] WHERE Report_status_id = 2 GROUP BY report_ID ) RLM ON RLM.report_ID = R1.report_ID INNER JOIN [dbo].[Reporthistory] R2 ON R2.report_ID = RLM.report_ID AND R2.last_modified = RLM.LastModifiedR1.[Report_status_id] = 1 [/code]Thu, 27 Sep 2012 22:36:05 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspxThis will do it:[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ReportHistoryID] [int] IDENTITY(1,1) NOT NULL ,[Report_id] [int] NOT NULL ,[Report_status_id] [int] NOT NULL ,[modified_by] [int] NOT NULL ,[last_modified] [datetime] NOT NULL DEFAULT (GETDATE()) ,PRIMARY KEY (ReportHistoryID) ,UNIQUE (ReportHistoryID) )INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Jul 31 2008 1:31AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 3 2003 10:27AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 15 2003 7:09AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 9 2003 5:41PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 13 2003 11:37AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,1,16836,'Dec 11 2003 10:52AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,2,16836,'Dec 11 2003 5:06PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 19 2003 8:49PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 12:23PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 12 2003 1:40PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 2:32PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 1:51PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,2,17221,'Dec 12 2003 2:22PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 2:30PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 12 2003 3:53PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 12 2003 4:24PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 16 2003 2:27PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 16 2003 3:38PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 18 2003 2:10PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 19 2003 12:23PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 19 2003 3:17PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (6,1,18019,'Dec 19 2003 3:05PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (7,1,17111,'Dec 19 2003 4:58PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (8,1,18010,'Dec 19 2003 5:34PM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,1,17773,'Dec 22 2003 10:35AM')INSERT INTO #TempTable (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,2,17773,'Dec 23 2003 5:05AM')SELECT Sub1.ReportHistoryID ,t1.Report_id ,t1.Report_status_id ,t2.Report_status_id AS Prev_status_id ,t1.modified_by ,t1.last_modifiedFROM ( SELECT MAX(rh1.ReportHistoryID) AS ReportHistoryID FROM #TempTable AS rh1 GROUP BY rh1.modified_by ) AS Sub1INNER JOIN #TempTable AS t1 ON Sub1.ReportHistoryID = t1.ReportHistoryIDINNER JOIN #TempTable AS t2 ON t1.ReportHistoryID = t2.ReportHistoryID + 1WHERE t1.Report_status_id = 1 AND t2.Report_status_id = 2[/code]Output:ReportHistoryID Report_id Report_status_id Prev_status_id modified_by last_modified5 1 1 2 15387 2003-12-13 11:37:00.00014 4 1 2 17221 2003-12-12 14:30:00.00021 5 1 2 17257 2003-12-19 15:17:00.000Wed, 26 Sep 2012 19:36:28 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspxtoo mach scans[code="sql"]select * from reporthistory rhwhere not exists (select * from reporthistory rh1 where rh1.Report_id = rh.Report_id and rh1.ReportHistoryID > rh.ReportHistoryID) and (select top 1 rh1.Report_status_id from reporthistory rh1 where rh1.Report_id = rh.Report_id and rh1.ReportHistoryID < rh.ReportHistoryID Order by rh1.ReportHistoryID desc) = 2 and rh.Report_status_id = 1[/code]Tue, 25 Sep 2012 01:14:04 GMThandkothttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspxThanks for the responsehere is a sample of the desired output.for report_id 15 1 1 15387 2003-12-13 11:37:00.000for report_id 2nothing returned because a 1 does not follow a 2For report_id 3nothing returned because a 1 does not follow a 2for report_id 414 4 1 17221 2003-12-12 14:30:00.000for report_id 521 5 1 17257 2003-12-19 15:17:00.000for report_id 6nothing returned because there is no 2, the report has not been submitted.for report_id 7nothing returned because there is no 2, the report has not been submitted.for report_id 8nothing returned because there is no 2, the report has not been submitted.for report_id 9nothing returned because a 1 does not follow a 2Fri, 21 Sep 2012 15:51:25 GMTGFhttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspxBased on the sample data you provided in your initial post, what is the expected results?Fri, 21 Sep 2012 13:54:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspx[quote][b]GF (9/21/2012)[/b][hr]What I am wanting to achieve is a list of any reports that have a current status of 1 (submitted), and that report has been previously submitted.In other words, I need a list of reports where the status =1 and the previous status =2 for that same reported.[/quote]Might not be exactly what you need but hopefully gives you some ideas.[code="sql"]DECLARE @tmp TABLE (RowId INT IDENTITY(1,1), Report_id INT, Report_status_id INT, last_modified DATETIME)INSERT INTO @tmp (Report_id, Report_status_id, last_modified)SELECT Report_id, Report_status_id, last_modifiedFROM reporthistoryORDER BY Report_id, last_modified DESCSELECT t1.Report_id, t1.Report_status_id, t1.last_modified , pv_Report_id = t2.Report_id, pv_Report_status_id = t2.Report_status_id, pv_last_modified = t2.last_modifiedFROM @tmp t1 INNER JOIN @tmp t2 ON t1.Report_id = t2.Report_id AND t1.RowId + 1 = t2.RowIdWHERE t1.Report_status_id = 1AND t2.Report_status_id = 2[/code]Fri, 21 Sep 2012 12:32:50 GMTRP_DBAhttp://www.sqlservercentral.com/Forums/Topic1362891-8-1.aspxHello,We are still on SQL 2000 and I need help with a query.We have a table named Reporthistory that records when a customer creates a report and subsequent actions on that report.A customer creates a report and a report_id is created also the Report_status_id is set to 1.Once the customer has completed any modifications to the report they can submit that report, which creates a new entry for that report is in the reporthistory table and sets the Report_status_id to 2.The customer can come back and “Un-submit” the report, which creates a new entry in the reporthistory for that report_id and sets the Report_status_id to 1What I am wanting to achieve is a list of any reports that have a current status of 1 (submitted), and that report has been previously submitted.In other words, I need a list of reports where the status =1 and the previous status =2 for that same reported.I hope I have explained the problem and required solution. If I need to provide more clarification please let me know.Thanks in advance.CREATE TABLE [dbo].[Reporthistory] ( [ReportHistoryID] [int] IDENTITY (1, 1) NOT NULL , [Report_id] [int] NOT NULL , [Report_status_id] [int] NOT NULL , [modified_by] [int] NOT NULL , [last_modified] [datetime] NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Reporthistory] WITH NOCHECK ADD CONSTRAINT [PK_ReportHistory] PRIMARY KEY CLUSTERED ( [ReportHistoryID] ) ON [PRIMARY] GOALTER TABLE [dbo].[Reporthistory] ADD CONSTRAINT [DF_ReportHistory_last_modified] DEFAULT (getdate()) FOR [last_modified]GOINSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Jul 31 2008 1:31AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 3 2003 10:27AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 15 2003 7:09AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,2,15387,'Dec 9 2003 5:41PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (1,1,15387,'Dec 13 2003 11:37AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,1,16836,'Dec 11 2003 10:52AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (2,2,16836,'Dec 11 2003 5:06PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 19 2003 8:49PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 12:23PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,1,17509,'Dec 12 2003 1:40PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (3,2,17509,'Dec 12 2003 2:32PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 1:51PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,2,17221,'Dec 12 2003 2:22PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (4,1,17221,'Dec 12 2003 2:30PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 12 2003 3:53PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 12 2003 4:24PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 16 2003 2:27PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 16 2003 3:38PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 18 2003 2:10PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,2,17257,'Dec 19 2003 12:23PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (5,1,17257,'Dec 19 2003 3:17PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (6,1,18019,'Dec 19 2003 3:05PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (7,1,17111,'Dec 19 2003 4:58PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (8,1,18010,'Dec 19 2003 5:34PM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,1,17773,'Dec 22 2003 10:35AM')INSERT INTO reporthistory (Report_id, Report_status_id, modified_by, last_modified) VALUES (9,2,17773,'Dec 23 2003 5:05AM')Fri, 21 Sep 2012 12:11:15 GMTGF