T-SQL query advice

Question

T-SQL query advice

onoriega3

Old Hand

Points: 386
More actions
December 3, 2004 at 2:44 pm

#60454

Hello,
   I'm just looking for some general advice on my T-SQL query. I'm new to the game and I have no one here to seek advice on the correct way of writing queries, other than you (great site) and the web. I get overwhelmed with all the information available.
So I want to know if there is better way to write this query as far as structure or performance.  I used the "DISTINCT" to return total accession, otherwise it would return the same count as the "Test_Count". Any input would be appreciated. Thank you for your Time
Regards
SELECT qlc.Client AS QLClient, qlc.Name AS Client_Name,
  COUNT(DISTINCT(tat.Accession)) AS Accession_Count,
  COUNT(tat.OrderedUnitCode) AS Test_Count

FROM dbo.tbl_QL_Clients qlc
INNER JOIN

  (
  SELECT qla.Accession, qla.ClientMnemonic, qlat.OrderedUnitCode
  FROM dbo.tbl_QL_Accessions qla INNER JOIN dbo.tbl_QL_Accns_Tests qlat
  ON qla.Accession = qlat.Accession
  WHERE qlat.OrderedUnitCode <> '1000' AND qlat.OrderedUnitCode <> '9996'
    and OrderedDate BETWEEN '08/01/2004' AND '08/31/2004'
   ) tat
ON qlc.ClientMnemonic = tat.ClientMnemonic
GROUP BY QLClient, Name
ORDER BY QLClient

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

In general your query looks fine, although the derived table isn't needed here. May be a little simpler to read if written with straight table joins as:

SELECT qlc.Client AS QLClient,
            qlc.Name AS Client_Name,
            COUNT(DISTINCT qla.Accession) AS Accession_Count,
            COUNT(qlat.OrderedUnitCode) AS Test_Count
FROM         dbo.tbl_QL_Clients qlc
INNER JOIN dbo.tbl_QL_Accessions qla on qla.ClientMnemonic = qlc.ClientMnemonic
INNER JOIN dbo.tbl_QL_Accns_Tests qlat on qlat.Accession = qla.Accession
WHERE qlat.OrderedUnitCode <> '1000' AND qlat.OrderedUnitCode <> '9996'
    and OrderedDate BETWEEN '08/01/2004' AND '08/31/2004'
GROUP BY qlc.QLClient, qlc.Name
ORDER BY qlc.QLClient

Note that I added the table alias to the group by and order by columns because I don't know if they are repeated in the other tables.

Indexes on the join columns are generally a good thing for performance reasons. Also, a clustered index on OrderedDate may help performance because there is a nice range filter on it. Of course, this depends on the set of all queries you run off these tables.

Scott Thornburg

Volt Information Sciences