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...

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.