It looks like you are using a NULL value to represent an unknown/unspecified date.  While you may not be able to change this, it's better to use a specific unlikely...

Another thing I should point out is that Johan's code assumes that your data is "clean", that is, that there are no overlaps.  Mine will handle overlaps.  An easy way...

Here is a different approach based on an article by Itzik Ben-Gan.

There are three types of intervals that you can work with:

• Open intervals -- Neither endpoint is included
• Half-closed...

• This reply was modified 1 years ago by drew.allen. Reason: Comment about the SUM being zero or NULL was on the wrong line
• This reply was modified 1 years ago by drew.allen.

When you define your CTE, you have to specify the column names...

WITH cteExample (FirstName, LastName, IDNo)
AS
(
SELECT fName, LName, NotMySSN
FROM tableX
)
SELECT FirstName, LastName
FROM cteExample
WHERE IDNo > 10;

If...

Your final select in that statement is

select contractnumber from d group by contractnumber having count(*)>1

Those added columns do not appear in that SELECT, so they do not...

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.

SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", (A."Debit")...

Do you really need to create 12 temporary tables to demonstrate the problem?

Also, you should use the {;} Code button to format your code.  It makes it much easier to...

Photos of data are not data.  If it's not worth your time to provide actual data, it's not worth my time to scrape your photos for actual data.

Drew

I believe that this also works.  I have no idea which is more efficient.

CREATE PROCEDURE ListNeedsResult

@RecordID int

AS

SET NOCOUNT ON

SELECT 
Sequence,
Need
FROM
dbo.NeedsAssessment 
WHERE EXISTS
(
SELECT * from Assessments
WHERE RecordID =...

There is no way for us to help you with the minimal information that you have given.  There is likely a problem with your JOIN conditions resulting in too few/many...

Please don't cross post.  It tends to fragment the discussion.

Please respond to this post https://www.sqlservercentral.com/forums/topic/count-of-total-appointments-within-30-days-of-each-appointment-date

Drew

Sorry, sorry my apologies.  I didn't catch the inconsistency.  The green arrows in the image you posted only point up!  Afaik that's the part I missed.  Once there's an...

Here is a slightly different approach.  There is no discernable difference in the performance for the sample data.  When I add a primary key on Number and StartTime, this approach...

• This reply was modified 1 years, 5 months ago by drew.allen. Reason: Changed COUNT to SUM

Another thing.  In cases such as this, it's usually best to start a new post and reference the old post instead of hijacking the original post.  Otherwise it can get...

The problem is that your data is stored as closed intervals (both end-points are included in the interval) when this issue requires half-closed intervals (only one end-point is included in...