2020-06 s dispo table cleanup: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
← Older edit
m →‎Query: no more collections allowed, really
 
(10 intermediate revisions by the same user not shown)
Line 6: Line 6:
{{Collapsable  
{{Collapsable  
| always= The following query was used. It takes a good long while to run. Don't even try it without filtering for a specific unit first.
| always= The following query was used. It takes a good long while to run. Don't even try it without filtering for a specific unit first.
| full= SELECT s_dispo.location_name, s_dispo.active, s_locations_allowed_collection.Laptop_identifier, Count(pa.D_ID) AS pa, Count(p.D_ID) AS p, Count(sl.D_ID) AS sl, Count(d.D_ID) AS d, sl.RecordStatus
| full= <pre>SELECT
FROM ((((s_dispo LEFT JOIN s_locations_allowed_collection ON s_dispo.location_name = s_locations_allowed_collection.ServiceLocation) LEFT JOIN L_Log AS sl ON s_dispo.dispo_ID = sl.Service_Location) LEFT JOIN L_Log AS p ON s_dispo.dispo_ID = p.Previous_Location) LEFT JOIN L_Log AS d ON s_dispo.dispo_ID = d.Dispo) LEFT JOIN L_Log AS pa ON s_dispo.dispo_ID = pa.Pre_admit_Inpatient_Institution
    s_dispo.location_name, s_dispo.active, Count(pa.D_ID) AS pa, Count(p.D_ID) AS p, Count(sl.D_ID) AS sl, Count(d.D_ID) AS d, sl.RecordStatus
WHERE (((s_dispo.s_location)=True))
FROM
GROUP BY s_dispo.location_name, s_dispo.active, s_locations_allowed_collection.Laptop_identifier, sl.RecordStatus;
    (((s_dispo
    LEFT JOIN
        L_Log AS sl
        ON
            s_dispo.dispo_ID = sl.Service_Location)
    LEFT JOIN
        L_Log AS p
        ON
            s_dispo.dispo_ID = p.Previous_Location)
    LEFT JOIN
        L_Log AS d
        ON
            s_dispo.dispo_ID = d.Dispo)
    LEFT JOIN
        L_Log AS pa
        ON
            s_dispo.dispo_ID = pa.Pre_admit_Inpatient_Institution
WHERE
    (
        (
            (
                s_dispo.s_location
            )
            =True
        )
    )
GROUP BY
    s_dispo.location_name, s_dispo.active, sl.RecordStatus
;</pre>
}}
}}
== [[S locations allowed collection table]] entries for inactive records ==
The following were allowed but their corresponding records in [[s_dispo table]] were inactive. They have been removed from [[S locations allowed collection table]]:
*STB_EMIPb - 0 entries, deleted
*STB_E6bB - 0 entries, deleted
*STB_E5b - 0 entries, deleted


== entries with s_location = true but no entry in allowed table ==
== entries with s_location = true but no entry in allowed table ==
The following had entries in [[S dispo table]] with s_location = true, but no corresponding entry in the allowed table. This means they were not available to enter.
The following had entries in [[S dispo table]] with s_location = true, but no corresponding entry in the allowed table. This means they were not available to enter.
*STB_B5a - 0 entries, deleted
*STB_B5a - 0 entries, deleted
*STB_E5a - 1 entry, STB_E5a-880
*STB_E5a - 0 entries, deleted
{{Discuss |
* Emailed Pagasa to change STB_E5a-880 one to service location STB_E5 so we can delete the STB_E5 entry. Ttenbergen 14:22, 2020 June 5 (CDT)}}


== Records that had been added but were never used ==
== Records that had been added but were never used ==
Line 43: Line 63:
* STB_B5d
* STB_B5d
* STB_E5b
* STB_E5b
* STB_E6bB
* STB_E6dB
* STB_E6dB
* STB_EMIPb
* STB_IMCd
* STB_IMCd


== Records with s_location = true and no corresponding [[Service/Location]] entries ==
== changes in preparation for no longer using letter ending entries for workload sharing ==
The s_location field expresses that a record can be a service location.  
Set s_location to true on existing record which was present because this was already a [[Previous Location]] and [[Dispo]] entry. Added to [[S locations allowed collection table]].
{{Discuss | If a record with s_location = true is active it should have a corresponding entry in [[S locations allowed collection table]], and records with [[RecordStatus]] incomplete. What will we do with these? }}
* [[HSC_EMIP]]
* [[HSC_H4H]]


* s_location true, active, allowed, has records : keep as is
== oddballs ==
* s_location true, active, allowed, no records :
* HSC_H7 - no entries with this as [[Service/Location]] and not allowed, but has dispo entries, changed to be s_location = false
* s_location true, active, no allowed, has records
* s_location true, active, no allowed, no records :
* s_location true, not active, allowed, has records :
* s_location true, not active, allowed, no records :
* s_location true, not active, not allowed, has records :
* s_location true, not active, not allowed, no records :


== Related articles ==  
== Related articles ==  

Latest revision as of 10:52, 2021 October 14

This page describes what was done during the cleanup of the S dispo table and S locations allowed collection table started 2020-06-05.

Query

We needed to make sure that entries were never used in either Pre-admit Inpatient Institution field, Previous Location, Service/Location or Dispo.

The following query was used. It takes a good long while to run. Don't even try it without filtering for a specific unit first.    
SELECT
    s_dispo.location_name, s_dispo.active, Count(pa.D_ID) AS pa, Count(p.D_ID) AS p, Count(sl.D_ID) AS sl, Count(d.D_ID) AS d, sl.RecordStatus
FROM
    (((s_dispo
    LEFT JOIN
        L_Log AS sl
        ON
            s_dispo.dispo_ID = sl.Service_Location)
    LEFT JOIN
        L_Log AS p
        ON
            s_dispo.dispo_ID = p.Previous_Location)
    LEFT JOIN
        L_Log AS d
        ON
            s_dispo.dispo_ID = d.Dispo)
    LEFT JOIN
        L_Log AS pa
        ON
            s_dispo.dispo_ID = pa.Pre_admit_Inpatient_Institution
WHERE
    (
        (
            (
                s_dispo.s_location
            )
            =True
        )
    )
GROUP BY
    s_dispo.location_name, s_dispo.active, sl.RecordStatus
;

entries with s_location = true but no entry in allowed table

The following had entries in S dispo table with s_location = true, but no corresponding entry in the allowed table. This means they were not available to enter.

  • STB_B5a - 0 entries, deleted
  • STB_E5a - 0 entries, deleted

Records that had been added but were never used

Some of these were added during covid move confusion.

The following records had no corresponding entries at all. They were entirely deleted from the table:

  • HSC_H7_MI
  • HSC_H7_SI
  • HSC_A7_MI
  • HSC_A7_SI
  • HSC_B2_C
  • HSC_MS3_C

older, not part of covid move confusion

  • HSC_OVER
  • STB_A5

added for workload sharing at some point, but not used, and no longer needed now:

  • STB_B5d
  • STB_E5b
  • STB_E6bB
  • STB_E6dB
  • STB_EMIPb
  • STB_IMCd

changes in preparation for no longer using letter ending entries for workload sharing

Set s_location to true on existing record which was present because this was already a Previous Location and Dispo entry. Added to S locations allowed collection table.

oddballs

  • HSC_H7 - no entries with this as Service/Location and not allowed, but has dispo entries, changed to be s_location = false

Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=2020-06_s_dispo_table_cleanup&oldid=152361"