51159

SQL query to retrieve the latest status of a process

Question:

I'm trying to track the status of multiple proposals that will be in either a "draft," "submitted," or "approved" status. The proposals can go back and forth between draft and submitted multiple times before it is approved so, a junction table was created to track the proposal status changes by proposalPackageID, approvalTypeID, and reviewDate. The query simply (ok, not so simply) needs to return a count of the various statuses.

I've gotten close with the following query (thanks to various posts on this site and google -- nothing really hits the nail on the head) but I'm missing some piece that is creating false counts.

SELECT COUNT(distinctified.approvalTypeID) AS MyCount, distinctified.approvalTypeID, refProposalApprovalType.approvalDescription FROM (SELECT r.proposalPackageID, r.approvalTypeID, MAX(reviewedDate) AS MRD FROM proposalReviewAction as r INNER JOIN proposalPackage ON r.proposalPackageID = proposalPackage.proposalPackageID WHERE proposalPackage.proposalCallID = 7 -- 7=MAIN 8=Sat GROUP BY r.approvalTypeID, r.proposalPackageID) as distinctified INNER JOIN proposalPackage ON distinctified.proposalPackageID = proposalPackage.proposalPackageID INNER JOIN refProposalApprovalType ON distinctified.approvalTypeID = refProposalApprovalType.approvalTypeID WHERE (proposalPackage.proposalCallID = 7) -- 7=MAIN 8=Sat group by distinctified.approvalTypeID, refProposalApprovalType.approvalDescription HAVING (distinctified.approvalTypeID = 25) or (distinctified.approvalTypeID = 50) --25 = submitted 50 = approved order by approvalTypeID

SORRY, had to step away and I forgot to paste the schema and the data...

CREATE TABLE [dbo].[proposalPackage]( [proposalPackageID] [int] IDENTITY(1,1) NOT NULL, [title] [varchar](500) NULL, [proposalCallID][int] NULL, [startDate] [datetime] NULL, [endDate] [datetime] NULL, ) ON [PRIMARY] INSERT INTO [dbo].[proposalPackage] ([title] ,[proposalCallID] ,[startDate] ,[endDate]) VALUES ('test proposal 1',7,'2018-10-01','2019-09-30') ,('test proposal 2',7,'2018-10-01','2019-09-30') ,('test proposal 3',7,'2018-10-01','2019-09-30') ,('test proposal 4',7,'2018-10-01','2019-09-30') ,('test proposal 5',7,'2018-10-01','2019-09-30') ,('test proposal 6',7,'2018-10-01','2019-09-30') ,('test proposal 7',7,'2018-10-01','2019-09-30') ,('test proposal 8',7,'2018-10-01','2019-09-30') ,('test proposal 9',7,'2018-10-01','2019-09-30') ,('test proposal 10',7,'2018-10-01','2019-09-30') CREATE TABLE [dbo].[refProposalApprovalType]( [approvalTypeID] [int] NOT NULL, [approvalDescription] [varchar](50) NULL ) ON [PRIMARY] INSERT INTO [dbo].[refProposalApprovalType] ([approvalTypeID] ,[approvalDescription]) VALUES (5,'Rejected by Approving Official') ,(10,'Rejected by PM') ,(15,'Rejected by Executive Review Board') ,(25,'Submitted for Approval') ,(50,'Approved by Approving Official') ,(75,'Approved by PM') ,(100,'Approved by Executive Review Board') CREATE TABLE [dbo].[proposalReviewAction]( [proposalReviewActionID] [int] IDENTITY(1,1) NOT NULL, [proposalPackageID] [int] NULL, [approvalTypeID] [int] NULL, [comments] [varchar](2000) NULL, [reviewedByID] [int] NULL, [reviewedDate] [datetime] NULL ) ON [PRIMARY] INSERT INTO [dbo].[proposalReviewAction] ([proposalPackageID] ,[approvalTypeID] ,[comments] ,[reviewedByID] ,[reviewedDate]) VALUES (1,25,'Submit',101,'2018-05-01') ,(2,25,'Submit',102,'2018-05-01') ,(3,25,'Submit',103,'2018-05-01') ,(4,25,'Submit',104,'2018-05-01') ,(5,25,'Submit',105,'2018-05-01') ,(1,10,'Incomplete',1001,'2018-05-10') ,(2,10,'Incomplete',1001,'2018-05-10') ,(3,10,'Incomplete',1001,'2018-05-10') ,(4,50,'Approved',1001,'2018-05-10') ,(1,25,'Resubmit',101,'2018-05-21') ,(2,25,'Resubmit',102,'2018-05-21') ,(3,25,'Resubmit',103,'2018-05-21') ,(7,25,'Submit',107,'2018-05-22') ,(1,10,'REJECTED',1001,'2018-05-22') ,(2,10,'REJECTED',1001,'2018-05-22') ,(3,10,'Approved',1001,'2018-05-22') ,(1,25,'Resubmit',101,'2018-05-23') ,(8,25,'Submit',108,'2018-05-23') ,(10,25,'Submit',110,'2018-05-24') ,(3,15,'REJECTED',1010,'2018-05-25')

