Pretend today 2022-10-24

case 1

case 1 depend on table above, I want to return only 1 row at id 1, why cause today 2022-10-24 still between startDate and endDate

case 2

case 2 depends on table above. I want to return only 1 row at id 2. Why cause when startDate has the same value between id 1 & 2, so choose endDate with NULL value.

I am still confused about how to implement this with query. I want to make for one query logic. When running query so when use case 1 return id 1 and when I use for case 2 return id 2.

CodePudding user response：

As I mention in the comments, seems you just need some simple >= and <(=) logic (while handling NULLs) and a "Top 1 per group":

WITH CTE AS(
    SELECT id,
           productCode,
           version,
           startDate,
           endDate,
           ROW_NUMBER() OVER (PARTITION BY productCode ORDER BY Version DESC) AS RN --Guessed the required partition and order clauses
    FROM dbo.YourTable
    WHERE startDate <= CONVERT(date,GETDATE())
      AND (endDate >= CONVERT(date,GETDATE()) OR endDate IS NULL))
SELECT id,
       productCode,
       version,
       startDate,
       endDate
FROM CTE
WHERE RN = 1;