I'm trying to create a query that will tell me which requests
took longer than 10 days to move one from particular state to another
state. The query I've created returns the correct requests,
but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.

I'm sure I'm missing something easy, but I can't figure out what it
might be. Any help is appreciated! Thanks,
Myron
-- remove SPAM-KILL from address to reply by email --


DDL for table creation and data population:

CREATE TABLE [dbo].[ReqHistory] (
[Id] [int] NOT NULL ,
[ReqId] [int] NOT NULL ,
[ReqIDStateId] [tinyint] NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RequestStates] (
[ID] [tinyint] NOT NULL ,
[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests] (
[ID] [int] NOT NULL ,
[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusChangeDate] [datetime] NULL ,
[Status] [tinyint] NULL
) ON [PRIMARY]
GO

insert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)
insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)
insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)

insert into ReqHistory values(904,361 ,1,cast('2004-05-03 00:20:55.983' as datetime))
insert into ReqHistory values(931,361 ,2,cast('2004-05-03 01:07:14.157' as datetime))
insert into ReqHistory values(959,361 ,20,cast('2004-05-03 01:29:20.157' as datetime))
insert into ReqHistory values(20250,361 ,31,cast('2004-06-03 08:51:58.950' as datetime))
insert into ReqHistory values(20251,361 ,98,cast('2004-06-03 08:52:03.013' as datetime))
insert into ReqHistory values(20249,361 ,30,cast('2004-06-03 08:51:51.107' as datetime))
insert into ReqHistory values(939,361 ,10,cast('2004-05-03 01:10:36.093' as datetime))
insert into ReqHistory values(7318,1400 ,1,cast('2004-05-13 03:48:01.420' as datetime))
insert into ReqHistory values(7346,1400 ,2,cast('2004-05-13 03:56:37.857' as datetime))
insert into ReqHistory values(7347,1400 ,12,cast('2004-05-13 03:57:03.293' as datetime))
insert into ReqHistory values(7356,1400 ,22,cast('2004-05-13 04:00:58.497' as datetime))
insert into ReqHistory values(7357,1400 ,97,cast('2004-05-13 04:01:55.250' as datetime))
insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))
insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))
insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))
insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))
insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))
insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))

insert into RequestStates values(1,'Awaiting CSMC')
insert into RequestStates values(2,'CSMC Review')
insert into RequestStates values(3,'Reject Awaiting CSMC')
insert into RequestStates values(10,'Awaiting MA Review')
insert into RequestStates values(12,'Awaiting FO Review')
insert into RequestStates values(13,'Awaiting IS Review')
insert into RequestStates values(20,'MA Review')
insert into RequestStates values(22,'FO Review')
insert into RequestStates values(23,'IS Review')
insert into RequestStates values(30,'Func Approval')
insert into RequestStates values(31,'Func Approval Complete')
insert into RequestStates values(96,'Resolved')
insert into RequestStates values(97,'Planning')
insert into RequestStates values(98,'Open')
insert into RequestStates values(99,'Closed')


The query that almost works:

select irh.ReqID, irh.MAactiondate, irh.reviewstate,
irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextState
from (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,
irh.ReqIDStateID As IRHState, irs.statetext as ReviewState
from ReqHistory IRH
join requeststates irs on irs.id = irh.ReqIDStateID
where irh.ReqIDStateID in (20, 23)
group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irh
join (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetext
from (select min(actiondate) as actiondate, ReqID,
min(ReqIDStateID) as IRH2State
from ReqHistory
--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,
--but I couldn't make it work
where ReqIDStateID > 23
group by ReqID) as irh2
join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqID
join requests ir on ir.id = irh.ReqID
where irh.MAactiondate + 10 < irh2.Nextactiondate
order by irh.ReqID


The data being returned is:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 96 Resolved

The data that should have been returned:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMC

Holy subqueries batman!

Thanks for including the DDL... it made finding an answer to this much
easier. A couple quick notes though... you didn't include and foreign
or primary keys. That combined with the inconsistent naming standards
made it a little difficult to understand the code.

The following code returned what you were expecting. The code assumes
that no two actions can occur at the same exact time. If that
assumption is incorrect then the code will not work correctly in those
situations.

Also, instead of the NOT EXISTS you could of course opt to use a LEFT
OUTER JOIN along with checking for IS NULL on one of the PK columns for
the table (RH). That often gives better performance than NOT EXISTS in
my experience.

HTH,
-Tom.

SELECT MAH.ReqID, MAH.ActionDate, RS.StateText, NA.ActionDate,
NA.ReqIDStateID, NS.StateText
FROM ReqHistory MAH
INNER JOIN ReqHistory NA ON NA.ReqID = MAH.ReqID
AND NA.ActionDate > DATEADD(dy, 10,
MAH.ActionDate)
INNER JOIN RequestStates RS ON RS.ID = MAH.ReqIDStateID
INNER JOIN RequestStates NS ON NS.ID = NA.ReqIDStateID
WHERE MAH.ReqIDStateID IN (20, 23)
AND NOT EXISTS (SELECT *
FROM ReqHistory RH
WHERE RH.ReqID = MAH.ReqID
AND RH.ActionDate > MAH.ActionDate
AND RH.ActionDate < NA.ActionDate)

"Thomas R. Hummel" <tom_hummel (AT) hotmail (DOT) com> wrote:

Thanks for the speedy and accurate answer, Tom! Your query looks a
lot more elegant than my monster, and it found rows that my original
was dropping.
Myron

Your basic design is wrong. Time comes in durations and not points --
look at everyone from Zeno to the ISO standards. Also, your data
element names make no sense -- "ReqIDStateId"??? if something is a
state, then it is a value and not an indentifier. What is the vague
"ID": floating around? Surely you do not blindly use sequentail
numbering in an RDBMS to mimic a sequential file physical record
number!

CREATE TABLE RequestHistory
( request_nbr INTEGER NOT NULL ,
request_status INTEGER NOT NULL
REFERENCES RequestStatusCodes(request_status)
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
PRIMARY KEY (request_nbr, start_date ));

<<

Trival with the right design, isn't it?