December 22, 2022 at 5:18 pm
SQL just goes and goes and returns no records if I un-comment this date line:
AND cp.from_service_date BETWEEN d.eff_from AND ISNULL(d.eff_thru,'12/31/9999')
FROM --#baseFilter jBaseClaim
--INNER JOIN dbo.claim_procedure cp ON cp.claim_procedure_id = jBaseClaim.claim_procedure_id
dbo.claim_procedure cp
INNER JOIN dbo.claim c ON c.claim_id = cp.claim_id
INNER JOIN dbo.claim_procedure_2 cp2 ON cp2.claim_procedure_id = cp.claim_procedure_id
INNER JOIN dbo.type_of_service t ON cp.type_of_service_id = t.type_of_service_id
INNER JOIN dbo.eligibility e ON e.eligibility_id = cp.eligibility_id
LEFT JOIN dbo.provider p ON p.provider_id = cp2.pcp_id
INNER JOIN dbo.supplemental_info si ON si.form_key = p.provider_id AND si.form_name = 'provider'
INNER JOIN dbo.supplemental_info_detail d ON si.supplemental_info_id = d.supplemental_info_id
LEFT JOIN dbo.referral r ON r.referral_id = c.referral_id
LEFT JOIN dbo.referral_category rc ON rc.referral_category_id = r.referral_category_id
WHERE e.employergroup_id = 7 -- Careplus
AND cp.from_service_date >= '01/01/2020'
AND t.type_of_service_ud = '1'
AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia')
-- AND cp.from_service_date BETWEEN d.eff_from AND ISNULL(d.eff_thru,'12/31/9999')
AND cp.from_service_date between '01/01/2020' AND '12/31/9999'
AND ISNULL(rc.referral_category_ud,'') <> 'FFS_NO_TAKE_BAC' -
I can't figure out how to make this return records. Any advice greatly appreciated.
December 22, 2022 at 5:50 pm
What happens if you use the following to avoid the function breaking sargability (assumption: tables are indexed to support the query)
AND (cp.from_service_date BETWEEN d.eff_from AND d.eff_thru OR
(d.eff_thru IS NULL AND cp.from_service_date BETWEEN d.eff_from AND '12/31/9999'))
And if from_service_date is a date type or never exceeds '12/31/9999', you could skip the between and just use
AND (cp.from_service_date BETWEEN d.eff_from AND d.eff_thru OR
(d.eff_thru IS NULL AND cp.from_service_date >= d.eff_from)
December 22, 2022 at 6:00 pm
Have you checked to see if the d.eff_thru column is actually NULLable?
If it's not, remove the ISNULL from your formula.
If it IS NULLable, the ISNULL formula is non-SARGable (basically, cannot do a SEEK/RangeScan), then you could try converting it to OR, but that probably won't work. You'll need to do something separately to isolate the rows according to your criteria.
This is why I also say that I have not found any utility in allowing NULLs in range date columns compared to the pain they bring. I'll make the recommendation that you need to change to the non-NULL methodology and set the default to the 999991231 date..
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2022 at 6:01 pm
ISNULL() is not SARGable, so it should be avoided in WHERE clauses. You have three instances of ISNULL() in your WHERE clause. There are a couple of ways to remove ISNULL() from your WHERE clauses. One such strategy is to define your tables, so that the specific fields cannot be NULL. This is particular true when using date ranges, e.g., from_service_date - eff_thru, but can also be used in other places, possibly including supplemental_info_detail.value_2.
You'll need to use a different method for rc.referral_category_ud, since that can be null due to the left outer join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2022 at 6:02 pm
Are you saying it returns rows if you uncomment that line?
If so that doesn't seem possible as adding an AND will just increase the constraints on the query so it can only return fewer rows. So when you comment the AND out it cannot return fewer rows.
December 22, 2022 at 6:39 pm
What happens if you use the following to avoid the function breaking sargability (assumption: tables are indexed to support the query)
AND (cp.from_service_date BETWEEN d.eff_from AND d.eff_thru OR
(d.eff_thru IS NULL AND cp.from_service_date BETWEEN d.eff_from AND '12/31/9999'))And if from_service_date is a date type or never exceeds '12/31/9999', you could skip the between and just use
AND (cp.from_service_date BETWEEN d.eff_from AND d.eff_thru OR
(d.eff_thru IS NULL AND cp.from_service_date >= d.eff_from)
If you are going to go this route, I'd suggest simplifying it so that you only test cp.eff_from once.
AND cp.from_service_date >= d.eff_from
AND (d.eff_thru IS NULL OR cp.from_service_date < d.eff_thru)
You may also find that your query runs faster if you use a UNION instead of using an OR, but that will entail duplicating much of your original query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2022 at 7:20 pm
@krypto69 ...
Ah... maybe not enough coffee when I read it the first time and assumed that "just goes and goes and returns no records" meant that it never stops.
Just to clarify for us, is that what you meant?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2022 at 8:34 pm
Thanks guys! VERY MUCH !
Jeff you are always on point. Thanks for the lesson and Drew also.
Happy Holidays and good karma to you all!
Really good points here. Thanks again.
Ratbak I used your example worked great thanks again.
December 30, 2022 at 9:03 am
Also convert '12/31/9999' to a proper date-type for comparison
In Belgium this would read as the 12th of month 31 in the year 9999
December 30, 2022 at 5:29 pm
You can, and definitely should, get rid of the other ISNULLs in your WHERE:
AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia') --replace with
--> AND d.value_2 NOT IN ('Clay','Volusia')
AND ISNULL(rc.referral_category_ud,'') <> 'FFS_NO_TAKE_BAC' --replace with
--> AND rc.referral_category_ud <> 'FFS_NO_TAKE_BAC'
The rule is: never use ISNULL() in a WHERE or JOIN clause.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 30, 2022 at 7:22 pm
You can, and definitely should, get rid of the other ISNULLs in your WHERE:
AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia') --replace with --> AND d.value_2 NOT IN ('Clay','Volusia')
AND ISNULL(rc.referral_category_ud,'') <> 'FFS_NO_TAKE_BAC' --replace with --> AND rc.referral_category_ud <> 'FFS_NO_TAKE_BAC'
The rule is: never use ISNULL() in a WHERE or JOIN clause.
If d.value_2 can be NULL - changing it to your recommendation will not produce the same results because it will exclude NULLs.
The ISNULL around rc.referral_category_ud can be removed. However, as it stands now - regardless of using ISNULL or not it is converting that outer join to effectively an inner join. If that is the desired result then also change the join for that table.
I would change to a CROSS APPLY and eliminate the check for eff_thru. You want the data that was effective for that service date and you can get that using TOP 1 where eff_from is less than or equal to the from_service_date - ordered by eff_from desc (latest effective from date that was in effect for that service date).
If there is no supplemental data for that service date - the cross apply would not return any results and that row would be eliminated, so no need to include anything in the where clause for that condition.
You can also eliminate this AND cp.from_service_date between '01/01/2020' AND '12/31/9999' - no need to check an end range and it is already being checked for the start range.
You should also change the join for the provider to an inner join - since you are looking for supplemental data that is tied to that provider.
Actually, I would probably move Provider, Supplemental_Info and Supplemental_Info_Detail into that cross apply and return the relevant data for each column. That would move the check for value_2 inside the cross apply and make sure the data returned is the correct data for that service date.
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
December 30, 2022 at 9:43 pm
ScottPletcher wrote:You can, and definitely should, get rid of the other ISNULLs in your WHERE:
AND ISNULL(d.value_2,'') NOT IN ('Clay','Volusia') --replace with --> AND d.value_2 NOT IN ('Clay','Volusia')
AND ISNULL(rc.referral_category_ud,'') <> 'FFS_NO_TAKE_BAC' --replace with --> AND rc.referral_category_ud <> 'FFS_NO_TAKE_BAC'
The rule is: never use ISNULL() in a WHERE or JOIN clause.
If d.value_2 can be NULL - changing it to your recommendation will not produce the same results because it will exclude NULLs.
The ISNULL around rc.referral_category_ud can be removed. However, as it stands now - regardless of using ISNULL or not it is converting that outer join to effectively an inner join. If that is the desired result then also change the join for that table.
I would change to a CROSS APPLY and eliminate the check for eff_thru. You want the data that was effective for that service date and you can get that using TOP 1 where eff_from is less than or equal to the from_service_date - ordered by eff_from desc (latest effective from date that was in effect for that service date).
If there is no supplemental data for that service date - the cross apply would not return any results and that row would be eliminated, so no need to include anything in the where clause for that condition.
You can also eliminate this AND cp.from_service_date between '01/01/2020' AND '12/31/9999' - no need to check an end range and it is already being checked for the start range.
You should also change the join for the provider to an inner join - since you are looking for supplemental data that is tied to that provider.
Actually, I would probably move Provider, Supplemental_Info and Supplemental_Info_Detail into that cross apply and return the relevant data for each column. That would move the check for value_2 inside the cross apply and make sure the data returned is the correct data for that service date.
Oops, quite right, for not in, should be this instead:
--> AND (d.value_2 IS NULL OR d.value_2 NOT IN ('Clay','Volusia'))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply