July 28, 2013 at 8:08 am
Dear friends,
i need an urgent help on a deadline task-
I have a table- source "Pending" which has approver columns as -
The 'Pending' Source table has columns for each requisition as -
Req# Approver 1 Name,Req# Approver 1 Decision,Req# Approver 1 Decision Date,Req# Approver 1 ID,
Req# Approver 2 Name,Req# Approver 2 Decision,Req# Approver 2 Decision Date,Req# Approver2 ID...
----------------------
my requirement is that:-
i need to Get the name of last approver in req approval path. E.g., say the following are listed as approvers in this order: Caldwell, Walter; Rondini, Joe; Zuccaro, Leo. This field should show Zuccaro, Leo.
and to do this In pending tables, it would be the last one before a null, in the Req# Approver # Name field. hence i needed help with the code on how to grab the last approver before a null, in the Req# Approver # Name field.
thanks
DJ
July 28, 2013 at 8:55 am
Dear friends,
I wrote a query to get the last approver as below- will this help me to get the lastapprover in the chain before a null:
---
SELECT [Requisition NO]
,[Req# Approver 1 Name]
,[Req# Approver 1 Decision]
,[Req# Approver 1 Decision Date]
,[Req# Approver 1 ID]
,[Req# Approver 2 Name]
,[Req# Approver 2 Decision]
,[Req# Approver 2 Decision Date]
,[Req# Approver 2 ID]
,[Req# Approver 3 Name]
,[Req# Approver 3 Decision]
,[Req# Approver 3 Decision Date]
,[Req# Approver 3 ID]
,[Req# Approver 4 Name]
,[Req# Approver 4 Decision]
,[Req# Approver 4 Decision Date]
,[Req# Approver 4 ID]
,[Req# Approver 5 Name]
,[Req# Approver 5 Decision]
,[Req# Approver 5 Decision Date]
,[Req# Approver 5 ID]
,[Req# Approver 6 Name]
,[Req# Approver 6 Decision]
,[Req# Approver 6 Decision Date]
,[Req# Approver 6 ID]
,[Req# Approver 7 Name]
,[Req# Approver 7 Decision Date]
,[Req# Approver 7 Decision]
,[Req# Approver 7 ID]
,[Req# Approver 8 Name]
,[Req# Approver 8 Decision]
,[Req# Approver 8 Decision Date]
,[Req# Approver 8 ID]
,[Master REQ ID]
,[Req# Creation Date]
,[Req# Last Modification Date]
,[Ageing]
,[Age Group]
,[Template ID]
,[Req# User Group]
,[Create_Date]
, case
when [Req# Approver 1 ID] is null
then null
when [Req# Approver 2 ID] is null
then [Req# Approver 1 ID]
when [Req# Approver 3 ID] is null
then [Req# Approver 2 ID]
when [Req# Approver 3 ID] is null
then [Req# Approver 2 ID]
when [Req# Approver 4 ID] is null
then [Req# Approver 3 ID]
when [Req# Approver 5 ID] is null
then [Req# Approver 4 ID]
when [Req# Approver 6 ID] is null
then [Req# Approver 5 ID]
when [Req# Approver 7 ID] is null
then [Req# Approver 6 ID]
when [Req# Approver 8 ID] is null
then [Req# Approver 7 ID]
end as [last approver]
FROM [Taleo].[dbo].[Pending]
where [Requisition NO] ='1169026'
--
July 28, 2013 at 1:25 pm
The fatal flaw in your plan is the Pending Requistions table itself. Having the columns setup as you do now makes it not much more than an Excel-style flat file. You can keep adding columns forever and that just adds unneeded complexity.
I hope it's not too late to properly normalize the data into something like this:
IF OBJECT_ID('tempdb..#PendingApproval') IS NOT NULL
DROP TABLE #PendingApproval
CREATE TABLE #PendingApproval
(
[ID] INT IDENTITY(1,1) NOT NULL,
[ReqID] INT NOT NULL,
[ReqDesc] VARCHAR(50) NULL,
[ApproverName] VARCHAR(50) NULL,
[Decision] VARCHAR(50) NULL,
[DecisionDate] DATETIME NULL,
[ApproverID] INT NULL,
[ApproverLevel] INT NULL,
PRIMARY KEY (ID,ReqID)
)
INSERT INTO #PendingApproval
SELECT 1169025,'Desk','Caldwell, Walter','Pass','2013-07-11',234,1 UNION ALL
SELECT 1169025,'Desk','Rondini, Joe','Pass','2013-07-15',234,2 UNION ALL
SELECT 1169025,'Desk','Zuccaro, Leo','Pass','2013-07-16',234,3 UNION ALL
SELECT 1169026,'Refrigerator','Caldwell, Walter','Pass','2013-06-15',234,1 UNION ALL
SELECT 1169026,'Refrigerator','Rondini, Joe','Pass','2013-06-18',234,2 UNION ALL
SELECT 1169026,'Refrigerator','Zuccaro, Leo','Pass','2013-06-30',234,3 UNION ALL
SELECT 1169026,'Refrigerator','Smith, Mary','Pass','2013-07-02',234,4 UNION ALL
SELECT 1169026,'Refrigerator','Madison, James','Pass','2013-07-11',234,5 UNION ALL
SELECT 1169026,'Refrigerator','Malki, John','Pass','2013-07-15',234,6 UNION ALL
SELECT 1169027,'Chair','Monroe, Pete','Pass','2013-07-11',234,1 UNION ALL
SELECT 1169027,'Chair','Martin, Ralph','Pass','2013-07-15',234,2 UNION ALL
SELECT 1169027,'Chair','Zuccaro, Leo','Pass','2013-07-16',234,3
Now you have each approval transaction in its own row and the queries become simple:
SELECT
r.ReqID
,r.ReqDesc
,r.ApproverName
,r.Decision
,r.DecisionDate
,r.ApproverID
,r.ApproverLevel
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ReqID ORDER BY ApproverLevel DESC) AS StatusRow
,*
FROM
#PendingApproval AS pa
) r
WHERE
StatusRow = 1
Now the above table is just an example. I realize your actual data may be a lot more complex and have more columns you haven't mentioned. So of course you can add more columns as necessary. If I was designing a schema for this (based on some generic assumptions), I'd also have other tables set up such as:
IF OBJECT_ID('tempdb..#Requistions') IS NOT NULL
DROP TABLE #Requistions
CREATE TABLE #Requistions
(
[RID] INT IDENTITY(1,1) NOT NULL,
[ReqID] INT NOT NULL,
[ReqDesc] VARCHAR(50) NULL,
PRIMARY KEY (RID,ReqID)
)
IF OBJECT_ID('tempdb..#Approvers') IS NOT NULL
DROP TABLE #Approvers
CREATE TABLE #Approvers
(
[AID] INT IDENTITY(1,1) NOT NULL,
[ApproverID] INT NOT NULL,
[ApproverName] VARCHAR(50) NULL,
[ApproverLevel] INT NULL,
PRIMARY KEY (AID,ApproverID)
)
IF OBJECT_ID('tempdb..#PendingApproval2') IS NOT NULL
DROP TABLE #PendingApproval2
CREATE TABLE #PendingApproval2
(
[PID] INT IDENTITY(1,1) NOT NULL,
[ReqID] INT NOT NULL,
[ApproverID] INT NULL,
[Decision] VARCHAR(50) NULL,
[DecisionDate] DATETIME NULL,
PRIMARY KEY (PID,ReqID)
)
INSERT INTO #Requistions
SELECT 1169025,'Desk' UNION ALL
SELECT 1169026,'Refrigerator' UNION ALL
SELECT 1169027,'Chair'
INSERT INTO #Approvers
SELECT 23456,'Caldwell, Walter',1 UNION ALL
SELECT 23457,'Rondini, Joe',5 UNION ALL
SELECT 23458,'Zuccaro, Leo',3 UNION ALL
SELECT 23496,'Smith, Mary',2 UNION ALL
SELECT 23444,'Madison, James',5 UNION ALL
SELECT 23445,'Malki, John',6 UNION ALL
SELECT 23479,'Monroe, Pete',3 UNION ALL
SELECT 23478,'Martin, Ralph',1
INSERT INTO #PendingApproval2
SELECT 1169025,23456,'Pass','2013-07-11' UNION ALL
SELECT 1169025,23457,'Pass','2013-07-15' UNION ALL
SELECT 1169025,23458,'Pass','2013-07-16' UNION ALL
SELECT 1169026,23456,'Pass','2013-06-15' UNION ALL
SELECT 1169026,23467,'Pass','2013-06-18' UNION ALL
SELECT 1169026,23458,'Pass','2013-06-30' UNION ALL
SELECT 1169026,23496,'Pass','2013-07-02' UNION ALL
SELECT 1169026,23444,'Pass','2013-07-11' UNION ALL
SELECT 1169026,23445,'Pass','2013-07-15' UNION ALL
SELECT 1169027,23479,'Pass','2013-07-11' UNION ALL
SELECT 1169027,23478,'Pass','2013-07-15' UNION ALL
SELECT 1169027,23458,'Pass','2013-07-16'
SELECT
r.ReqID
,r.ReqDesc
,r.ApproverName
,r.Decision
,r.DecisionDate
,r.ApproverID
,r.ApproverLevel
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY req.ReqID ORDER BY app.ApproverLevel DESC) AS StatusRow
,req.ReqID
,req.ReqDesc
,pa2.Decision
,pa2.DecisionDate
,app.ApproverName
,app.ApproverID
,app.ApproverLevel
FROM
#PendingApproval2 AS pa2
INNER JOIN
#Requistions AS req
ON req.ReqID = pa2.ReqID
INNER JOIN
#Approvers AS app
ON pa2.ApproverID = app.ApproverID
) r
WHERE
StatusRow = 1
Now with that you can add names, new requistions, change approval levels, change descriptions, etc, as well as not having to worry about the order of the rows since that (in this case) is handled by the approval level. This also helps with maintaining the integrity of your data by letting you add Foreign Keys which under your existing structure would be impossible to do.
If you cannot make these structural changes permanently in the actual database, you CAN take the data you have and do like I've done above and INSERT the data into either temp tables or views to do your queries. I think it will make things much simpler for you in the long run.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply