Getting the last entry based on a previous entry.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Getting the last entry based on a previous...

Getting the last entry based on a previous entry.

Rate Topic

Display Mode

Topic Options

Author

Message

Posted Friday, September 21, 2012 12:11 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:32 AM
Points: 421, Visits: 589

Hello,
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 1

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.
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]
GO

ALTER TABLE [dbo].[Reporthistory] WITH NOCHECK ADD
CONSTRAINT [PK_ReportHistory] PRIMARY KEY CLUSTERED
(
[ReportHistoryID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Reporthistory] ADD
CONSTRAINT [DF_ReportHistory_last_modified] DEFAULT (getdate()) FOR [last_modified]
GO
INSERT 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')

Post #1362891

RP_DBA

Posted Friday, September 21, 2012 12:32 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:31 PM
Points: 580, Visits: 814

GF (9/21/2012)

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.

Might not be exactly what you need but hopefully gives you some ideas.

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_modified
FROM	reporthistory
ORDER BY Report_id, last_modified DESC

SELECT	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_modified
FROM	@tmp t1
		INNER JOIN @tmp t2 ON t1.Report_id = t2.Report_id AND t1.RowId + 1 = t2.RowId
WHERE	t1.Report_status_id = 1
AND		t2.Report_status_id = 2

_____________________________________________________________________
- Nate

@nate_hughes

Post #1362908

Lynn Pettis

Posted Friday, September 21, 2012 1:54 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,627, Visits: 27,480

Based on the sample data you provided in your initial post, what is the expected results?

Cool

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 #1362964

Posted Friday, September 21, 2012 3:51 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:32 AM
Points: 421, Visits: 589

Thanks for the response
here is a sample of the desired output.

for report_id 1

5 1 1 15387 2003-12-13 11:37:00.000

for report_id 2
nothing returned because a 1 does not follow a 2

For report_id 3
nothing returned because a 1 does not follow a 2

for report_id 4
14 4 1 17221 2003-12-12 14:30:00.000

for report_id 5
21 5 1 17257 2003-12-19 15:17:00.000

for report_id 6
nothing returned because there is no 2, the report has not been submitted.

for report_id 7
nothing returned because there is no 2, the report has not been submitted.

for report_id 8
nothing returned because there is no 2, the report has not been submitted.

for report_id 9
nothing returned because a 1 does not follow a 2

Post #1363022

handkot

Posted Tuesday, September 25, 2012 1:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:19 PM
Points: 1,017, Visits: 433

too mach scans

select 
	* 
from 
	reporthistory rh
where 
	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

Post #1363823

Steven Willis

Posted Wednesday, September 26, 2012 7:36 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 284, Visits: 1,244

This will do it:


IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE 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_modified
FROM
    (
    SELECT
        MAX(rh1.ReportHistoryID) AS ReportHistoryID
    FROM
        #TempTable AS rh1
    GROUP BY
        rh1.modified_by
    ) AS Sub1
INNER JOIN 
    #TempTable AS t1
    ON Sub1.ReportHistoryID = t1.ReportHistoryID
INNER JOIN 
    #TempTable AS t2
    ON t1.ReportHistoryID = t2.ReportHistoryID + 1
WHERE
    t1.Report_status_id = 1
    AND t2.Report_status_id = 2

Output:

ReportHistoryID Report_id Report_status_id Prev_status_id modified_by last_modified
5 1 1 2 15387 2003-12-13 11:37:00.000
14 4 1 2 17221 2003-12-12 14:30:00.000
21 5 1 2 17257 2003-12-19 15:17:00.000

Post #1364998

Sergiy

Posted Thursday, September 27, 2012 10:36 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 4,557, Visits: 8,213

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.LastModified
R1.[Report_status_id] = 1

Post #1365600

« Prev Topic | Next Topic »

Permissions