Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting the last entry based on a previous entry. Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 12:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:37 PM
Points: 442, Visits: 795
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
Posted Friday, September 21, 2012 12:32 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:13 AM
Points: 620, Visits: 864
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
Posted Friday, September 21, 2012 1:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 23,081, Visits: 31,611
Based on the sample data you provided in your initial post, what is the expected results?



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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:37 PM
Points: 442, Visits: 795
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
Posted Tuesday, September 25, 2012 1:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:01 PM
Points: 1,304, Visits: 466
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
Posted Wednesday, September 26, 2012 7:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
Posted Thursday, September 27, 2012 10:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
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 »

Add to briefcase

Permissions Expand / Collapse