Merge the queries

Question

I have two set of queries which is shown below i need to merge the queries in terms of some circumstances which is specified below.

SELECT  M.Ward_ID ,
        M.Ward_Name ,
        M.Bed_Strength ,
        M.Bed_Strength - ISNULL(COUNT(A.WARD_id), 0) AS Free_Bed ,
        ISNULL(COUNT(A.Status), 0) AS TBV
FROM    Ward_Master M
        LEFT OUTER  JOIN IP_Admission A ON A.WARD_ID = M.WARD_ID
                                           AND ( A.Status = 'O' )
                                           OR A.Status IN ( SELECT  CONVERT(VARCHAR(1), COUNT(A.Status)) AS TBV
                                                            FROM    Ward_Master w ,
                                                                    IP_Admission a
                                                            WHERE   a.Status = 'D'
                                                                    AND a.Ward_ID = w.Ward_ID )
GROUP BY M.Ward_ID ,
        M.Bed_Strength ,
        M.Ward_Name ,
        A.Status

Output is like

       Ward_ID        Ward_Name         Bed_Strength Free_Bed   TBV
           1           Labor                  6          4       2
           2           GYN                    6          5       1
           3           OBG                    6          6       0
           4           High Ri                6          4       2
           5           PED                   14         14       0

2)

SELECT  ward_ID ,
        COUNT(status)
FROM    IP_Admission
WHERE   Status = 'D'
GROUP BY Ward_ID

output:

ward_ID (No column name)
   2    1
   3    1
   5    2

Now I need the first table to be converted as

     Ward_ID     Ward_Name   Bed_Strength    Free_Bed    TBV
       1           Labor           6           4          0
       2           GYN             6           5          1
       3           OBG             6           6          1
       4           High Ri         6           4          0
       5           PED             14          14         2

You can see that for ward ID 2,3,5 there are 1,1,2 total number of status having D. And also if any Ward_ID which doesnt contain status count it should display 0.Any help appreciated.

Clockwork-Muse · Accepted Answer

It seems clear to me from looking at your query that you don't completely understand why you're getting the results that you are; you kept on tacking on additional parts to the query, which has resulted in your current question here.

First, some notes:

GROUP BY M.Ward_ID ,
         M.Bed_Strength ,
         M.Ward_Name ,

Generally speaking, if your GROUP BY starts off with a unique column for a given table, and then lists non-key attributes for that table it's a sign you want to pre-aggregate results from other tables (subquery table references). This will (almost always) make your query consume fewer resources in two ways:

It's unlikely that an index (which are used to speed up access to data) would or should be created for the given columns you're using, which means the query is unlikely to use one.
You're doing an unnecessary GROUP BY which includes columns that should already be unique. The temporary set of results will be larger than it should be
```
     A.Status
```

You're also including a column in the GROUP BY that you don't list as a non-aggregate column. This is the equivalent to a teacher averaging the grades of his students by gender, and then not stating which average belongs to which group; it renders the data meaningless. There's almost never a reason to do this.

ISNULL(...)

.... does do what you want. However, it's preferable to use the SQL Standard COALESCE(...) function instead.

ON A.WARD_ID = M.WARD_ID
   AND ( A.Status = 'O' )
   OR A.Status IN (...)

EVERY time you mix ORs with anything else, wrap the intended compound statements in parentheses. The computer is a blind idiot, and will do whatever it thinks you told it to do. So for your own sake (and those who come after you), always explicitly clarify what you mean in these situations

CONVERT(...)

While this is likely doing what you want, it's preferable to use another SQL Standard function here, CAST(...). The equivalent statement here would be CAST(COUNT(A.Status)) as VARCHAR(1))... however:

The only advantage a VARCHAR(1) has over a CHAR(1) is that it could not store a single whitespace character.
Given the statuses you've provided are all character, I really doubt that any of the codes are numeric
You don't even have the control over the results you think you do, because...

SELECT COUNT(A.Status) FROM Ward_Master w , IP_Admission a WHERE a.Status = 'D' AND a.Ward_ID = w.Ward_ID

This statement is actually counting up all beds will Status = 'D' for every ward, with no correlation to the outside. If there's more than 9, this will be (at least) a 2-digit number, at which point one of three things happens (I can't remember which off-hand):

The server throws an error because the number's two big to fit in the destination data type.
You get the first digit (from the start of the string, not the 1's digit).
You get the last digit (of the string - in math/number terms, the first digit/1's place).

That out of the way, lets build a better query.

First, we need some information from the master Ward table:

SELECT ward_Id, ward_Name, bed_Strength
FROM Ward_Master

... we also need some aggregated data from a secondary table.
This is a problem - if we do a simple join to the table, we need to include the columns from Ward_Master in the GROUP BY, which isn't preferable (as they aren't all likely to be on the same index).
Can we aggregate the data by itself?

SELECT ward_Id, SUM(CASE WHEN status = 'O' THEN 1 ELSE 0 END) AS status_O,
                SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS status_D
FROM IP_Admission
WHERE status IN ('D', 'O')
GROUP BY ward_Id

This will total up all beds, per ward, with status O or D.
(The WHERE clause isn't strictly necessary, due to the CASE statements, but it makes the optimizer more likely to be able to use an index).
The CASE statements are being used to "split" the allowed values into two different columns. We now have a single row per ward, so now we can combine our two queries (being mindful of name collisions):

SELECT Ward_Master.ward_Id, Ward_Master.ward_Name, Ward_Master.bed_Strength,
       Ward_Master.bed_Strength - COALESCE(Bed.status_O, 0) as free_Bed,
       COALECE(Bed.status_D, 0) as TBV
FROM Ward_Master
LEFT JOIN (SELECT ward_Id, SUM(CASE WHEN status = 'O' THEN 1 ELSE 0 END) AS status_O,
                           SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS status_D
           FROM IP_Admission
           WHERE status IN ('D', 'O')
           GROUP BY ward_Id) Bed
       ON Bed.ward_Id = Ward_Master.ward_Id

(I'm no longer a fan of single-character table aliases. The names you have here aren't terribly long, so just use them. When you do need something shorter or sometimes longer, make it explanatory)
... and unless I've missed something, that's all you need.

Merge the queries

Answers (2)

Related Questions