Sql Joins !

Question

Sql Joins !

Hengis_me

Valued Member

Points: 73
More actions
January 24, 2021 at 12:30 pm

#3836825

Hi There
Im pretty new to SQL ..and best describe my queries at this stage as basic. I wonder if I could get some thoughts on an sql query that for some reason, the results are missing out some records from one of my join tables. I've added some left joins pulling in fields from various tables, the one table shown in BOLD
Before adding this table in, I was pulling through around 1000 rows... but for some reason after adding what i thought was the right join, it ended up missing a few rows ? Any pointers people can give would be fantastic..and i can't see what I'm doing wrong.

Fingers crossed, Michael
SELECT
DATEPART(HH, [SNAPSHOT_DT]) AS SNAPSHOT_HOUR
,CONVERT(DATE,[SNAPSHOT_DT]) AS SNAPSHOT_DATE
,pat.districtnumber
,PAT.[DOB]
,DATEDIFF (YY,PAT.[DOB],FSS.[SNAPSHOT_DT]) -
CASE WHEN DATEADD(YY,DATEDIFF(YY,PAT.[DOB],FSS.[SNAPSHOT_DT]),PAT.[DOB])
> FSS.[SNAPSHOT_DT] THEN 1
ELSE 0
END AS AGE
,SPEC.[SPC_CODE] AS SPEC_CODE
,SPEC.[SPC_MAIN_DESC] AS SPECIALTY
,DB.BED_WARD AS WARD
,BS.[BS_STATUS] AS BED_STATUS
,OBS.[EarlyWarningScore] AS EARLY_WARNING_SCORE
,OBS.[OxygenMaskType] AS OXYGEN_MASK_TYPE
,[identifier]

FROM [QAHCHILIVESQL21].[BEDVIEW_PROD].[dbo].[FACT_SNAPSHOT] AS FSS WITH (NOLOCK)
LEFT JOIN [QAHCHILIVESQL21].[BEDVIEW_PROD].[dbo].[DIM_BEDSTATUS] AS BS WITH (NOLOCK) ON FSS.[BEDSTATUS_SKEY] = BS.[BEDSTATUS_SKEY]
LEFT JOIN [BEDVIEW].[BedView].[dbo].[tblPatient] AS PAT WITH (NOLOCK) ON FSS.[PMI_ID] = PAT.[InternalPatientNumber]
LEFT JOIN [QAHCHILIVESQL21].[CHIMERA_PROD].[dbo].[SPC_SPECIALTY] AS SPEC WITH (NOLOCK) ON FSS.[CURRENT_PASSPECIALTY_SKEY] = SPEC.[SPC_SKEY]
LEFT JOIN [QAHCHILIVESQL21].BEDVIEW_PROD.dbo.DIM_BED AS DB ON FSS.[BED_SKEY] = DB.[BED_SKEY]
LEFT JOIN [BEDVIEW].[BedviewReceiver].[dbo].[tblObservation] AS OBS WITH (NOLOCK) ON PAT.[DistrictNumber] = OBS.[DistrictNumber]
WHERE
DATEPART(HH, FSS.[SNAPSHOT_DT]) = '16'
AND CONVERT(DATE,FSS.[SNAPSHOT_DT]) = DATEADD(dd,-1,CONVERT(DATE,SYSDATETIME()))
AND PMI_ID is not null
AND
( OBS.[Identifier] in (SELECT MAX(VP.[Identifier]) FROM [BEDVIEW].[BedviewReceiver].[dbo].[tblObservation] AS VP
WHERE PAT.[DistrictNumber] = VP.[DistrictNumber] and VP.[ModifiedDateTime] <= fss.[snapshot_dt] GROUP BY VP.[DistrictNumber])

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

This is actually quite simple - the problem is in your where clause. When you outer join a table - you get all results from the preserved table (the left side) and only those rows that match from the outer table. This means those rows that do not match will have NULL for those column values.

If you then try to use one of those columns in the where clause - you eliminate any rows where the value is NULL, eliminating the rows from the preserved table that do not have matching rows in the outer table.

Instead of using LEFT JOIN - you can probably use an OUTER APPLY. The OUTER APPLY would be constructed to return the appropriate columns based on the correlated values from the outer tables. I think this might work:

Outer Apply (Select Top 1
                    ob.EarlyWarningScore
                  , ob.OxygenMaskType
               From BEDVIEW.BedviewReceiver.dbo.tblObservation As ob
              Where ob.DistrictNumber = pat.DistrictNumber
                And ob.ModifiedDateTime <= fss.snapshot_dt
              Order By
                    ob.Identifier desc
            ) As obs

Not sure - but this should get you started.

Now...a couple notes on your code:

Consider setting up and using synonyms for any linked servers - this will save you if/when that linked server is changed or moved or modified. It also will simplify your code.
Do not CAST/CONVERT date columns and compare to a CAST/CONVERT of a date column. This can prevent index usage and make your queries take much longer than necessary.
If your code is running in the context of the BEDVIEW database - don't use 3-part naming. It isn't needed...if your code is running from a different database, consider using synonyms here also.
If you use synonyms - consider creating a separate schema for those synonyms.

Example schemas:

Create Schema BEDVIEW_PROD Authorization dbo;

Example synonym:

Create Synonym BEDVIEW_PROD.DIM_BED For [QAHCHILIVESQL21].BEDVIEW_PROD.dbo.DIM_BED;

Example usage:

LEFT JOIN BEDVIEW_PROD.DIM_BED AS DB ON FSS.[BED_SKEY] = DB.[BED_SKEY]

Now - if the linked server changes to [QAHCHILIVESQL22] - then all you need to do is the following:

Drop Synonym If Exists BEDVIEW_PROD.DIM_BED;
Create Synonym BEDVIEW_PROD.DIM_BED For [QAHCHILIVESQL22].BEDVIEW_PROD.dbo.DIM_BED;

No code changes and now your code is working with the new linked server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 2

For the date column comparison - you should use the following:

AND FSS.SNAPSHOT_DT >= DATEADD(dd, -1, CONVERT(DATE, SYSDATETIME())
AND FSS.SNAPSHOT_DT <  CONVERT(DATE, SYSDATETIME())

Assuming FSS.SNAPSHOT_DT has the data type of DATE. If it is datetime or datetime2 - then you want to use a different calculation to return the value in the appropriate data type. For example:

AND FSS.SNAPSHOT_DT >= dateadd(day, datediff(day, 0, getdate()) - 1, 0)
AND FSS.SNAPSHOT_DT <  dateadd(day, datediff(day, 0, getdate()), 0)

This will avoid implicit conversions that can have a negative impact on the cardinality estimator. You can avoid all of that by using variables instead:

DECLARE @startDate datetime = dateadd(day, datediff(day, 0, getdate() - 1, 0)
      , @endDate datetime = dateadd(day, datediff(day, 0, getdate()), 0);

Change the data type of the variable to match the data type of FSS.SNAPSHOT_DT - and any implicit conversion performed here will not impact the execution plan.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Collins SSCrazy Points: 2068 More actions · Answer 3

Maybe the correlated subquery in the WHERE clause is converting your LEFT JOIN into an INNER JOIN. Maybe try expressing OBS as OUTER APPLY SELECT TOP(1) ... instead.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2068 More actions · Answer 4

Should've hit refresh 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Hengis_me Valued Member Points: 73 More actions · Answer 5

wow... thank you for the quick response. I've started to make some of the changes...and the query is now pulling back the expected result.. so Thank You.

Still need to incorporate some of the changes to the date field... but this is a great start.

Perfect !

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 6

That is good to hear - thank you for the feedback.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs