Repository: data-to-insight/csc-validator-be-903 (language: python)

Issue:
Error code SW11bSTG2 [Added 2023 v1.2]
**Note: This rule was added by DFE in the 2023/24 v1.2 validation check document. It replaces SW11STG2 (#679)**

## Error information
#### Error code
SW11bSTG2
#### Description to display
There should be a social worker episode open on 31 March. Social worker episode has ended before 31 March and no subsequent social worker episode has been provided.
#### DfE Long-form description of error
The social worker episode has ended before 31 March and no following social worker episode has been provided.
All social worker episodes should be provided for the time period 1 April to 31 March for a child looked after on 31 March, including periods when the child was no longer looked after if they still had a social worker,
Missing social worker episodes should be added.
If there is no social worker on 31 March then please request a bypass and provide a reason.
#### DfE suggested coding
If `<SW_DEC>` >= (`<COLLECTION_END_DATE>` - 5 days)
then next social worker episode `<SW_DECOM>` must be present

For further info, refer to the DfE validation document [here](https://www.gov.uk/government/publications/children-looked-after-return-2023-to-2024-technical-specifications) 


Discussion / hints:

Fix patch:
diff --git a/lac_validator/rules/lac2023_24/rule_SW11STG2.py b/lac_validator/rules/lac2023_24/rule_SW11aSTG2.py
similarity index 58%
rename from lac_validator/rules/lac2023_24/rule_SW11STG2.py
rename to lac_validator/rules/lac2023_24/rule_SW11aSTG2.py
index 2ab2dcbf..046b58ca 100644
--- a/lac_validator/rules/lac2023_24/rule_SW11STG2.py
+++ b/lac_validator/rules/lac2023_24/rule_SW11aSTG2.py
@@ -4,7 +4,7 @@
 
 
 @rule_definition(
-    code="SW11STG2",
+    code="SW11aSTG2",
     message="Social worker episode has ended before 31 March and no subsequent social worker episode has been provided.",
     affected_fields=["SW_DEC"],
     tables=["SWEpisodes"],
@@ -37,11 +37,17 @@ def validate(dfs):
             suffixes=("", "_prev"),
         )
 
-        before_end = m_df[m_df["SW_DEC_prev"] < collection_end]
+        before_end = m_df[m_df["SW_DEC_prev"] <= collection_end - pd.DateOffset(days=5)]
+        decom_within_timeframe = (
+            before_end["SW_DECOM"] >= before_end["SW_DEC_prev"] - pd.DateOffset(days=5)
+        ) & (
+            before_end["SW_DECOM"] <= before_end["SW_DEC_prev"] + pd.DateOffset(days=5)
+        )
+
+        same_child = before_end["CHILD"] == before_end["CHILD_prev"]
 
         error_rows = before_end[
-            before_end["SW_DECOM"].isna()
-            | (before_end["SW_DECOM"] != before_end["SW_DEC_prev"])
+            (before_end["SW_DECOM"].isna() | ~decom_within_timeframe) & same_child
         ]["index"]
 
         return {"SWEpisodes": error_rows.tolist()}
@@ -52,14 +58,33 @@ def test_validate():
 
     fake_data = pd.DataFrame(
         [
-            {"CHILD": "1", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
+            {"CHILD": "1", "SW_DECOM": "01/04/2000", "SW_DEC": "25/03/2000"},
             {"CHILD": "1", "SW_DECOM": pd.NA, "SW_DEC": pd.NA},
-            {"CHILD": "2", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
+            {"CHILD": "2", "SW_DECOM": "01/04/2000", "SW_DEC": "25/03/2000"},
             {"CHILD": "2", "SW_DECOM": "02/04/2000", "SW_DEC": "03/04/2000"},
             {"CHILD": "3", "SW_DECOM": "01/04/2000", "SW_DEC": "1/04/2000"},
             {"CHILD": "3", "SW_DECOM": "02/04/2000", "SW_DEC": "03/04/2000"},
             {"CHILD": "4", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
             {"CHILD": "4", "SW_DECOM": "30/03/2000", "SW_DEC": "03/04/2000"},
+            {"CHILD": "5", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
+            {
+                "CHILD": "5",
+                "SW_DECOM": pd.NA,
+                "SW_DEC": pd.NA,
+            },  # passes, SW_DEC later than end - 5
+            {"CHILD": "6", "SW_DECOM": "01/04/2000", "SW_DEC": "25/03/2000"},
+            {
+                "CHILD": "6",
+                "SW_DECOM": "29/03/2000",
+                "SW_DEC": "03/04/2000",
+            },  # passes, SW_DECOM within timeframe
+            {"CHILD": "7", "SW_DECOM": "01/04/2000", "SW_DEC": "20/03/2000"},
+            {"CHILD": "7", "SW_DECOM": "21/03/2000", "SW_DEC": "22/03/2000"},
+            {
+                "CHILD": "7",
+                "SW_DECOM": "29/03/2000",
+                "SW_DEC": "03/04/2000",
+            },  # Fails, not within timeframe, child 7 checks it works for children with multiple rows
         ]
     )
 
@@ -69,4 +94,4 @@ def test_validate():
 
     result = validate(fake_dfs)
 
-    assert result == {"SWEpisodes": [1, 3]}
+    assert result == {"SWEpisodes": [1, 3, 14]}
diff --git a/lac_validator/rules/lac2023_24/rule_SW11bSTG2.py b/lac_validator/rules/lac2023_24/rule_SW11bSTG2.py
new file mode 100644
index 00000000..84f6d2cd
--- /dev/null
+++ b/lac_validator/rules/lac2023_24/rule_SW11bSTG2.py
@@ -0,0 +1,145 @@
+import pandas as pd
+
+from lac_validator.rule_engine import rule_definition
+
+
+@rule_definition(
+    code="SW11bSTG2",
+    message="Social worker episode has ended before 31 March and no subsequent social worker episode has been provided.",
+    affected_fields=["SW_DEC"],
+    tables=["SWEpisodes"],
+)
+def validate(dfs):
+    if "SWEpisodes" not in dfs:
+        return {}
+    else:
+        df = dfs["SWEpisodes"]
+
+        df["SW_DEC"] = pd.to_datetime(df["SW_DEC"], format="%d/%m/%Y", errors="coerce")
+        df["SW_DECOM"] = pd.to_datetime(
+            df["SW_DECOM"], format="%d/%m/%Y", errors="coerce"
+        )
+
+        collection_end = dfs["metadata"]["collection_end"]
+        collection_end = pd.to_datetime(collection_end, format="%d/%m/%Y")
+
+        df["index"] = df.index
+
+        # Children with an NA following a SW_DEC
+        df = df.sort_values(["CHILD", "SW_DECOM"])
+
+        df_lead = df.shift(1)
+
+        df_lead = df_lead.reset_index()
+
+        m_df = df.merge(
+            df_lead,
+            left_on=["index", "CHILD"],
+            right_on=["level_0", "CHILD"],
+            how="outer",
+            suffixes=("", "_prev"),
+        )
+
+        m_df.dropna(subset="index", axis="rows", inplace=True)
+        m_df["index"] = m_df["index"].astype("int")
+        before_end = m_df[
+            (m_df["SW_DEC_prev"] >= collection_end - pd.DateOffset(days=5))
+            & (m_df["SW_DEC_prev"] <= collection_end)
+        ]
+
+        error_rows = before_end[before_end["SW_DECOM"].isna()]["index"]
+        error_rows = error_rows.to_list()
+
+        # need children who appear once in SW DEC after timeframe
+        appearance_counts = df.value_counts("CHILD").reset_index()
+        one_appearance = appearance_counts[appearance_counts[0] == 1]["CHILD"].tolist()
+        children_appearing_once = df[
+            df["CHILD"].isin(one_appearance)
+            & (
+                (df["SW_DEC"] >= collection_end - pd.DateOffset(days=5))
+                & (df["SW_DEC"] <= collection_end)
+            )
+        ]["index"].tolist()
+
+        # children who have multiple SW_DEC in the time period and no following SW_DECOM
+        # sorting in reverse lets us find rows where there's multiple filled DEC/DECOM
+        # in the timeframe with no DECOM following the last DEC by putting the
+        df = df.sort_values(["CHILD", "SW_DECOM"], ascending=False)
+
+        df_reverse_lead = df.shift(1)
+
+        df_reverse_lead = df_reverse_lead.reset_index()
+
+        m_reverse_df = df.merge(
+            df_reverse_lead,
+            left_on=["index", "CHILD"],
+            right_on=["level_0", "CHILD"],
+            how="outer",
+            suffixes=("", "_prev"),
+        )
+        no_following_decom = m_reverse_df[
+            (m_reverse_df["SW_DECOM"] < m_reverse_df["SW_DEC_prev"])
+            & (
+                (m_reverse_df["SW_DEC_prev"] >= collection_end - pd.DateOffset(days=5))
+                & (m_reverse_df["SW_DEC_prev"] <= collection_end)
+            )
+        ]
+
+        multiple_dec_in_timeframe = (
+            no_following_decom["index_prev"].astype(int).to_list()
+        )
+
+        error_rows.extend(children_appearing_once)
+        error_rows.extend(multiple_dec_in_timeframe)
+        return {"SWEpisodes": error_rows}
+
+
+def test_validate():
+    import pandas as pd
+
+    fake_data = pd.DataFrame(
+        [
+            {"CHILD": "1", "SW_DECOM": "01/04/2000", "SW_DEC": "26/03/2000"},
+            {
+                "CHILD": "1",
+                "SW_DECOM": "01/05/2000",
+                "SW_DEC": pd.NA,
+            },  # Passes, rule doesn't suggest a timeframe needed for SW_DECOM
+            {"CHILD": "2", "SW_DECOM": "01/04/2000", "SW_DEC": "25/03/2000"},
+            {"CHILD": "2", "SW_DECOM": "02/04/2000", "SW_DEC": "03/04/2000"},
+            {"CHILD": "3", "SW_DECOM": "01/04/2000", "SW_DEC": "1/04/2000"},
+            {"CHILD": "3", "SW_DECOM": "02/04/2000", "SW_DEC": "03/04/2000"},
+            {"CHILD": "4", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
+            {"CHILD": "4", "SW_DECOM": "30/03/2000", "SW_DEC": "03/04/2000"},
+            {"CHILD": "5", "SW_DECOM": "01/04/2000", "SW_DEC": "30/03/2000"},
+            {"CHILD": "6", "SW_DECOM": "01/04/2000", "SW_DEC": "26/03/2000"},
+            {"CHILD": "6", "SW_DECOM": pd.NA, "SW_DEC": pd.NA},  # Fails, no SW_DECOM
+            {"CHILD": "7", "SW_DECOM": "01/04/2000", "SW_DEC": "25/03/2000"},
+            {
+                "CHILD": "7",
+                "SW_DECOM": pd.NA,
+                "SW_DEC": pd.NA,
+            },  # Passes, no SW_DEC outside of timeframe
+            {"CHILD": "8", "SW_DECOM": "01/04/2000", "SW_DEC": "26/03/2000"},
+            {"CHILD": "8", "SW_DECOM": "27/03/2000", "SW_DEC": "28/03/2000"},
+            {
+                "CHILD": "8",
+                "SW_DECOM": pd.NA,
+                "SW_DEC": pd.NA,
+            },  # Fails, no SW_DECOM after DEC in timeframe
+            {"CHILD": "9", "SW_DECOM": "01/03/2000", "SW_DEC": "25/03/2000"},
+            {
+                "CHILD": "9",
+                "SW_DECOM": "26/03/2000",
+                "SW_DEC": "27/03/2000",
+            },  # Should fail, SW_DEC in timeframe no following DECOM
+        ]
+    )
+
+    fake_meta = {"collection_end": "31/3/2000"}
+
+    fake_dfs = {"SWEpisodes": fake_data, "metadata": fake_meta}
+
+    result = validate(fake_dfs)
+
+    assert result == {"SWEpisodes": [10, 15, 8, 17]}


Test patch:
diff --git a/tests/test_utils.py b/tests/test_utils.py
index 86e4ef02..fceaf84a 100644
--- a/tests/test_utils.py
+++ b/tests/test_utils.py
@@ -12,7 +12,7 @@
 def test_get_year_ruleset():
     registry = get_year_ruleset("2024")
 
-    assert len(registry) == 311
+    assert len(registry) == 312
 
     assert isinstance(registry, dict)
     assert isinstance(list(registry.values())[1], RuleDefinition)