September 21, 2012 at 12:11 pm
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')
September 21, 2012 at 12:32 pm
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)
SELECTReport_id, Report_status_id, last_modified
FROMreporthistory
ORDER BY Report_id, last_modified DESC
SELECTt1.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
WHEREt1.Report_status_id = 1
ANDt2.Report_status_id = 2
_____________________________________________________________________
- Nate
September 21, 2012 at 1:54 pm
Based on the sample data you provided in your initial post, what is the expected results?
September 21, 2012 at 3:51 pm
Thanks for the response
here is a sample of the desired output.
for report_id 1
511153872003-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
1441172212003-12-12 14:30:00.000
for report_id 5
2151172572003-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
September 25, 2012 at 1:14 am
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
I Have Nine Lives You Have One Only
THINK!
September 26, 2012 at 7:36 pm
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:
ReportHistoryIDReport_idReport_status_idPrev_status_idmodified_bylast_modified
5112153872003-12-13 11:37:00.000
14412172212003-12-12 14:30:00.000
21512172572003-12-19 15:17:00.000
September 27, 2012 at 10:36 pm
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
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply