How to get the row for the current date?

Question

Pretend today 2022-10-24

case 1

id	productCode	version	startDate	endDate
1	AAA	1	2022-10-01	2022-10-28
2	AAA	2	2022-10-29	NULL

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

id	productCode	version	startDate	endDate
1	AAA	1	2022-10-01	2022-10-28
2	AAA	2	2022-10-01	NULL

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.

Seems like you want a `BETWEEN` with a [top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). — Thom A, Oct 24 '22 at 09:30
@ahmed yes for now still 2 version. my question above, i want to make one query logic for case 1 and case 2. — helsaKidsCom, Oct 24 '22 at 09:41

score 1 · Accepted Answer · answered Oct 24 '22 at 09:51

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;

How to get the row for the current date?

1 Answers1