Using Gail's script:
/*
I need to get the list of referrals, per client, in chronological order using the dates from the referralaction table.
A allocations status will be given once a referral is open.
A status of NFA or Closed means that referral is closed.
A client can have many referrals, i need to identify the current one and have the others with their open and closed dates.
I have tried pivoting the data and just cannot get it to work due to the various dates following on.
*/
-- Have a look at the data
SELECT
cd.*, '#' '#',
cr.*, '#' '#',
x.*
FROM dbo.ChildDetails cd
INNER JOIN dbo.ChildReferrals cr
ON cr.CLI_PER_ID = cd.PER_ID
CROSS APPLY (
SELECT *
FROM dbo.ChildReferralAction cra
WHERE cra.CLI_PER_ID = cr.CLI_PER_ID
AND cra.RAC_REF_ID = cr.REF_ID
) x
ORDER BY cd.PER_ID
-- possible solution
SELECT
cd.*, '#' '#',
cr.*, '#' '#',
x.*
FROM dbo.ChildDetails cd
INNER JOIN dbo.ChildReferrals cr
ON cr.CLI_PER_ID = cd.PER_ID
CROSS APPLY (
SELECT
[OpenDate] = MAX(CASE WHEN RAC_TYPE = 'ALLOCATE' THEN RAC_DATE ELSE NULL END),
[CloseDate] = MAX(CASE WHEN RAC_TYPE IN ('CLOS', 'NFA') THEN RAC_DATE ELSE NULL END)
FROM dbo.ChildReferralAction cra
WHERE cra.CLI_PER_ID = cr.CLI_PER_ID
AND cra.RAC_REF_ID = cr.REF_ID
) x
ORDER BY cd.PER_ID, x.CloseDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden