I also posted this on StackOverflow...I could use your help!
Background: I am using a translated MySQL to MS SQL Server RDBMS. The system the data is entered is a rapid development environment and has a more hierarchical structure which is why I join on tables "links" and "documents" instead of directly between the tables I am actually using for the columns. The only other table I use is a reference table that contains dates in order to check which fiscal year and quarter clients were served first.
Additionally, the way clients are managed is that they can receive services from many programs. There are two types of programs, shelter and mobile community based outreach. Checking for services for these two types of programs are slightly different. Shelter only requires a program enrollment because a client takes a bed the moment they are enrolled until the time they are exited. Outreach needs a service record (which comes after a program enrollment) in order to count the client as served.
Objective: My goal with this code is to ensure I am grabbing the first service date and program per fiscal year (which is based on a 10/1 - 9/30 range). Once 10/1 hits, all clients are treated as "new" and I will explain this more below. If a client is served by both shelter and Outreach, then shelter takes precedent.
10/1 FY Reset: To explain what I mean by clients are treated as "new" when the fiscal year restarts on 10/1 and what I mean by program precedence, the best way I can, is with examples.
- If a client had a shelter enrollment or service prior to 10/1 (and that was their first service that fiscal year) and are still enrolled or received another service past 10/1 the following fiscal year (meaning no end date yet), then they should be counted as served in both fiscal years as well as the quarter they were originally enrolled and Q1 of the new fiscal year (2 rows).
- If a client had a shelter enrollment or service prior to 10/1 and exited or received another service prior to 10/1 then they are only counted as served once within that fiscal year in the quarter they were opened/received first service, unless and until they are enrolled again/served again and it spans past 10/1.
- If a client had a shelter enrollment or service prior to 10/1 and exited/received another service after 10/1 then they are again counted as served in both fiscal years and in Q1 of the new fiscal year as well as the quarter they were originally enrolled/served.
- If a client had a shelter enrollment or service after 10/1 and either exited or is still enrolled or did not receive another service yet or received a service prior to 10/1, then they are counted that fiscal year and quarter (this is similar to the second example).
Example Scenarios and Ideal Expected Outputs (my code gets close but not exactly what is needed):
EXAMPLE 1: A client was enrolled in Program2 on August 25th, 2020 and exited on October 31st, 2020.
| Client_ProfileID | fiscal_year | Quarter | Program_Name | first_service | ServiceStartDate | ServiceEndDate | T2_RecordID |
|---|---|---|---|---|---|---|---|
| 1 | 2020 | Q4 | Program 2 | 2020-08-25 | 2020-08-25 | 2020-10-31 | pe1 |
| 1 | 2021 | Q1 | Program 2 | 2020-10-01 | 2020-08-25 | 2020-10-31 | pe1 |
EXAMPLE 2: A client was enrolled in Program1 on April 1st, 2021 and exited on July 13th, 2021. They were also served by Program5 on April 12th, 2022, twice.
| Client_ProfileID | fiscal_year | Quarter | Program_Name | first_service | ServiceStartDate | ServiceEndDate | T2_RecordID |
|---|---|---|---|---|---|---|---|
| 2 | 2021 | Q3 | Program 1 | 2021-04-01 | 2021-04-01 | 2021-07-13 | pe2 |
| 2 | 2022 | Q3 | Program 5 | 2022-04-12 | 2022-02-07 | 2022-04-12 | svcs1 |
EXAMPLE 3: A client was enrolled in Program1 on July 20th, 2021 and exited on July 28th, 2021. They were also served by Program5 20 times in 2023. 7 times on May 5th, 1 time on May 9th, 1 time on May 10th, 1 time on May 17th, 1 time on May 23rd, 1 time on June 6th, 1 time on July 11th, 1 time on July 14th, 2 times on July 24th, 1 time on August 28th, 1 time on September 27th, 1 time on October 25th, and 1 time on November 15th.
| Client_ProfileID | fiscal_year | Quarter | Program_Name | first_service | ServiceStartDate | ServiceEndDate | T2_RecordID |
|---|---|---|---|---|---|---|---|
| 3 | 2021 | Q4 | Program 1 | 2021-07-20 | 2021-07-10 | 2021-07-28 | pe3 |
| 3 | 2023 | Q3 | Program 5 | 2023-05-05 | 2023-05-05 | 2023-11-15 | svcs2 |
| 3 | 2023 | Q1 | Program 5 | 2023-10-25 | 2023-05-05 | 2023-11-15 | svcs3 |
EXAMPLE 4: This example gives some look at precedence. A client was enrolled in Program3 on August 10th, 2023 and exited September 5th, 2023. They were enrolled in Program5 on August 28th, 2023 when they started receiving services on August 28th, 2023. This enrollment had 4 services, 1 on August 28th and 3 on August 29th. Eventually they ended their time with Program5 on August 29th but continued to receive 1 more service on October 10th, 2023. They were then enrolled in Program1 on September 5th, 2023 and exited October 17th, 2023. They then were enrolled in Program2 on October 17th, 2023 and exited November 6th, 2023. Lastly they were enrolled in Program5 on November 1st, 2023 and received services starting November 1st (2).
| Client_ProfileID | fiscal_year | Quarter | Program_Name | first_service | ServiceStartDate | ServiceEndDate | T2_RecordID |
|---|---|---|---|---|---|---|---|
| 4 | 2023 | Q4 | Program 3 | 2023-08-10 | 2023-08-10 | 2023-09-05 | pe4 |
| 4 | 2024 | Q1 | Program 1 | 2023-10-01 | 2023-09-05 | 2023-10-17 | pe5 |
EXAMPLE 5: A client was enrolled in Program5 on September 6th, 2023 and has not enrolled in a shelter yet nor stop receiving services from Program5 yet. This client received 24 services thus far. First one was on September 6th, 2023 and the first one received after 10/1 was on October 2nd, 2023 (5 services on this date).
| Client_ProfileID | fiscal_year | Quarter | Program_Name | first_service | ServiceStartDate | ServiceEndDate | T2_RecordID |
|---|---|---|---|---|---|---|---|
| 5 | 2023 | Q4 | Program 5 | 2023-09-06 | 2023-09-06 | NULL | svcs4 |
| 5 | 2024 | Q1 | Program 5 | 2023-10-02 | 2023-09-06 | NULL | svcs5 |
Current Version of Code:
Currently, my main issues are with the shelter cte as my code is not able to handle when a client is still enrolled in shelter past 10/1 (meaning NULL ServiceEndDates) to mark them as served on 10/1. It is also not able to get the correct first_service dates and I am not confident my Ref_Dates table is joined the way I need it to to make sure I am getting the right fiscal year and quarter (right now it only gives the min of pe.ServiceStartDate as first_service and the FY and Q were based on the ServiceStart/EndDates). Outside of the shelter cte, I am not sure the precedence portion of my code is working as I think it does (I am referring to the RankedData cte), but so far I think it is handling things properly. Any help you can provide is very appreciated!
WITH
cte_shelter_clients AS ( --Grab all dv shelter clients served and their first time being served in ea program.
SELECT DISTINCT cp.document_id AS Client_ProfileID
,ref.Fiscal_Year
,ref.[Quarter]
,CASE
WHEN pe.Program IN ('Program1') THEN 'Program 1'
WHEN pe.Program IN ('Program2') AND pe.ProjectType NOT IN ('Transitional Housing') THEN 'Program 2'
WHEN pe.Program IN ('Program2') AND pe.ProjectType IN ('Transitional Housing') THEN 'Program 2 Transitional'
WHEN pe.Program IN ('Program3') THEN 'Program 3'
WHEN pe.Program IN ('Program4') THEN 'Program 4'
END AS [Program_Name]
,MIN(CAST(pe.ServiceStartDate AS DATE)) AS first_service
,CAST(pe.ServiceStartDate AS DATE) AS ServiceStartDate
,CASE
WHEN pe.ServiceEndDate IS NULL THEN
CASE
WHEN (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) THEN
CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE)
ELSE
CAST(GETDATE() AS DATE)
END
ELSE
CAST(pe.ServiceEndDate AS DATE)
END AS ServiceEndDate
,pe.RecordID AS T2_RecordID
FROM Client_Profile AS cp
JOIN documents docs ON docs.ParentId=cp.document_id
JOIN ProgramEnrollment pe ON pe.RecordID=docs.RecordId
JOIN Ref_Dates ref ON (
(pe.ServiceStartDate <= ref.State_QEnd AND COALESCE(
CASE
WHEN pe.ServiceEndDate IS NULL AND (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) THEN CAST(GETDATE() AS DATE)
ELSE CAST(pe.ServiceEndDate AS DATE)
END,
GETDATE()
) >= ref.State_QStart)
OR (pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceStartDate >= ref.State_QStart AND (COALESCE(
CASE
WHEN pe.ServiceEndDate IS NULL AND (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) THEN CAST(GETDATE() AS DATE)
ELSE CAST(pe.ServiceEndDate AS DATE)
END,
GETDATE()
) IS NULL OR COALESCE(
CASE
WHEN pe.ServiceEndDate IS NULL AND (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) THEN CAST(GETDATE() AS DATE)
ELSE CAST(pe.ServiceEndDate AS DATE)
END,
GETDATE()
) = ''))
)
--JOIN Ref_Dates ref ON ((pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceEndDate >= ref.State_QStart) OR (pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceStartDate >=ref.State_QStart AND (pe.ServiceEndDate IS NULL OR pe.ServiceEndDate = ''))) --Funding entity uses fiscal year starting 10/1, we can later group clients based on which quarter they were served. This was the old way I was joining which is correct, however, doesn't achieve what I need for clients still enrolled (no Program End Date yet)
WHERE cp.last_name NOT LIKE 'Test' --don't include test clients from database
AND pe.Program IN ('Program 1', 'Program 2', 'Program 2 Transitional', 'Program 3', 'Program 4')
GROUP BY cp.document_id
,ref.Fiscal_Year
,ref.[Quarter]
,pe.Program
,CASE
WHEN pe.Program IN ('Program1') THEN 'Program 1'
WHEN pe.Program IN ('Program2') AND pe.ProjectType NOT IN ('Transitional Housing') THEN 'Program 2'
WHEN pe.Program IN ('Program2') AND pe.ProjectType IN ('Transitional Housing') THEN 'Program 2 Transitional'
WHEN pe.Program IN ('Program3') THEN 'Program 3'
WHEN pe.Program IN ('Program4') THEN 'Program 4'
END
,pe.ServiceStartDate
,CASE
WHEN pe.ServiceEndDate IS NULL THEN
CASE
WHEN (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) THEN
CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE)
ELSE
CAST(GETDATE() AS DATE)
END
ELSE
CAST(pe.ServiceEndDate AS DATE)
END
,pe.ServiceEndDate
,pe.RecordID
),
cte_mcba_clients AS ( --Grab all Program 5 clients served and their first time being served in the program.
SELECT DISTINCT cp.document_id AS Client_ProfileID
,ref.Fiscal_Year
,ref.[Quarter]
,CASE
WHEN svcs.ProgramEnrollment LIKE 'Program5%' THEN 'Program 5' --the ProgramEnrollment field contains a concatenated string of program name and program start date which is why I am checking the name sans the rest of the string
END AS [Program_Name]
,MIN(CAST(svcs.DateofService AS DATE)) AS first_service
,CAST(pe.ServiceStartDate AS DATE) AS ServiceStartDate
,CAST(pe.ServiceEndDate AS DATE) AS ServiceEndDate
,svcs.RecordID AS T2_RecordID
FROM Client_Profile AS cp
JOIN documents docs ON docs.ParentId=cp.document_id
JOIN ProgramEnrollment pe ON pe.RecordID=docs.RecordId
JOIN Links link ON link.LinkToId=docs.RecordId
JOIN [Services] svcs ON svcs.RecordID=LinkFromId
JOIN Ref_Dates ref ON (svcs.DateofService <= ref.State_QEnd AND svcs.DateofService >= ref.State_QStart) --Funding entity uses fiscal year starting 10/1, we can later group clients based on which quarter they were served
WHERE cp.last_name NOT LIKE 'Test'
--AND pe.Program IN ('Program5')
AND svcs.ProgramEnrollment LIKE 'Program5%'
GROUP BY cp.document_id
,ref.Fiscal_Year
,ref.[Quarter]
,CASE
WHEN svcs.ProgramEnrollment LIKE 'Program5%' THEN 'Program 5'
END
,pe.ServiceStartDate
,pe.ServiceEndDate
,svcs.RecordID
),
cte_agg_clients AS (
SELECT *
FROM cte_shelter_clients cs
UNION ALL
SELECT *
FROM cte_mcba_clients mcba
),
RankedData AS (
SELECT agg.Client_ProfileID
,agg.Fiscal_Year
,agg.[Quarter]
,agg.[Program_Name]
,agg.first_service
,agg.ServiceStartDate
,agg.ServiceEndDate
,agg.T2_RecordID
,ROW_NUMBER() OVER (
PARTITION BY agg.Client_ProfileID, agg.Fiscal_Year--, agg.[Quarter]
ORDER BY
CASE
WHEN agg.[Program_Name] = ('Program 5') THEN 1 --Excludes MCBA if serviced by Shelter
ELSE 0 -- Includes MCBA with lower priority, assigning other programs higher priority
END
,agg.first_service
) AS RowNum
FROM cte_agg_clients agg
)
SELECT rd.Client_ProfileID
,rd.Fiscal_Year
,rd.[Quarter]
,rd.[Program_Name]
,rd.first_service
,rd.ServiceStartDate
,rd.ServiceEndDate
,rd.T2_RecordID
FROM RankedData rd
WHERE RowNum = 1
GO
Hopefully the below gives more context: