<div>
Sorry but I don't understand what you are after. 
 
I don't understand &quot;total in jail&quot; versus &quot;total by day&quot;. &nbsp;Are you wanting a summary row? 
 
A total count on every row should be simple enough like: 

<pre><code class="code-1 nolinks" id="code-20-43307101-1">with cte as (
 select Date_Added, count( BookingID ) as Day_Total
 from bookings
 where In_Jail = 1 and Date_Added &lt;= @endDate
 group by Date_Added
)
select Date_Added, Day_total, sum(Day_Total) over() Total_In_Jail
from cte
order by 1 desc;
</code></pre>
 
If that isn't a good guess, can you provide some sample data and expected results?
</div>

<div>
We need a table "Periods": dtAdded &nbsp; dtFreed 2020-02-03 &nbsp; 2020-02-15 2020-02-11 &nbsp; 2020-02-20 And the table "Dates" will have results: dt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; inmates 2020-02-02 &nbsp; 0 2020-02-03 &nbsp; 1 2020-02-04 &nbsp; 1 2020-02-05 &nbsp; 1 2020-02-06 &nbsp; 1 2020-02-07 &nbsp; 1 2020-02-08 &nbsp; 1 2020-02-09 &nbsp; 1 2020-02-10 &nbsp; 1 2020-02-11 &nbsp; 2 2020-02-12 &nbsp; 2 2020-02-13 &nbsp; 2 2020-02-14 &nbsp; 2 2020-02-15 &nbsp; 2 2020-02-16 &nbsp; 1 2020-02-17 &nbsp; 1 2020-02-18 &nbsp; 1 2020-02-19 &nbsp; 1 2020-02-20 &nbsp; 1 2020-02-21 &nbsp; 0 2020-02-22 &nbsp; 0 &nbsp; &nbsp; USE [testDB] &nbsp; &nbsp; &nbsp; &nbsp;IF NOT Exists(SELECT * FROM sys.tables WHERE name='Dates') &nbsp; &nbsp;CREATE TABLE Dates(dt Date, inmates Int) &nbsp; &nbsp;GO &nbsp; &nbsp; &nbsp; &nbsp;IF NOT Exists(SELECT * FROM sys.tables WHERE name='Periods') &nbsp; &nbsp;CREATE TABLE Periods(dtAdded Date, dtFreed Date) &nbsp; &nbsp;GO &nbsp; &nbsp; &nbsp; &nbsp; SET NoCount ON &nbsp; &nbsp; TRUNCATE&nbsp;TABLE&nbsp;Dates; -- to be able to repeat &nbsp; &nbsp; IF (SELECT&nbsp;Count(*)&nbsp;FROM&nbsp;Dates)&nbsp;=&nbsp;0&nbsp;BEGIN &nbsp; &nbsp; &nbsp; DECLARE @startDate Date = '2020-02-01', @endDate Date = '2020-02-22', &nbsp; &nbsp; &nbsp; @currentDate Date &nbsp; &nbsp; &nbsp; SET @currentDate = @startDate &nbsp; &nbsp; &nbsp; WHILE @currentDate &lt;= @endDate BEGIN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INSERT INTO Dates VALUES(@currentDate,0) -- Dates between start and end &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @currentDate = DateAdd(day,1,@currentDate) &nbsp; &nbsp; &nbsp; END &nbsp; &nbsp;END &nbsp; &nbsp;TRUNCATE TABLE Periods; -- to be able to repeat &nbsp; &nbsp;IF (SELECT&nbsp;Count(*)&nbsp;FROM&nbsp;Periods)&nbsp;=&nbsp;0&nbsp;BEGIN -- If the table is empty &nbsp; &nbsp; &nbsp;&nbsp;INSERT&nbsp;INTO&nbsp;Periods&nbsp;VALUES -- just two inmates to test &nbsp; &nbsp; &nbsp;&nbsp;('2020-02-03','2020-02-15'),('2020-02-11','2020-02-20') &nbsp; &nbsp;END &nbsp; &nbsp;SELECT * FROM Periods -- Checking &nbsp; &nbsp;UPDATE Dates SET inmates = 0; -- to be able to repeat &nbsp; &nbsp;IF (SELECT TOP (1) inmates FROM Dates) = 0 BEGIN -- if - to avoid duplicates on repeat &nbsp; &nbsp; &nbsp; DECLARE cr CURSOR READ_ONLY FAST_FORWARD FOR &nbsp; &nbsp; &nbsp; SELECT p.dtAdded, p.dtFreed FROM Periods p &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DECLARE @dtAddedVar Date, @dtFreedVar Date &nbsp; &nbsp; &nbsp; OPEN cr; &nbsp; &nbsp; &nbsp;&nbsp;WHILE&nbsp;1=1&nbsp;BEGIN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FETCH NEXT FROM cr INTO @dtAddedVar, @dtFreedVar -- one-by-one inmate &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF&nbsp;@@FETCH_STATUS&nbsp;&lt;&gt;&nbsp;0&nbsp;BREAK -- end of cycle &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UPDATE Dates &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET inmates = inmates + 1 -- adding 1 for every day in the jail &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE dt BETWEEN&nbsp; @dtAddedVar AND @dtFreedVar &nbsp; &nbsp; &nbsp;&nbsp;END &nbsp; &nbsp; &nbsp; CLOSE cr; &nbsp; &nbsp; &nbsp;&nbsp;DEALLOCATE&nbsp;cr; &nbsp; &nbsp;END &nbsp; &nbsp;SELECT * FROM Dates -- result (see above)
</div>

<div>
Thanks, I'll give this a rest and report back tomorrow
</div>

<div>
&gt;&gt;but I got the same number for Total_In_Jail, which seems highly unlikely. Below is a screenshot of the data coming back: 
 
Yes, it was a complete guess at what you were after. &nbsp;I guessed you wanted the total in jail, overall, as another column. 
 
I still don't know what results you want because your original question didn't really explain it. 
 
That is why I asked for sample data and expected results.
</div>

Developer & Coffee Roaster

Scott Fell

<div>
I should also mention, if the record for when they were added to jail and when they were released is in a separate table somewhere, we can still take a similar approach but would need to change where we're getting the daily activity. If you don't have a record of when they were released or added and all you have is whether they are still in jail or not, you won't be able to do a daily population unless you start collecting the data into a separate table each day (as a scheduled job for example) or something similar.
</div>

<div>
just a little background here - I'm only 3 months into this job and I was thrust into the position of "head code monkey" when my coworker unexpectedly passed away last month. I've been mostly doing more code work as opposed to database work, so I'm still learning the architecture of this database, which has well over 100 tables, so I appreciate your patience :) Doing some more digging around, I found a table called ArrestReports, which contains both an Arrest Date as well as a Release Date, and is connected to the Bookings table by BookingID. Bookings.In_Jail is typically updated whenever a person is released, but I have found scenarios where this may not happen, leaving it an unreliable field. I want my output to be as follows:<pre><code class="code-1 nolinks" id="code-20-43307564-1">select &lt;fields to select&gt; from Arrest_Reports where ArrestDate between startDate and endDate and &lt;rest of where clause&gt;
startDate and endDate are the date range I want to search for


Incarceration Date &nbsp; &nbsp; &nbsp;Total
01/01/2021 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6
01/02/2021 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9
01/03/2021 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14
01/04/2021 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10
</code></pre> Here's a breakdown on how those totals were calculated:<pre><code class="code-10 nolinks" id="code-20-43307564-2">01-01-2021:
&nbsp; &nbsp;Total = 6
&nbsp; &nbsp;
01-02-2021:
&nbsp; &nbsp;Total = 9
&nbsp; &nbsp; &nbsp; 5 inmates for 01/01/2021 &nbsp; &nbsp; &nbsp; &nbsp; bookingID 103 is being released today, do not include them in daily total
&nbsp; &nbsp; &nbsp; 4 inmates for 01/02/2021 &nbsp; &nbsp; &nbsp; &nbsp; 4 new inmates were booked that day
&nbsp; &nbsp; &nbsp; 
01-03-2021:
&nbsp; &nbsp;Total = 14
&nbsp; &nbsp; &nbsp; 3 inmates for &nbsp;01/01/2021 &nbsp; &nbsp; &nbsp;bookingIDs 101, 103, 105 have release dates &lt;= 01/03/2021
&nbsp; &nbsp; &nbsp; 3 inmates for &nbsp;01/02/2021 &nbsp; &nbsp; &nbsp; bookingID 201 is the only one that doesn't qualifies
&nbsp; &nbsp; &nbsp; 8 inmates for 01/03/2021 &nbsp; &nbsp; &nbsp;bookingID 301 is the only one that doesn't qualifies


01-04-2021:
&nbsp; &nbsp;Total =
&nbsp; &nbsp; &nbsp; 1 inmate for &nbsp;01/01/2021 &nbsp; &nbsp; &nbsp; all bookingIDs except 102 have release dates &lt;= 01/04/2021
&nbsp; &nbsp; &nbsp; 2 inmates for 01/02/2021 &nbsp; &nbsp; &nbsp; release date is null for bookingIDs 201, 203, so they are still in jail
&nbsp; &nbsp; &nbsp; 5 inmates for 01/03/2021 &nbsp; &nbsp; &nbsp; bookingIDs 302, 303, 306, 307, 308 have release dates of null or &gt; 01/04/2021
&nbsp; &nbsp; &nbsp; 2 inmates for 01/04/2021 &nbsp; &nbsp; &nbsp; both bookingIDs are valid
</code></pre> One caveat (the one tripping me up the most) is that all records that have a Release_Date of null should be included in the calculation, even if the Arrest_Date is prior to the startDate range, because they are still part of the population. The only exception to this is when the arrestDate is &gt; the endDate range. So if I have a record with an arrestDate of 4/1/2021 and a release_date of null, and my date range is 03/01/2021 to 03/30/2021, it would be omitted from the total I appreciate all of your help on this! ps - Forgot to leave the structure for ArrestReports. I only included the relevant columns: <pre><code class="code-1 nolinks" id="code-20-43307564-3">CREATE TABLE #ArrestReports (
&nbsp; &nbsp;[BookingID] [int] NOT NULL,
&nbsp; &nbsp;[Arrest_Date] [datetime] NULL,
&nbsp; &nbsp;[Release_Date] [datetime] NULL
)
</code></pre>
</div>

<div>
So, to get the result like I mentioned we can use the same process and just change the table names. We can find the starting population by looking at all inmates who were arrested before the target date who have a NULL Release_Date.<pre><code class="code-1 nolinks" id="code-20-43307592-1">DECLARE @inmates INT = (SELECT COUNT (*) FROM &nbsp;#ArrestReports WHERE Release_Date IS NULL AND Arrest_Date &lt; @startDate)
</code></pre>Then we can use basically the same query, just getting the changes for each day. Here's an example with random sample data, the query is the bottom section <pre><code class="code-10 nolinks" id="code-20-43307592-2">/* SETUP */
&nbsp; &nbsp;/* sample table */
&nbsp; &nbsp;DROP TABLE IF EXISTS #ArrestReports
&nbsp; &nbsp;CREATE TABLE #ArrestReports (
&nbsp; &nbsp;&nbsp; &nbsp;[BookingID] [int] NOT NULL,
&nbsp; &nbsp;&nbsp; &nbsp;[Arrest_Date] [datetime] NULL,
&nbsp; &nbsp;&nbsp; &nbsp;[Release_Date] [datetime] NULL
&nbsp; &nbsp;)
&nbsp; &nbsp;/* end sample table */

&nbsp; &nbsp;/* sample data */
&nbsp; &nbsp;DECLARE @x INT = 1, @y INT = 100, @bookingDate DATETIME
&nbsp; &nbsp;WHILE @x &lt;= @y 
&nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp;&nbsp;SET @bookingDate = DATEADD(d, ABS(CHECKSUM(NEWID())%180), '1/1/2021')
&nbsp; &nbsp; &nbsp;&nbsp;INSERT INTO #ArrestReports SELECT @x, @bookingDate, DATEADD(d, ABS(CHECKSUM(NEWID())%60), @bookingDate)
&nbsp; &nbsp; &nbsp;&nbsp;SET @x = @x + 1
&nbsp; &nbsp;END

&nbsp; &nbsp;UPDATE #ArrestReports
&nbsp; &nbsp;SET Release_Date = CASE WHEN CHECKSUM(NEWID())%8 = 0 THEN NULL ELSE Release_Date END
&nbsp; &nbsp;/* end sample data */
/* end SETUP */

/* QUERY */

DECLARE @startDate DATETIME = '3/1/2021', @endDate DATETIME = '3/7/2021', @popIn INT, @popOut INT

DECLARE @inmates INT = (SELECT COUNT (*) FROM &nbsp;#ArrestReports WHERE Release_Date IS NULL AND Arrest_Date &lt; @startDate)

DECLARE @results TABLE (Population_Date DATETIME, Inmates_In INT, Inmates_Out INT, Total_Population INT)

WHILE @startDate &lt;= @endDate
BEGIN

&nbsp; &nbsp;SET @popIn = (SELECT COUNT(*) FROM #ArrestReports WHERE Arrest_Date = @startDate)
&nbsp; &nbsp;SET @popOut = (SELECT COUNT(*) FROM #ArrestReports WHERE Release_Date = @startDate)
&nbsp; &nbsp;SET @inmates = @inmates + @popIn - @popOut

&nbsp; &nbsp;INSERT INTO @results
&nbsp; &nbsp;SELECT
&nbsp; &nbsp; &nbsp;&nbsp;@startDate,
&nbsp; &nbsp; &nbsp;&nbsp;@popIn,
&nbsp; &nbsp; &nbsp;&nbsp;@popOut,
&nbsp; &nbsp; &nbsp;&nbsp;@inmates
&nbsp; &nbsp;
&nbsp; &nbsp;SET @startDate = DATEADD(d, 1, @startDate)
END


SELECT * FROM @results
SELECT * FROM #ArrestReports
</code></pre>
</div>

<div>
Dustin - Thanks for this, I'll have a go at this in the A.M.
</div>

<div>
I find it better to workout the logic first to address the result. 
 
Presumably each individual has a start date and a release date. 
You would need to combine and verify the range of dates you are specifying are validated against the individual. 
In your case it is a dIk 
 
You are trying to reverse the number of days to number of people on a day. 
 
 
You are looking to join a date to people who were present by having jail =1 and date between when they entered and ifnull(release_date test or sometime in 100 years from now.
</div>

<div>
I'm not seeing the need for accessing the table more than once nor am I seeing the need for a while loop or code to do this. &nbsp;It &quot;should&quot; be able to be done with straight SQL. 
 
I put &quot;should&quot; in air-quotes because I'm still not sure what you want the actual results to look like. 
 
I'll ask one more time: 
Please provide some sample data and the expected results for the data you provide.
</div>

<div>
@slightwv - I put my desired output <a href="https://www.experts-exchange.com/questions/29219096/count-number-of-records-for-each-day.html?anchorAnswerId=43307564#a43307564" rel="ugc">here</a>, right after the text "I want my output to be as follows: ". Basically, I want the date as well as the total number of inmates for that given day, see my example on how I broke down those numbers.
</div>

<div>
Missed the expected results. 
 
Now can we get the sample data that goes along with your descriptions so we can produce the results you want? 
 
For example: 

<blockquote>
 &nbsp; &nbsp; &nbsp;5 inmates for 01/01/2021 &nbsp; &nbsp; &nbsp; &nbsp; bookingID 103 is being released today, do not include them in daily total 
&nbsp; &nbsp; &nbsp; 4 inmates for 01/02/2021 &nbsp; &nbsp; &nbsp; &nbsp; 4 new inmates were booked that day 
&nbsp;
</blockquote>
 
We can try to create dummy data based on your description but we probably won't be able to produce dummy data that gives your exact desired results.
</div>

<div>
The issue you have to haev two tables in my opinion 
one deals with the dates of interest to you inner join on the people table on datetable.date between people_table.datestart and date.end 
 
Basically, you need each day to reflect 
 
 
the other option which I think I scanned over above deals with having a cursor that uses a loop going from datebegin to dateend and pulls the same info. 
 
See if the following is of any help 
 
<a href="https://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par" rel="ugc">https://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par</a>
</div>

<div>
Here's a script you can use to generate some test data: <pre><code class="code-10 nolinks" id="code-20-43307758-1">IF Exists(SELECT * FROM sys.tables WHERE name='#Bookings') drop table #Bookings;
IF Exists(SELECT * FROM sys.tables WHERE name='#ArrestReports') drop table #ArrestReports;

CREATE TABLE #Bookings (
&nbsp; &nbsp;[BookingID] [int] NOT NULL,
&nbsp; &nbsp;[Date_Added] [datetime] NULL
)
go

insert into #Bookings values ( 111, '01-01-2021' );
insert into #Bookings values ( 222, '01-01-2021' );
insert into #Bookings values ( 333, '01-02-2021' );
insert into #Bookings values ( 444, '01-02-2021' );
insert into #Bookings values ( 555, '01-02-2021' );
insert into #Bookings values ( 666, '01-03-2021' );
insert into #Bookings values ( 777, '01-04-2021' );
insert into #Bookings values ( 888, '01-04-2021' );
go

CREATE TABLE #ArrestReports (
&nbsp; &nbsp;[BookingID] [int] NOT NULL,
&nbsp; &nbsp;[Arrest_Date] [datetime] NULL,
&nbsp; &nbsp;[Release_Date] [datetime] NULL
)

insert into #ArrestReports values ( 100, '01-01-2021', '01-04-2020' );
insert into #ArrestReports values ( 101, '01-01-2021', '01-03-2020' );
insert into #ArrestReports values ( 102, '01-01-2021', null );
insert into #ArrestReports values ( 103, '01-01-2021', '01-02-2020' );
insert into #ArrestReports values ( 104, '01-01-2021', '01-04-2020' );
insert into #ArrestReports values ( 105, '01-01-2021', '01-03-2020' );

insert into #ArrestReports values ( 200, '01-02-2021', null );
insert into #ArrestReports values ( 201, '01-02-2021', '01-03-2020' );
insert into #ArrestReports values ( 202, '01-02-2021', '01-04-2020' );
insert into #ArrestReports values ( 203, '01-02-2021', null );

insert into #ArrestReports values ( 300, '01-03-2021', '01-04-2020' );
insert into #ArrestReports values ( 301, '01-03-2021', '01-03-2020' );
insert into #ArrestReports values ( 302, '01-03-2021', null );
insert into #ArrestReports values ( 303, '01-03-2021', null );
insert into #ArrestReports values ( 304, '01-03-2021', '01-04-2020' );
insert into #ArrestReports values ( 305, '01-03-2021', '01-04-2020' );
insert into #ArrestReports values ( 306, '01-03-2021', null );
insert into #ArrestReports values ( 307, '01-03-2021', '01-09-2020' );
insert into #ArrestReports values ( 307, '01-03-2021', null );

insert into #ArrestReports values ( 400, '01-04-2021', '01-05-2020' );
insert into #ArrestReports values ( 401, '01-04-2021', null );









</code></pre>
</div>

<div>
@arnold - I am using 2 tables, see my last comment with what data I'm using
</div>

<div>
&gt;&gt;Here's a script you can use to generate some test data: 
 
Sorry to be a pain but I need a test case that you want us to produce your results for. 
 
Otherwise, I am guessing. &nbsp;I would much rather take data you provide and provide SQL that produces the results you say you want form the provided data. 
 
Then I'll provide a fiddle that shows it all working. 
 

<blockquote>
I found a table called ArrestReports, which contains both an Arrest Date as well as a Release Date, and is connected to the Bookings table by BookingID.
</blockquote>
 
The sample data you provided doesn't have matching bookingIDs.
</div>

<div>
not a pain, I just misunderstood you I think we can get the desired results directly from the ArrestReports table, but just in case, here's some sample data for the Bookings table. As for an actual test case, I want to know how the total amount of inmates per day between the dates of 1-1-2021 and 1-4-2021. Is that what you're looking for? <pre><code class="code-10 nolinks" id="code-20-43307797-1">insert into #Bookings values ( 100, '01-01-2021' );
insert into #Bookings values ( 101, '01-01-2021' );
insert into #Bookings values ( 102, '01-01-2021' );
insert into #Bookings values ( 103, '01-01-2021' );
insert into #Bookings values ( 104, '01-01-2021' );
insert into #Bookings values ( 105, '01-01-2021' );

insert into #Bookings values ( 200, '01-02-2021' );
insert into #Bookings values ( 201, '01-02-2021' );
insert into #Bookings values ( 202, '01-02-2021' );
insert into #Bookings values ( 203, '01-02-2021' );

insert into #Bookings values ( 300, '01-03-2021' );
insert into #Bookings values ( 301, '01-03-2021' );
insert into #Bookings values ( 302, '01-03-2021' );
insert into #Bookings values ( 303, '01-03-2021' );
insert into #Bookings values ( 304, '01-03-2021' );
insert into #Bookings values ( 305, '01-03-2021' );
insert into #Bookings values ( 306, '01-03-2021' );
insert into #Bookings values ( 307, '01-03-2021' );
insert into #Bookings values ( 308, '01-03-2021' );

insert into #Bookings values ( 400, '01-04-2021' );
insert into #Bookings values ( 401, '01-04-2021' );
</code></pre>
</div>

<div>
the two tables are not the same tables I had in mind. 
The two tables you have are releated tables dealing with the person. 
 
you need one table to reflect the date range. I think you can use row_number to generate a date range as a CTE 
 
To clarify my understanding of what you want. 
 
you have a range of dates 2020-01-01-2020-04-01 where you want a daily inmate count. 
the two tables you reference deals with the daily booking record and an inmate records 
 
Your #arrest record makes little sense as the example has an arrest entry after the person was released. or said differently, your release date is before the arrest date 
 
 
table1 
date 
2020-01-01 
2020-01-02 
 
table2 
id,bookingid,startdate,releasedate 
12,Bdsf,2019-12-01,null 
13,ergfd,2020-02-01,null 
14,werterg,2019-12-01,2019-12-31 
15,sdf,2019-12-02,2020-01-02 
 
select a.date,b.bookingid from table1 a inner join table2 b on a.date&gt;=b.stardate and a.date&lt;isnull(b.releasedate,'2025-01-01') 
 
typos if any, 
here the response should be 
2020-01-01,bdsf 
2020-01-01,sdf 
2020-01-02,bdsf 
 
and I think this is what you want 
then if you add a group by a.date and replace the bookingid with count(bookingid) you will get the results you are after 
 
2020-01-01,2 
2020-01-02,1
</div>

<div>
I think I'm still missing something but here it goes. 
 
Note: &nbsp;I had to tweak your release_date's: &nbsp;They were 2020 and the arrest_date's were 2021. 
 

<pre><code class="code-10 nolinks" id="code-20-43307809-1">declare @startDate datetime = '2021-01-01';
declare @endDate datetime = '2021-04-01';


 select Date_Added, count(case when date_added between arrest_date and release_date or release_date is null then 1 end) total_in_jail
 from #bookings b
 join #arrestreports a on b.bookingid=a.bookingid
 where Date_Added between @startDate and @endDate
 group by Date_Added
;
</code></pre>
 
Fiddle here: 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3945c0cc4378dee78faa83a868f4a3ba" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3945c0cc4378dee78faa83a868f4a3ba</a> 
 
If I'm missing something, please modify the fiddle and explain what I'm missing.
</div>

<div>
The issue you need a reference point that indicates the point of interest, in your case, it is daily occupancy based on a period. 
 
you could use your existing table by doubling up on it. 
selecting unique dates from Table1 inner joined to the same table to get the bookingid 
 
i.e. using the date field from the table joined on itself in the same as my example. 
using the where clause that limits the data pull within the dates of interest. 
date of entry, being 
 
 
I think the booking and arrest are the same data set. 
 
The use of between can be off, because the release date could count in your example i.e. 
if the person is released 2020-03-24, the person will still be counted in the 2020-03-24 as being in. 
This is why I used the &gt;= on the arrest day, meaning once in, they count, on release date they are no longer in.
</div>

<div>
you could use 
;with cte 
as ( 
select date from #bookings where dateadded between @startdate and @enddate group by date order by date desc 
) 
 
select a.date, b.bookingid from cte a inner join #arrestreport b on a.date&gt;=b.arrestdate and a.date &lt;&nbsp;isnull(b.releasedate,'3025-01-01') 
 
 
this should return a list booking ids per day for .
</div>

<div>
thanks everyone for all of this, I'm going to go through it in the next hour or so
</div>

<div>
&gt;&gt;you could use 
 
Not seeing the need for a CTE there.
</div>

<div>
hi Slightwv 
 
The booking and arrest record is for the same person. 
To get a daily count of people, occupancy, you can not use the bookingID on which to aggregate data. it has to be done on the date as the common field. 
you want all people present on a date. grouping the two tables on bookingid will confirm that a person booked is the person for whom an arrest record exists. 
might be locked into my own thinking, but your query might error out on syntax ambigous 
 
lets say we have a person booked at one a day from 2020-01-01-2020-01-03 each for a four days &nbsp;stint. 
2020-01-01 you will have 1 
2020-01-02 you will have 1 
2020-01-03 you will have 1 
versus what you should get as an answer 
2020-01-01 1 
2020-01-02 2 
2020-01-03 3 
 
I do not see how your booking on arrestrecords join on bookingid will include people that are already present. 
 
dog, bone. I can not see my way through the logic you are considering. 
I think: 
since the Date is the primary item of interest, it has to be the basis around/on which data is joined and organizes.
</div>

<div>
&gt;&gt;I do not see how your booking on arrestrecords join on bookingid will include people that are already present. 
 
That's why I would like to see sample data and expected results from that sample data. &nbsp;I can easily have missed something in the logic. 
 
If they are in jail, they had to have an arrest_date somewhere in the arrest table. &nbsp;At least I would hope there isn't someone in jail that doesn't?
</div>

<div>
I think that is what the booking, initial entry 
and the arrest record is their stay from arrest to release. 
 
This is why I concluded that the bookingID is in effect, inmate ID. 
 
This is why I figured you could use the booking table for the date source, but as I type this, there is a possibility for a gap in the dates as it relates to incoming new resident, is possible which is why I Am reverting back to using row_number or something similar to generate a table with a single column, date that reflects from datestart to dateend and then have this table join on the arrest table on date &gt;=arrestdate and date&lt;release_date 
 
Potentially using something like the following along with dateadd to create a single column CTE type table 
Below it uses Rownumber 
 
;with cte (date) as ( 
 
 
) 
then do the example with the join date from cte table on arrest record. the logic on that side may need to be adjusted correct. 
since mine will provide a list of 
date,bookingid where the arrest record reflect the individual was present. 
 
Not sure whether I am trying to persuade you of my logic, or trying to confirm my logic would work as needed. 
 
<a href="https://www.codeproject.com/tips/811875/generating-desired-amount-of-rows-in-sql-using-cte" rel="ugc">https://www.codeproject.com/tips/811875/generating-desired-amount-of-rows-in-sql-using-cte</a> 
 
From the table above, modified it to approach my solution. 
 

<pre><code class="code-10 nolinks" id="code-20-43307946-1">declare @datestart as datetime
declare @dateend as datetime
set @datestart='2020-01-01'
set @dateend='2020-04-01'
;WITH InfiniteRows (RowNumber) AS (
 -- Anchor member definition
 SELECT 1 AS RowNumber
 UNION ALL
 -- Recursive member definition
 SELECT a.RowNumber +1 AS RowNumber
 FROM InfiniteRows a
 WHERE a.RowNumber &lt; 90
)

-- Statement that executes the CTE
SELECT dateadd(d,RowNumber,@datestart) as Date
FROM InfiniteRows; inner join #arrestrecord b on datediff(d,b.Arrestdate,dateadd(d,RowNumber,@datestart))&gt;0 and datediff(d,dateadd(d,RowNumber,@datestart),isnull(b.ReleaseDate,'2200-01-01')&gt;0
where datediff(d,dateadd(d,RowNumber,@datestart),@dateend)=0
GO
</code></pre>
</div>

<div>
@slightwv using the test data script I provided earlier (with the correct year on the dates), I tried your suggestion and didn't get the results I would have expected. I expected the following results: Incarceration Date &nbsp; &nbsp; &nbsp;Total 01/01/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 01/02/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9 01/03/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14 01/04/2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10 You were correct to assume the day of the inmates release, they should not be included in the calculation. So if the Release_Date is 1/2/2021, then the total should subtract one from the total since they aren't in jail any more. We do record the arrest/release times, but I was told I don't need to get that granular.<blockquote>That's why I would like to see sample data and expected results from that sample data. &nbsp;I can easily have missed something in the logic.</blockquote>not sure what else you're looking for. I provided the script to create some test data and listed the results I'm expecting. What else can I provide to give you more info? <blockquote>If they are in jail, they had to have an arrest_date somewhere in the arrest table. &nbsp;At least I would hope there isn't someone in jail that doesn't?</blockquote>that is correct, the arrest_date has to exist in order for someone to be booked. @arnold My apologies about the release dates being BEFORE the arrest dates, both sets of dates should be in the year 2020. I tried your CTE suggestion but got nothing returned,
</div>

<div>
Yeah, with slightwv's code you're getting the new inmates/day. &nbsp;Like I mentioned, there are many ways to go about doing this but if you need something going at least in the meanwhile you can use my query. &nbsp;If you don't want the In/Out columns (I included in the query to show the changes) just change the select statement. ​<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=1fbeaeb719fed1cbc87beef1263bdd08" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=1fbeaeb719fed1cbc87beef1263bdd08</a>​​​ 
</div>

<div>
&gt;&gt; I provided the script to create some test data and listed the results I'm expecting. 
 
I missed the results you posted way above was the results from the sample data you posted later. 
 
Try this. &nbsp;It uses a recursive CTE which isn't the most efficient but should be better than temp tables and a while loop. 
 
Given the age of the data, I hard-coded &nbsp;5/1/2021 as 'today'. 
 
If you use it replace: 
coalesce(release_date,'2021-05-01') 
 
with 
coalesce(release_date,getdate()) 
 
Also note that a recursive cte defaults to a 100 recursion limit. &nbsp;It will crash if you have any inmates in jail for more than 100 days. 
 
You can override the the limit with MAXRECURSION: 
<a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15</a> 
 
If you have inmates in jail for years, a recursive CTE probably won't be the way to go. 
 

<pre><code class="code-10 nolinks" id="code-20-43308871-1">with cte as (
	select bookingid, arrest_date, coalesce(release_date,'2021-05-01') release_date from #arrestreports where arrest_date!= coalesce(release_date,'2021-05-01')
	union all
	select bookingid, dateadd(day,1,arrest_date), release_date
	from cte
	where arrest_date &lt; release_date-1
)
select arrest_date, count(*)
from cte
where arrest_date between @startDate and @endDate
group by arrest_date
order by arrest_date
</code></pre>
 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cec4c0f1c34f1e64a246572aa95ad5f1" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cec4c0f1c34f1e64a246572aa95ad5f1</a> 
 
 
 
 
An example using getdate and setting maxrecursion to 5000 (hope you don't have any inmates that have been in for 80 years): 

<pre><code class="code-10 nolinks" id="code-20-43308871-2">SET DATEFORMAT YDM

declare @startDate datetime = '2021-01-01';
declare @endDate datetime = '2021-04-01';


with cte as (
	select bookingid, arrest_date, coalesce(release_date,getdate()) release_date from #arrestreports where arrest_date!= coalesce(release_date,getdate())
	union all
	select bookingid, dateadd(day,1,arrest_date), release_date
	from cte
	where arrest_date &lt; release_date-1
)
select arrest_date, count(*)
from cte
where arrest_date between @startDate and @endDate
group by arrest_date
order by arrest_date
OPTION (MAXRECURSION 5000);
</code></pre>
 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e001fa7b7e9090028ef1772d60a2622d" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e001fa7b7e9090028ef1772d60a2622d</a>
</div>

Web Ninja at large

Big Monty

<div>
Ignore the previous post. &nbsp;I'm an idiot at times... &nbsp;I get too deep into it and forget the obvious. 
 
As soon as I posted, I realized I had an issue: &nbsp;I wasn't limiting the expansion of the arrest date range in the recursive CTE based on start and end dates. &nbsp;That was what was causing the MAXRECURSIVE issue. 
 
I was also returning bookingid in the CTE from old left-over testing. &nbsp;I didn't really need it. 
 
This one shouldn't be a recursion issue as long as your start and end dates don't go over 100 days. 
 
At least that's what I think will happen. &nbsp;I confuse myself at times! 
 

<pre><code class="code-10 nolinks" id="code-20-43308890-1">SET DATEFORMAT YDM

declare @startDate datetime = '2021-01-01';
declare @endDate datetime = '2021-04-01';

with cte as (
	select arrest_date, coalesce(release_date,getdate()) release_date from #arrestreports
	where arrest_date!= coalesce(release_date,getdate())
	union all
	select dateadd(day,1,arrest_date), release_date
	from cte
	where arrest_date &lt; release_date-1
	and arrest_date&gt;=@startdate and arrest_date &lt; @enddate
)
select arrest_date, count(*)
from cte
group by arrest_date
order by arrest_date
;
</code></pre>
 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c43fce8a9a08a7c6ce8753e8ff80e07a" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c43fce8a9a08a7c6ce8753e8ff80e07a</a>
</div>

<div>
One possible issue brought up above that I bet your folks WISH would happen: 
If you specify a date range and one or more days in that range, the jail is empty, no rows will be returned for those days. 
 
If you need a 0 count for that day, I'll need to tweak it a little. &nbsp;Just let me know.
</div>

<div>
There's still an issue with the cte recursion there, it doesn't include the days requested (it only shows days with activity) and the count will get messed up with a gap. &nbsp;That's why I took the approach of running a loop since activity may not be contiguous. For example if I change the last entry to 1-14, it thinks the population on 1-14 is only 1 inmate. &nbsp;Similarly it has an issue with days prior to the start, if you change the first entry to be arrested on 12-1-2020 it thinks the population is 5 on Jan-1. ​<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=5dac11842397de478b68cc6fc1dd3d95" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=5dac11842397de478b68cc6fc1dd3d95</a>​​​
</div>

<div>
Apologies. &nbsp;You grabbed an older fiddle link. &nbsp;I edited my comment a couple times to make sure I had the right fiddle link in there to match the SQL I posted. &nbsp;I had several working copies and kept messing it up. 
 
Unless I'm missing something, using your updated dates, yours also doesn't seem right? 
 
Using the last SQL I had from yours, I also see 5 for 1/1. 
 
Fiddle with your updated data and both of our previous queries: 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9b5dd2cd310f62d0085aaab11ff295cb" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9b5dd2cd310f62d0085aaab11ff295cb</a> 
 
 
In fixing the 1/1 counts, I think I have a simpler version. 
 
Changing the last entry to 1/14 makes the expected results one off for 1/4. 
 
See if this works better. &nbsp;I actually like it a LOT better: 

<pre><code class="code-10 nolinks" id="code-20-43308924-1">with date_range AS (
 SELECT @startdate report_date
 UNION ALL
 SELECT DATEADD(day,1,report_date)
 FROM date_range
 WHERE report_date &lt; @enddate
)
SELECT report_date
FROM date_range
	join #arrestreports on report_date between arrest_date and coalesce(release_date,'2021-05-01')-1
</code></pre>
 
 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9ff28c57540e921b266606646050d72d" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9ff28c57540e921b266606646050d72d</a> 
 
 
 
Even if it is a little off, I'm still 100% convinced it can be done in native SQL with no loops and minimal table accesses.
</div>

<div>
Can you post the entire query you are using based on the posted example?
</div>

<div>
&gt;&gt;which is the report range is 1-1-2021 to 4-1-2021 but does not include 1-14-2021 
 
Funny things with dates as strings: &nbsp;Is '4-1-2021' April 1 or Jan 4th? 
 
In my fiddle notice the: 
SET DATEFORMAT YDM 
 
I took the rage for the report to run from Jan 1 to Jan 4 so 1-14-2021 is outside the report range. 
 
If you modify the fiddle with: 
SET DATEFORMAT YMD, I get a count for 1/14? 
 
&gt;&gt;But whether or not native is a big impact really depends on the size of data required and the frequency in which its accessed. 
 
Another big concern is what tool/process/??? will be running the report and if it can handle loops and code blocks versus a single select. 
 
Sure, you can create a function with the loop logic but that even further complicates things. 
 
So a report for 30 days, your loop hits the arrest table 60 times. &nbsp;90 days, 180 times. 
Mine should hit the table once no matter how many days the report covers. 
 
Native SQL is almost always the best approach.
</div>

<div>
If the query works with the arrest table, why are you using the booking table? Is the booking table an entry record that updates the arrest table?
</div>

<div>
@Dustin your script seems to be doing what I need, thank you! I need to go back and see what exactly this script is going to be used for (I was just asked to create the sql for it), so if performance isn't an issue, it should be what I need. @slightwv &gt;&gt;&gt; Also note that a recursive cte defaults to a 100 recursion limit. &nbsp;It will crash if you have any inmates in jail for more than 100 days. It is quite common that inmates will be detained for more than 100 days. I also imagine, although hopefully not, that 80 years may not be safe to use (I'm thinking of life sentences) and I have no control over enforcing that limit (MAXRECURSION 5000). &nbsp;Are there any issues I should be aware of for using MAXRECURSION? @arnold i think i closed the window i used testing your script, I just remember nothing being returned. Since I already have 2 potential solutions, I'm going to go with them, but I do sincerely appreciate the assistance you provided. I'm going to present these two scripts as possible solutions and try to get more info on how/where this is going to be used. I appreciate the help and will close this question down after I hear back from them. Again, many thanks for the assistance, and have a great 4th this weekend (if you're in America of course)!
</div>

<div>
&gt;&gt;It is quite common that inmates will be detained for more than 100 days. 
 
Don't use that SQL. &nbsp;I changed it then rewrote it entirely. 
 
Try the one in: <a href="#a43308924" rel="ugc">#a43308924</a>
</div>

<div>
That is fine, mine was an example that could require slight adjustments. 
it uses a date range that is created when you define the start/end periods 
and then uses this to get the runs, but was not in this iteration perform the count reports. 
 
Any time you rely on entries in existing tables, you potentially could exclude days where no activity occurred. 
I have seen that slightvw was accounting for days with no activity to try an tabulate the information or using a loop as Dustin provided. 
 
Mine is a run a query, get the data.from the arrest table where both the starting date and release date are available. 
The issues noted by either SLightvw or Dustin that the entry for booking ore release that are inconsistent in format ......that can cause an issue ...can be accounted for using convert if the date matches isdate but in the wrong format...
</div>

<div>
@slightwv That's the one I tried, although it just returned one row. I had to modify it because it didn't have the daily total, only the date. here's what I did: <pre><code class="code-10 nolinks" id="code-20-43309291-1">
declare @datestart as datetime
declare @dateend as datetime
set @datestart='2020-01-01'
set @dateend='2020-04-01'



--drop table #Bookings;
--drop table #ArrestReports;

;WITH InfiniteRows (RowNumber) AS (
&nbsp; &nbsp;-- Anchor member definition
&nbsp; &nbsp;SELECT 1 AS RowNumber
&nbsp; &nbsp;UNION ALL
&nbsp; &nbsp;-- Recursive member definition
&nbsp; &nbsp;SELECT a.RowNumber +1 AS RowNumber
&nbsp; &nbsp;FROM &nbsp; InfiniteRows a
&nbsp; &nbsp;WHERE &nbsp;a.RowNumber &lt; 90
)

-- Statement that executes the CTE
SELECT dateadd(d,RowNumber,@datestart) as Date, count(*)
FROM &nbsp; InfiniteRows inner join #ArrestReports b on datediff(d,b.Arrest_date,dateadd(d,RowNumber,@datestart))&gt;0 &nbsp;and datediff(d,dateadd(d,RowNumber,@datestart),isnull(b.Release_Date,'2200-01-01'))&gt;0
where datediff(d,dateadd(d,RowNumber,@datestart),@dateend)=0
group by InfiniteRows.RowNumber 


</code></pre>
</div>

<div>
&gt;&gt; here's what I did: 
 
That looks nothing like what I posted. 
 
&gt;&gt;although it just returned one row. 
 
I provided the fiddle against your sample data that returned more than one row. 
 
Pay attention to my comment about the dates provided as strings. &nbsp;4-1-2021 can be two different dates depending on where you live 
 
&gt;&gt;I had to modify it because it didn't have the daily total 
 
No idea what that means. &nbsp;I provided a fiddle that showed your expected results based on your sample data.
</div>

<div>
an you post a sample data from the arrest table 
the bookingid is of little to no consequence in terms of identification. and can be excluded. to maintain privacy. 
I can backfill a random bookingid. 
 
my main interest is to see why the datediff comparison used with isnull did not return rows for your test.
</div>

<div>
<div class="content wysiwyg-content fr-view">
I'm attempting to create a query that'll return to me the number of records for a given date range. [code] declare @startDate datetime = '2020-01-01'; declare @endDate datetime = '2020-04-01'; select Date_Added, count( BookingID ) as Total from bookings where In_Jail = 1 and Date_Added &lt;= @endDate group by Date_Added order by 1 desc; [/code] In_Jail tells me if the inmate is still in jail, 1 = true, 0 = false. Right now it's giving me the total number per day, and what I want is to have it tell me the total amount of inmates actively in the jail. I think I'm pretty close, but am having trouble getting over that last piece, so any help is greatly appreciated!
</div>
</div>

I'm attempting to create a query that'll return to me the number of records for a given date range.
[code]
declare @startDate datetime = '2020-01-01';
declare @endDate datetime = '2020-04-01';
select Date_Added, count( BookingID ) as Total
from bookings
where In_Jail = 1 and Date_Added <= @endDate
group by Date_Added
order by 1 desc;
[/code]
In_Jail tells me if the inmate is still in jail, 1 = true, 0 = false. Right now it's giving me the total number per day, and what I want is to have it tell me the total amount of inmates actively in the jail.
I think I'm pretty close, but am having trouble getting over that last piece, so any help is greatly appreciated!

count number of records for each day

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.