I would expect to see

MyCount approvalTypeID approvalDescription 6 25 Submitted for Review 1 50 Approved by Approving Official

Any help is appreciated...

Answer1:

I think your subquery was not correctly getting the latest status for each package.

If possible using the ROW_NUMBER() window function makes it easy to identify the latest status row for each package while keeping the other column values for that row.

The following query would get you just the latest status for each package:

SELECT ls.* FROM ( SELECT r.proposalPackageID, r.approvalTypeID, RowNr = ROW_NUMBER() OVER (PARTITION BY r.proposalPackageID ORDER BY r.reviewedDate DESC) FROM proposalReviewAction AS r JOIN proposalPackage AS pp ON pp.proposalPackageID = r.proposalPackageID WHERE pp.proposalCallID = 7) AS ls -- 7=MAIN 8=Sat WHERE ls.RowNr = 1 AND ls.approvalTypeID IN (25, 50); -- 25 = sumbitted 50 = approved

Note the WHERE ls.RowNr = 1 which is the filter removing older statuses. Ensure that the filter for approvalTypeID IN (25, 50) is on the outside of the subquery so that we get the true latest status for each package.

Then it is a simple task to join to other tables to return additional columns from those tables to match whatever output format you prefer.

Recommend

  • FOLLOW UP to SQL query to retrieve the latest status of a process
  • Python can't find modules with PYTHONPATH set
  • Confusion with virtualenvs and Python packages
  • Error installing the shuffle library with stack
  • Parsing json files
  • Grunt build not working in Yo Angular
  • how do you output api resource arns from AWS::Serverless::Function (SAM)?
  • Determining proximity between 2 words in a sentence in Python
  • facing errors in installing django-tracking
  • toastr undefined with requirejs
  • how to build a custom jquery ui file
  • How to resolve “Ambiguous reference to a JS library”?
  • python ssl eof occurred in violation of protocol, wantwriteerror, zeroreturnerror
  • Python : Finding an item in a list where a function return a minimum value?
  • Error at typescript compilation with webpack
  • Composer error [UnexpectedValueException]
  • Where can in find the locale objects for d3.js for different countries
  • Can't use openlayers-3 with webpack
  • Intel c/c++ compiler: “could not locate executable icc” (and ecc)
  • Gruntfile.js - Throwing error 'Recursive process.nextTick detected\"
  • How to parse utc date
  • Importing pyplot in a Jupyter Notebook
  • Python 2.7 on OS X: TypeError: 'frozenset' object is not callable on each command
  • Is mp4 stream able with ffserver?
  • Using Netbeans with Cygwin and SDL, including SDL.h creates strange error
  • Converter from SAT to 3-SAT
  • Python 3.2.2, error(scripts to exe)
  • error importing numpy
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • How to get next/previous record number?
  • Akka Routing: Reply's send to router ends up as dead letters
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • unknown Exception android
  • Checking variable from a different class in C#