December 15, 2009 at 3:42 pm
I am trying to pull my result with the highest status value ID (i.e. the latest status) in a approval system.
There are basically 4 tables:
REQ (request)
FXTR (fixture)
REQ_STAT (Status of request)
REQ_STAT_TYPE (Type of request status)
The SQL goes as follows:
[font="Courier New"]select rq.[REQ_I],
rq.[REQ_NUM],
rt.REQ_TYPE_N as ReqType,
(convert(varchar,
rq.[REQ_DATE],
101 ))as ReqDate ,
rq.[REQ_BY_LANID],
rq.[REQ_CMNO],
zd.DivName as Division ,
(convert(varchar,
rq.[REQ_IMPL_DATE],
101)) as ImpDate
--Below request status name is commented out
--rst.[REQ_STAT_TYPE_N] as RqsType
from [TRIX].[dbo].[REQ] rq
inner join zDIV zd on zd.DivID = rq.[REQ_DIV_I]
inner join REQ_TYPE rt on rt.[REQ_TYPE_I] = rq.[REQ_TYPE_I]
--Below inner joins are commented out
--inner join REQ_STAT rqs on rqs.[REQ_I] = rq.[REQ_I]
--inner join REQ_STAT_TYPE rst on rst.[REQ_STAT_TYPE_I] = rqs.[REQ_STAT_TYPE_I]
where 0 = 0
and rq.[REQ_I] in
(
--I m getting the request ID s for the maximum status below
select [TRIX].[dbo].[REQ_STAT].[REQ_I]
from [TRIX].[dbo].[REQ_STAT] ,
(
select [TRIX].[dbo].[REQ_STAT].[REQ_I], max( [REQ_STAT_TYPE_I] ) as MaxStatID
from [TRIX].[dbo].[REQ_STAT]
group by [TRIX].[dbo].[REQ_STAT].[REQ_I]) as subQuery
where [TRIX].[dbo].[REQ_STAT].[REQ_I] = subquery.[REQ_I]
and [TRIX].[dbo].[REQ_STAT].[REQ_STAT_TYPE_I]= subQuery.MaxStatID
) [/font]
The result is that i get only dataset for only the highest value of request status. so for example if the request has gone thru status 1, 2, 3, 4 then only the status 4 results. These status are stored in REQ_STAT which also holds REQ_I and FXTR_I . For example 4 could be approved Status , 3 could be pending and so on which I have stored in REQ_STAT_TYPE.
The SQL above works fine.
My problem is when i join the REQ_STAT rqs and REQ_STAT_TYPE to get the status names (i.e. approved, cancelled, pending), it does not give me the highest values. It will give me all values in REQ. That's why i have it commented out currently.
Really appreciate your input and suggestions to get this done in a better way if not a fix for the SQL. BTW i m not able to use t-SQL.
December 15, 2009 at 4:04 pm
Please read the first article I have referenced in my signature block below regarding asking for assistance. Please follow the instructions for posting code and sample data. You also need to post expected results based on the sample data.
The more you do up front, the better assistance you will get in return.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply