Jonathan AC Roberts wrote:
Jeff, Here is the data in a consumable format. I couldn't work out from the question exactly what's wanted so I'll leave it to someone else.
Except for a little bit of unnecessary criteria, it looks to me like you worked it out just fine, Jonathan. All I'm doing below is the same as you except I'm putting the test data into a table to demonstrate that the Cartesian Product can be avoided by the addition of an index and eliminating the extra criteria.
And thanks for posting the readily consumable data. We now have two different methods to show the OP how to help the people both understand the question better and make it easier for them to provide a working coded solution.
Here's my version of the readily consumable test data with a couple of explanations added in.
-- Create and populate the test table.
-- This is not a part of the solution. We are just creating demonstrable,
-- readily consumable test data to test code with.
--===== If it exists, drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#Episode','U') IS NOT NULL
DROP TABLE #Episode
--===== Without knowing anything else about the table, create the test table
-- with a guess at the datatypes. Change then if necessary. The solution
-- code should still work unless you do something totally wonky with the
CREATE TABLE #Episode
EHRClientFK INT NOT NULL
,AdmDate DATE NOT NULL
--===== Assuming that there''s more to this table than just those 3 columns
-- and that a Clustered Index already exists on the table, add a UNIQUE
-- Non-Clustered Index to the table to make it so that we don''t end up
-- with a Cartesian Product due to scans in the upcoming query.
CREATE UNIQUE NONCLUSTERED INDEX IX_Cover01
ON #Episode (EHRClientFK, AdmDate, DischDate)
--===== Populate the test table with test data according to what the OP posted.
INSERT INTO #Episode WITH (TABLOCK)
(EHRClientFK, AdmDate, DischDate)
VALUES ( 2,'20181010','20200220')
Here's one solution almost identical to Jonathan's but without the extra criteria. Please READ THE WARNING IN THE HEADER!
-- Solve the problem in a fashion similar to how Jonathan AC Roberts
-- solved it in his previous post on this thread.
-- To learn how it works, please see the following article on the subject.
-- NOTE THAT THIS CODE COULD BECOME A PERFORMANCE ISSUE IF THERE ARE A LOT
-- OF ROWS FOR ANY GIVEN EHRClientFK VALUE DUE TO MULTIPLE SMALLER
-- CARTESIAN PRODUCTS. IF THAT''S THE CASE, PLEASE POST BACK AND WE''LL USE
-- ANOTHER METHOD.
FROM ##Episode a
JOIN ##Episode b ON a.EHRClientFK = b.EHRClientFK
AND a.AdmDate <= b.DischDate
AND b.AdmDate <= a.DischDate
GROUP BY a.EHRClientFK, a.AdmDate, a.DischDate
HAVING COUNT (*) > 1
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)