I need help with querying and will do my best to explain my issue. I have the following table below of 11 rows which is created from importing values from SharePoint list.
ID SHPT_ID STATUS_DESC REC_UPDT_DTTM REC_CRTE_DTTM EXPIR_DT
1 270 Active 1-18-2019 1-19-2019 1-24-2019
2 270 In Progress 1-23-2019 1-24-2019 2-3-2019
3 270 Completed 2-2-2019 2-3-2019 2-19-2019
4 270 Completed 2-18-2019 2-19-2019 2-28-2019
5 270 In Progress 2-27-2019 2-28-2019 3-2-2019
6 270 Completed 3-1-2019 3-2-2019 3-6-2019
7 270 Completed 3-5-2019 3-6-2019 12-31-9999
8 295 Active 12-20-2018 12-21-2018 12-26-2018
9 295 Completed 12-25-2018 12-26-2018 12-31-9999
10 345 Active 6-7-2017 6-8-2017 6-14-2017
11 345 Completed 6-13-2017 6-14-2017 6-22-2017
12 345 Completed 6-21-2017 6-22-2017 12-31-9999
The last record associated to a particular SharePoint ID brings in the EXPIR_DT (Expire Date) of '12/31/9999'. Everytime a value in the SharePoint ID record is updated, a new row is created.
From this table, I am trying to pull back 3 rows in particular (rows where ID = #6, 9, and 11.)
These are the records having the minimum REC_UPDT_DTTM when STATUS_DESC equals 'Completed' for the last time. For the rows where SharePoint ID = 270, there is an instance when the record was 'Completed' but was reversed to 'In Process' and then later was put back in to 'Completed.' For this record, it should not take the row where ID =3, it should take the row where ID = 6.
Is there anyone who can help me with this code as I am stuck with how to proceed to get the rows that I want? I know I have to use subquerying and functions but I am really stuck at the moment.
Please let me know if need more info.
This query works for the dataset above. However, if a SHPT_ID could have two Completed records on the same day, this will return two rows for that SHPT_ID:
SELECT m.*
FROM MyTable m
INNER JOIN (
SELECT Min(Rec_UPDT_DTTM) MinUpdt,
Shpt_ID
FROM MyTable m1
WHERE Status_Desc = 'Completed'
AND NOT EXISTS (
SELECT *
FROM MyTable m2
WHERE m2.Shpt_ID = m1.Shpt_ID
AND m2.REC_UPDT_DTTM > m1.REC_UPDT_DTTM
AND m2.Status_Desc <> 'Completed'
)
) filter
ON filter.MinUpdt = m.REC_UPDT_DTTM
AND filter.Shpt_ID = m.Shpt_ID
To handle the case with duplicates on the same day, the code would look like this:
SELECT MyTable.*
FROM MyTable
INNER JOIN (
SELECT Shpt_ID,
MIN(ID) as ID
FROM MyTable m
INNER JOIN (
SELECT Min(Rec_UPDT_DTTM) MinUpdt,
Shpt_ID
FROM MyTable
WHERE Status_Desc = 'Completed'
AND NOT EXISTS (
SELECT *
FROM MyTable m2
WHERE m2.Shpt_ID = m1.Shpt_ID
AND m2.REC_UPDT_DTTM > m1.REC_UPDT_DTTM
AND m2.Status_Desc <> 'Completed'
)
) filter
ON filter.MinUpdt = MyTable.REC_UPDT_DTTM
AND filter.Shpt_ID = MyTable.Shpt_ID
) as IDs
ON MyTable.ID = IDs.ID