I have a table with items similar to the following which contains item names, SoldDate (if sold) and DisplayInWebsiteDate (this is date upto which that item should be displayed in website if not sold)
Id ItemName IsSold SoldDate DisplayInWebsiteDate
-------------------------------------------------------------------------
1 Shirt 0 NULL 2020-03-28
2 Pant 1 2019-10-20 2020-04-25
3 Jacket 1 2020-01-05 2020-01-20
4 Trouser 0 NULL 2020-01-10
I want to SELECT the items based on the following conditions:
1. If item is not Sold i.e SoldDate is NULL then check if its DisplayInWebsiteDate is
greater than current date and that would be valid
2. If item is Sold i.e SoldDate has some date then ignore DisplayInWebsiteDate and check if that item was
sold within last 30 days, and display. If it was sold more than 30 days earlier, then don't get that record
Today's date is 2020-01-22 Therefore, the result would be following
1. Shirt is VALID because it is not sold, and DisplayInWebsiteDate is greater than today's date
2. Pant is INVALID because it is sold, so we ignore its DisplayInWebsiteDate.
And its sold date has passed more than 30 days form today's date
3. Jacket is VALID because it was sold just 17 days ago i.e it falls within range of 30 days sold date
4. Trouser is INVALID because it is not sold and its DisplayInWebsiteDate has already passed on January 10, 2020
Expected Result:
Id ItemName IsSold SoldDate DisplayInWebsiteDate
-------------------------------------------------------------------------
1 Shirt 0 NULL 2020-03-28
3 Jacket 1 2020-01-05 2020-01-20
This should be pretty simple via the following statement:
CREATE TABLE t(
ID int,
ItemName nvarchar(100),
IsSold int,
SoldDate datetime,
DisplayInWebsiteDate datetime
)
INSERT INTO t VALUES
(1, 'Shirt', 0, NULL, '2020-03-28')
,(2, 'Pant', 1, '2019-10-20', '2020-04-25')
,(3, 'Jacket', 1, '2020-01-05', '2020-01-20')
,(4, 'Trouser', 0, NULL, '2020-01-10');
SELECT *
FROM t
WHERE (SoldDate IS NULL AND DisplayInWebsiteDate > GETDATE())
OR (SoldDate IS NOT NULL AND DateDiff(d, SoldDate, GETDATE()) <= 30)
However, you could parametrize the Days and so on... but basically I think that's it.
See SQL Fiddle for details: http://sqlfiddle.com/#!18/36538/4/1