January 23, 2013 at 8:41 am
HI,
Not sure how to go about this
I am pulling a field called servicedate connected to the date is an eligdate, there can be many eligdates.
My output looks like this
servicedate eligdate
8/6/20128/4/2012
8/6/20128/15/2012
8/6/20126/17/2011
I want only 8/6/20128/4/2012
I want to get the max of eligdate that is <= the servicedate
Thanks In Advance
Joe
January 23, 2013 at 10:20 am
Try something like this:CREATE TABLE #test (servicedate date, eligdate date)
INSERT INTO #test
SELECT '8/6/2012', '8/4/2012' U-N-I-O-N ALL
SELECT '8/6/2012', '8/15/2012' U-N-I-O-N ALL
SELECT '8/6/2012', '6/17/2011'
SELECT
MAX(servicedate),
MAX(eligdate)
FROM #test
WHERE
eligdate <= (SELECT MAX(servicedate) FROM #Test)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 23, 2013 at 11:22 am
Thanks for the response,
This may work but I don't think I explained it correctly.
Here is a snippet:
SELECT TOP (100) PERCENT dbo.Client.ID, dbo.RECORDED_SERVICE.STARTTIME, dbo.ClientPayor_Eligibility_Detail.Effective_As_Of_Date
FROM dbo.Other_To_ClientPayor_Eligibility_Detail_Collection INNER JOIN
dbo.Other ON dbo.Other_To_ClientPayor_Eligibility_Detail_Collection.OID = dbo.Other.OID RIGHT OUTER JOIN
dbo.CROSSREF INNER JOIN
dbo.RECORDED_SERVICE INNER JOIN
dbo.USI_V_Recorded_Service_To_Client ON dbo.RECORDED_SERVICE.OID = dbo.USI_V_Recorded_Service_To_Client.RECORDED_SERVICE_OID INNER JOIN
dbo.Client ON dbo.USI_V_Recorded_Service_To_Client.Client_OID = dbo.Client.OID ON
dbo.CROSSREF.OID_TO_REFERENCE = dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER INNER JOIN
dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION ON
dbo.USI_V_Recorded_Service_To_Client.Episode_OID = dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID INNER JOIN
dbo.OtherPayorLink ON dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID_LINK = dbo.OtherPayorLink.OID ON
dbo.Other.OID = dbo.OtherPayorLink.OtherPayorLink FULL OUTER JOIN
dbo.ClientPayor_Eligibility_Detail ON dbo.Other_To_ClientPayor_Eligibility_Detail_Collection.OID_LINK = dbo.ClientPayor_Eligibility_Detail.OID
WHERE (dbo.CROSSREF.Code = 'b') AND (dbo.RECORDED_SERVICE.BILLING_SIGNED_FLAG = 0) AND
(dbo.RECORDED_SERVICE.RECORDED_SERVICE_VOIDED_MONIKER IS NULL)
ORDER BY dbo.Client.ID, dbo.RECORDED_SERVICE.STARTTIME
This will display starttime and all the elig dates for the client
something like:
10012361/14/20139/30/2012
10012361/14/20135/22/2012
10012361/14/201310/2/2012
what I need is the last elig date <= Starttime(in this case, 10/2/2012)
Thanks
Joe
January 23, 2013 at 1:30 pm
It looks like you are using a query designer for this, and worryingly, it looks like the result has been achieved by accident rather than by design. Here's the full query reformatted to make it a little easier on the eye:
SELECT TOP (100) PERCENT
dbo.Client.ID,
dbo.RECORDED_SERVICE.STARTTIME,
dbo.ClientPayor_Eligibility_Detail.Effective_As_Of_Date
FROM dbo.Other_To_ClientPayor_Eligibility_Detail_Collection
INNER JOIN dbo.Other
ON dbo.Other_To_ClientPayor_Eligibility_Detail_Collection.OID = dbo.Other.OID
RIGHT OUTER JOIN dbo.CROSSREF
INNER JOIN dbo.RECORDED_SERVICE
INNER JOIN dbo.USI_V_Recorded_Service_To_Client
ON dbo.RECORDED_SERVICE.OID = dbo.USI_V_Recorded_Service_To_Client.RECORDED_SERVICE_OID
INNER JOIN dbo.Client
ON dbo.USI_V_Recorded_Service_To_Client.Client_OID = dbo.Client.OID
ON dbo.CROSSREF.OID_TO_REFERENCE = dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER
INNER JOIN dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION
ON dbo.USI_V_Recorded_Service_To_Client.Episode_OID = dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID
INNER JOIN dbo.OtherPayorLink
ON dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID_LINK = dbo.OtherPayorLink.OID
ON dbo.Other.OID = dbo.OtherPayorLink.OtherPayorLink
FULL OUTER JOIN dbo.ClientPayor_Eligibility_Detail
ON dbo.Other_To_ClientPayor_Eligibility_Detail_Collection.OID_LINK = dbo.ClientPayor_Eligibility_Detail.OID
WHERE (dbo.CROSSREF.Code = 'b')
AND (dbo.RECORDED_SERVICE.BILLING_SIGNED_FLAG = 0)
AND (dbo.RECORDED_SERVICE.RECORDED_SERVICE_VOIDED_MONIKER IS NULL)
ORDER BY dbo.Client.ID, dbo.RECORDED_SERVICE.STARTTIME
If this query were hand coded, it would be a trivial matter to wrap it up as a derived table or a CTE and process it to get the results you want. Is your query designer capable of doing this?
Here's an equivalent using table aliases, which makes the whole query much easier to read.
SELECT
TOP (100) PERCENT -- << why?
c.ID,
rs.STARTTIME,
ed.Effective_As_Of_Date
FROM dbo.Other_To_ClientPayor_Eligibility_Detail_Collection dc
INNER JOIN dbo.Other o
ON dc.OID = o.OID
RIGHT OUTER JOIN dbo.CROSSREF cr -- << why? right joins are rarely used
INNER JOIN dbo.RECORDED_SERVICE rs
INNER JOIN dbo.USI_V_Recorded_Service_To_Client v
ON rs.OID = v.RECORDED_SERVICE_OID
INNER JOIN dbo.Client c
ON v.Client_OID = c.OID
ON cr.OID_TO_REFERENCE = rs.SERVICE_ITEM_MONIKER
INNER JOIN dbo.EPISODE_TO_OTHERPAYORLINK_COLLECTION co
ON v.Episode_OID = co.OID
INNER JOIN dbo.OtherPayorLink pl
ON co.OID_LINK = pl.OID
ON o.OID = pl.OtherPayorLink
FULL OUTER JOIN ClientPayor_Eligibility_Detail ed --<< why? full outer joins are rarely used
ON dc.OID_LINK = ed.OID
WHERE (cr.Code = 'b')
AND (rs.BILLING_SIGNED_FLAG = 0)
AND (rs.RECORDED_SERVICE_VOIDED_MONIKER IS NULL)
ORDER BY c.ID, rs.STARTTIME
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply