December 14, 2011 at 10:42 am
Hello guys,
I need some help in solving this issue
In the below mentioned query i need to get the ActiveDate, InActiveDate for the corresponding ParentProviderID and need to compare the ActiveDate and InActiveDate in Query2. But the problem is Query 2 returns more than 1 record so how do i loop through it to make sure that i compare the correct Active and InActive dates for the respectiveParentProviderID
Any help would be highly appreciated
Query1:
select @ParentProviderID = rprvi1.ParentProviderID, @ActiveDate = rprv1.ActiveDate, @InActiveDate = rprv1.InactiveDate
from hrp_Provider rprv1,hrp_ProviderInstance rprvi1,hrp_Provider sprv1 ,hrp_ProviderInstance sprvi1, hrp_Provider bprv1
where rprv1.Id = rprvi1.ProviderID
and rprvi1.ParentProviderID = sprv1.ID and rprv1.RenderingFlag = 'Y' and sprv1.ServiceLocFlag = 'Y'and sprv1.Retired = 'N'
and sprv1.ID = sprvi1.ProviderID and sprvi1.ParentProviderID = bprv1.ID and bprv1.BillingFlag = 'Y'
and bprv1.Retired = 'N' and bprv1.OrgType <> 'LP DIRECTLY OP' --exclude LP DO dups
and rprvi1.ProviderID = 162509
Query2:
select distinct(rprv.ID)
from hrp_Provider rprv ,hrp_ProviderInstance rprvi ,hrp_Provider sprv,
hrp_ProviderInstance sprvi ,hrp_Provider bprv ,hrp_ProviderAltID ralt
where ralt.ProviderID in
( --Find all rendering provider for the given Service Locations
select ProviderID
from hrp_ProviderInstance
where ParentProviderID in ( --Find ServiceLocations for a given Rendering Provider
select rprvi1.ParentProviderID
from hrp_Provider rprv1,hrp_ProviderInstance rprvi1,hrp_Provider sprv1 ,hrp_ProviderInstance sprvi1, hrp_Provider bprv1
where rprv1.Id = rprvi1.ProviderID
and rprvi1.ParentProviderID = sprv1.ID and rprv1.RenderingFlag = 'Y' and sprv1.ServiceLocFlag = 'Y'and sprv1.Retired = 'N'
and sprv1.ID = sprvi1.ProviderID and sprvi1.ParentProviderID = bprv1.ID and bprv1.BillingFlag = 'Y'
and bprv1.Retired = 'N' and bprv1.OrgType <> 'LP DIRECTLY OP' --exclude LP DO dups
and rprvi1.ProviderID = 162509
)
)
and rprv.Id = rprvi.ProviderID and rprvi.ParentProviderID = sprv.ID and rprv.RenderingFlag = 'Y'
and sprv.ServiceLocFlag = 'Y'and sprv.Retired = 'N'and sprv.ID = sprvi.ProviderID and sprvi.ParentProviderID = bprv.ID and bprv.BillingFlag = 'Y'
and bprv.Retired = 'N'and bprv.OrgType <> 'LP DIRECTLY OP' --exclude LP DO dups
and ralt.ProviderID = rprv.ID and ralt.IDQualifier = 'HPI' and ralt.AlternateID = @NPI
and (((@ActiveDate between rprvi.ActiveDate and coalesce(rprvi.ExpirationDate,'12/31/9999') and @ParentProviderID = rprvi.ParentProviderID))--active date of association
or
((coalesce(@InActiveDate,'12/31/9999') between rprvi.ActiveDate and coalesce(rprvi.ExpirationDate,'12/31/9999'))and @ParentProviderID = rprvi.ParentProviderID)
or
((@ActiveDate<rprvi.ActiveDate and @InActiveDate ='')and @ParentProviderID = rprvi.ParentProviderID)
December 14, 2011 at 11:25 am
Folks may be a little reluctant to help with this because it looks - forgive me if I'm wrong - as if someone fairly new to TSQL has gone a little too far with coding by trial and error. I'm not really sure where to start, apart from converting your query1 into something which folks are more likely to recognise and understand. Here it is:
SELECT
ParentProviderID = rprvi1.ParentProviderID,
ActiveDate = rprv1.ActiveDate,
InActiveDate = rprv1.InactiveDate
FROM hrp_Provider rprv1
INNER JOIN hrp_ProviderInstance rprvi1
ON rprvi1.ProviderID = rprv1.Id
INNER JOIN hrp_Provider sprv1
ON sprv1.ID = rprvi1.ParentProviderID
INNER JOIN hrp_ProviderInstance sprvi1
ON sprvi1.ProviderID = sprv1.ID
INNER JOIN hrp_Provider bprv1
ON bprv1.ID = sprvi1.ParentProviderID
WHERE rprv1.RenderingFlag = 'Y'
AND sprv1.ServiceLocFlag = 'Y'
AND sprv1.Retired = 'N'
AND bprv1.BillingFlag = 'Y'
AND bprv1.Retired = 'N'
AND bprv1.OrgType <> 'LP DIRECTLY OP' --exclude LP DO dups
AND rprvi1.ProviderID = 162509
All I've done is convert the table joins into ANSI-syntax, but it makes the code a lot more readable.
Does the query return what you expect it to? It's unusual having the same table joined three times (and another table joined twice) in the same query.
Why are you storing the results into variables?
Hopefully this will start the ball rolling, and you'll end up with something you can work with - picking up some knowledge on the way.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 14, 2011 at 12:48 pm
I didn't even get that far. I saw that:
* There was no sample data
* There were no expected results
* There was no definition of the "correct Active and Inactive" dates
* The query didn't use tabs for formatting
* The OP didn't use the [code] tags to preserve their non-existent formatting.
If I had gotten that far, I wouldn't have gotten past the old-fashioned implicit joins. The OP has been around long enough to know what should be included when asking for help. If the OP is too lazy to put in the work, he shouldn't be surprised when others won't do it for him.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply