Get SQL results from another query that has many results and count how many results

toly P 0
Viewed 22

I have a database table with columns IDS and Date. I need to know if there are a certain number of rows (the number predetermined from another table) that meet the criteria of being between each week within a range of a larger span. Lets say '2021-11-29' and '2022-03-01' (In this case 12 weeks). So that the result would look something like this.

I am using this code to get the DayStart and DayEnd:

declare @STARTDATE date;
declare @ENDDATE date;

set @STARTDATE = '2021-11-29';
set @ENDDATE = '2022-03-01';

with Nums as
(
    select 1 as NN
    union all
    select NN + 1 as NN
    from Nums
    where NN < 1000
)
select  
    dateadd(dd, NN, @STARTDATE) as DayStart, 
    dateadd(dd, NN + 6, @STARTDATE) as DayEnd
from 
    Nums
where 
    dateadd(dd, NN + 6, @STARTDATE) <= @ENDDATE
    and datepart(dw, dateadd(dd, NN, @STARTDATE)) = 1 
option (maxrecursion 0)

Any help would be greatly appreciated

sql-server sql
1 Answers

If I'm understanding your question correctly, if you have @STARTDATE and @ENDDATE set already you could do something like this:

SELECT SUM(CASE WHEN Date BETWEEN @STARTDATE AND @ENDDATE THEN 1 ELSE 0) END AS RowCount

That should return the number of rows in that table that are between the date range you pass into your start and end dates.

SD_230
Related