Filtering record using two different date field

I have this table Tbl1

CREATE TABLE [dbo].[Tbl1](      [Code] [nvarchar](6) NOT NULL,      [Date1] [datetime] NULL,      [Date2] [datetime] NULL,      CONSTRAINT [PK_Tbl1] PRIMARY KEY CLUSTERED   (      [Code] ASC  )  

with the following data:

Code	Date1	Date2
OE1	NULL	2020-12-31 00:00:00.000
OE2	NULL	2021-10-31 00:00:00.000
TE1	2020-11-30 00:00:00.000	2021-01-31 00:00:00.000
TE2	2020-11-30 00:00:00.000	2020-12-31 00:00:00.000
TE3	2020-11-30 00:00:00.000	2022-08-31 00:00:00.000

Let say Date1 is the date where an item is intentionally disposed while Date2 is the actual or should be scrap date. Records should be filtered by these dates with the following scenario.

A. If my selected month is November 2020, only OE1 and OE3 should be display since TE1, TE2, TE3 are disposed on Nov. 2020.

B. If my selected month is less than or equal to October 2020, all codes should be display.

C. If my selected month is December 2020 and beyond, only OE1 and OE2 should be display.

What I've tried is

SELECT * FROM Lapsing WHERE Format(Date2, 'yyyyMM') >= 202011  AND  Format(ISNULL(DisposalDate,'1/1/1990'), 'yyyyMM') < 202011  

and yeah, it only works for the second scenario only (B).

https://stackoverflow.com/questions/66792925/filtering-record-using-two-different-date-field March 25, 2021 at 12:06PM