question

craftybooth avatar image
craftybooth asked

How to use a CASE Statement or a HAVING clause to eliminate rows from query output

Consider the following output: How can I have the query only return rows where the LatestToDate for any RenewCnt > 1 is > the LatestToDate for RenewCnt1? In this case, I would not want to return rows 2,3,4. This is only an example, the actual output will be much greater but in all cases there will be a grouping by ProjectID and SalesItem.

CREATE TABLE [dbo].Project
 (ProjectID varchar(25) NOT NULL,
 SalesItem varchar(50) NOT NULL,
 NewRenewal varchar(15) NULL,
 LatestToDate date NULL,
 RenewCnt int NOT NULL)


INSERT INTO dbo.Project
VALUES ('71684310165','Population Focus','New','2021-07-31',1),
  ('71684310165','Population Focus','Renewal','2019-06-30',2),
  ('71684310165','Population Focus','Renewal','2019-06-30',2),
  ('71684310165','Population Focus','Renewal','2019-06-30',2),
  ('71684310165','Population Focus','Renewal','2023-01-31',3)


SELECT ProjectID
   ,SalesItem
   ,NewRenewal
   ,LatestToDate
   ,RenewCnt
FROM dbo.Project

case-statement
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@craftybooth Is it possible (for the same ProjectID/SalesItem group of rows) to have more than 1 record with RenewCnt = 1? It looks like for each ProjectID/SalesItem group of rows you have just one record with RenewCnt = 1 followed by some arbitrary number of records where RenewCnt is greater than 1, so all you need to do is to remove those rows where RenewCnt is greater than 1 which have their respective LatestToDate values smaller than the value of the same column for the record where RenewCnt = 1. The reason I have to ask is because if there are multiple rows with RenewCnt = 1 per group of ProjectID/SalesItem rows, then it is kinda difficult to figure out which date to honour. Also, please clarify whether the SQL Server in question is 2012 or newer, in which case the solution would be trivial, provided that my assumption about the data is correct (just one row with RenewCnt = 1 per ProjectID/SalesItem combination followed by multiple rows where RenewCnt is greater than 1).

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.