MS SQL Server - Help with SubQuery

Author

Help with SubQuery

sck10

2006-02-17, 11:23 am

Hello,

I have two tables to track Travel request and track Travel Approvals:

tblTravelRequest
--------------------
ApprovalStamp_ID

tblTravelTool_Approv
al
--------------------
ApprovalStamp_ID
Approver_ID (Email Handle)
ApprovalStatus

What happens is that a traveler will submit a travel request. The
traveler's manager will approve it, then a Director will approve it, then
the VP will approve it.

tblTravelTool_Approv
al
--------------------
ApprovalStamp_ID Approver_ID ApprovalStatus
tvl001 MgrHdl approved
tvl001 DirHdl approved
tvl001 ExeHdl NULL
tvl002 MgrHdl approved
tvl002 DirHdl NULL
tvl002 MgrHdl Denied

My "Exe" wants to see a crosstab like the following, so he knows what needs
his immediate approval and what is in process. However, he doesn't want to
see anything that he has already Entered a status for or has been denied by
a manager or director. There are many managers and directors, but one
executive. Here are all the possible combinations that can occure. This
excludes those that are "Denied" since they don't need to be reviewed.

ApprovalStamp_ID Mgr Dir Exe
-------------------- ---- ---- ----
tvl001 Null Null Null
tvl002 Apr Null Null
tvl003 Apr Apr Null

My problem is that I created the subquery to get all the records that need
the Executive's immediate attention (Exe Handle ApprovalStatus=NULL)
, but I
cant figure our how to get the records where the (Mgr Handle
ApprovalStatus=NULL)
or (Dir Handle ApprovalStatus=NULL)
.

Here is my preliminary SubQuery

WHERE
(ApprovalStamp_ID
IN (SELECT ApprovalStamp_ID
FROM tblTravelTool_Approv
al Tapr02
WHERE (Tapr02.ApprovalHandle = 'MyVPHandle') AND
(Tapr02.ApprovalStatus IS NULL)))

--
Thanks in advance,

sck10

Hugo Kornelis

2006-02-17, 8:23 pm

On Fri, 17 Feb 2006 10:10:33 -0600, sck10 wrote:

>Hello,
>
>I have two tables to track Travel request and track Travel Approvals:
>
>tblTravelRequest
>--------------------
>ApprovalStamp_ID
>
> tblTravelTool_Approv
al
>--------------------
>ApprovalStamp_ID
>Approver_ID (Email Handle)
>ApprovalStatus
>
>What happens is that a traveler will submit a travel request. The
>traveler's manager will approve it, then a Director will approve it, then
>the VP will approve it.
>
> tblTravelTool_Approv
al
>--------------------
>ApprovalStamp_ID Approver_ID ApprovalStatus
>tvl001 MgrHdl approved
>tvl001 DirHdl approved
>tvl001 ExeHdl NULL
>tvl002 MgrHdl approved
>tvl002 DirHdl NULL
>tvl002 MgrHdl Denied
>
>
>My "Exe" wants to see a crosstab like the following, so he knows what needs
>his immediate approval and what is in process. However, he doesn't want to
>see anything that he has already Entered a status for or has been denied by
>a manager or director. There are many managers and directors, but one
>executive. Here are all the possible combinations that can occure. This
>excludes those that are "Denied" since they don't need to be reviewed.
>
>ApprovalStamp_ID Mgr Dir Exe
>-------------------- ---- ---- ----
>tvl001 Null Null Null
>tvl002 Apr Null Null
>tvl003 Apr Apr Null

Hi sck10,

If you can be sure that the data is "clean" (i.e. no requests already
approved by Dir, but not yet by Mgr, etc), then you could use this:

SELECT ApprovalStamp_ID,
CASE WHEN CountAppr > 0 THEN 'Apr' END AS Mgr,
CASE WHEN CountAppr > 1 THEN 'Apr' END AS Dir,
NULL AS Exe
FROM (SELECT ApprovalStampID,
COUNT(CASE WHEN ApprovalStatus = 'approved'
THEN 1 END) AS CountAppr,
COUNT(CASE WHEN ApprovalStatus = 'Denied'
THEN 1 END) AS CountDeny
FROM tblTravelTool_Approv
al
GROUP BY ApprovalStampID) AS Der
WHERE CountDeny = 0
AND CountAppr < 3

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

>
>
>
>My problem is that I created the subquery to get all the records that need
>the Executive's immediate attention (Exe Handle ApprovalStatus=NULL)
, but I
>cant figure our how to get the records where the (Mgr Handle
> ApprovalStatus=NULL)
or (Dir Handle ApprovalStatus=NULL)
.
>
>Here is my preliminary SubQuery
>
>WHERE
> (ApprovalStamp_ID
> IN (SELECT ApprovalStamp_ID
> FROM tblTravelTool_Approv
al Tapr02
> WHERE (Tapr02.ApprovalHandle = 'MyVPHandle') AND
>(Tapr02.ApprovalStatus IS NULL)))

--
Hugo Kornelis, SQL Server MVP

Sponsored Links