Another question I got at work, someone wanted to have all instrument sets that remain sterile for 360 days for the operation rooms and all instrument sets that remain sterile for 180 days for other departments in the hospital.

In the application we use there is a table called PSET which has all information for instrument sets, as well as other variations on sets. First I made an easy query showing me everything I can with this table. It looked as follows:

Code:


SELECT    Bezeichnung
FROM        PSET
WHERE    (STyp >= '260') AND (Verfallzeit = 360)


The Bezeichnung is the description for the set, SType is a number that shows if it is a set or something else. All sets are 260 or above. Last is the Verfallzeit, this is the number of days a set remains sterile. First we needed for 360 days, so we change it later to 180. This resulted at 22 items, all which I have no idea if they are still used and by which department.

How to get the department and see if they are still used? Well that information is located in another table called SerienNum and is linked through the Snummer of a set. First we are also going to select some more information, to make life easier for the person who wanted this. We are going to select the visible serial number which is also part of the SerienNum table and the number that stands for the department. Then we need to join both tables through the Snummer and we are going to add going to add another filter which helps me see if a set is still used. Now it looks like below.

Code:


SELECT    PSET.Bezeichnung, SerienNum.AnzSerienNum, SerienNum.ANummer
FROM        PSET INNER JOIN
                      SerienNum ON PSET.Snummer = SerienNum.SNummer
WHERE    (PSET.STyp >= '260') AND (PSET.Verfallzeit = 360) AND (SerienNum.Gesperrt = 'False')


I’ve added AnzSerienNum which is the visible number on the sets, and ANummer which is the number that represents a department. The join is done on SNummer which is a group number for the sets. I have added a filter on Gesperrt, which indicated if a set is blocked or not.
The results we have now are still hard to read, because we still don’t have any idea what department is which. We have no names but only some numbers.

The names are located in a third table, called PABT. To get these we need to do another join on ANummer. Of course we are not going to select ANummer anymore since we can now select a name.
Our query will look something like this.

Code:


SELECT    PSET.Bezeichnung, SerienNum.AnzSerienNum, PABT.Bezeichnung AS Dept
FROM        PSET INNER JOIN
                      SerienNum ON PSET.Snummer = SerienNum.SNummer INNER JOIN
                      PABT ON SerienNum.ANummer = PABT.ANummer
WHERE    (PSET.STyp >= '260') AND (PSET.Verfallzeit = 360) AND (SerienNum.Gesperrt = 'False')


Reason for having PABT.Bezeichnung AS Dept is that MS SQL doesn’t like the same column name twice.
As you can see we now have a double INNER JOIN, joining the SNummer from two tables and ANummer from two tables together.

Now we needed something similar for other departments, unfortunately there are about 50 of them, and when I select on Verfallzeit = 180 I will also get all the Operating rooms. To stop this I have added one more filter, SerienNum.ANummer BETWEEN '1.31' AND '2.00'. This way I only get departments whose number is between 1.31 and 2.00 and that rules out the Operating rooms. So in the end I have the following.

Code:


SELECT    PSET.Bezeichnung, SerienNum.AnzSerienNum, PABT.Bezeichnung AS Dept
FROM        PSET INNER JOIN
                      SerienNum ON PSET.Snummer = SerienNum.SNummer INNER JOIN
                      PABT ON SerienNum.ANummer = PABT.ANummer
WHERE    (PSET.STyp >= '260') AND (PSET.Verfallzeit = 180) AND (SerienNum.Gesperrt = 'False') AND (SerienNum.ANummer BETWEEN '1.31' AND '2.00')


If you have any questions on why or what in the above example, please reply to this post and I will try to answer as quickly and accurate as possible. Hope this helped.