MS SQL - how to simply the code by loop/recursive table?

1

select ID, DT from Table where Date=DT
union all
select ID, DT+1 from Table where Date=DT+1
union all
select ID, DT+2 from Table where Date=DT+2
...
till DT+n=today

sometimes I have to grap 100+ days. I don't want to write 100+ lines.
can loop statement or recursive table to simplify the code?

2

welcome.

select ID, DT
  from Table 
where (Date between DT 
and DATEADD (day, 100, DT ) )
3

hi, Yosiasz:
Thank you for your reply. But that's not what I am look for ...
my data is about residents in a room. residents entered and left the room at different dates. I need to count the # of residents each day. the actual code looks like this:

select ID, DT from Table where StartDate<=DT+1 and EndDate>=DT
union all
select ID, DT+1 from Table where StartDate<=DT+2 and EndDate>=DT+1
union all
select ID, DT+2 from Table where StartDate<=DT+3 and EndDate>=DT+2
...

4

You didn't give us much info to go on. And no sample data at all.

A "standard" "tally table" should help you get what you want here. Google "SQL tally table" if you're not familiar with them. It's literally a table that is just a list of sequential numbers.

I assumed that the DT column in your data table is a date (rather than a datetime/2). If it's not, the code will need adjusted.

DECLARE @EndDate date
DECLARE @StartDate date

SET @StartDate = '20211101'
SET @EndDate = '20211231'

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT 0 AS number 
    UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number 
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT DATEADD(DAY, t.number, DT) AS DT, COUNT(*) AS residents_count
FROM cte_tally1000 t
LEFT OUTER JOIN [dataTable] d ON @StartDate <= DATEADD(DAY, t.number + 1, d.DT) AND
    @EndDate >= DATEADD(DAY, t.number, d.DT)
1 Like
5

Thank you.

Yes. DT is a user- defined date. Sorry that I am new to coding and did not explain it clearly.
I searched tally table and I think I get what you mean. I’ll try tomorrow to see if I can make it work.