Code Check & Select Values From a 'Max' Record 1

[Auguy]

SQL 2005. The Unit table contains vehicle entries like cars & trucks. The downtime table has the matching entries for the units being out of service for each unit. I'm trying to get the total count of ALL downtime entires for the unit (BB.CntDate) and the most recent downtime entry (AA.MaxDate) in a given date range. I just used '20070101' as an example.

Two questions.
1. Is there a better way to write this Select statement?
2. What if I now want the values from the matching 'Max' record? Not quite sure how to approach this one.

SELECT U.UnitPK, U.UnitNo, U.UnitYear, AA.MaxDate, BB.CntDate
FROM Unit as U
Left OUTER JOIN (SELECT Unitfk, MAX(DateDown) as MaxDate
	FROM DownTime
	WHERE UnitFk = 1050 and DateDown < '20070101'
	GROUP BY Unitfk) as AA on U.UnitPK = AA.Unitfk
Left OUTER JOIN (SELECT Unitfk, COUNT(DateDown) as CntDate
	FROM DownTime
	WHERE UnitFk = 1050
	GROUP BY Unitfk) as BB on U.UnitPK = BB.Unitfk
WHERE U.UnitPK = 1050
GROUP BY U.UnitPK, U.UnitNo, U.UnitYear, AA.MaxDate, BB.CntDate
ORDER BY U.UnitPK, U.UnitNo

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

The query you have isn't necessarily bad, but there is room for improvement.

You are using 2 derived tables. One to get the max, and the other to get the count. Looking at the queries, they are very similar. The only difference I see is that one has an additional where clause on DateDown. If you combine these queries in to one, you will improve performance.

To combine the queries...

SELECT  Unitfk, 
        COUNT(DateDown) as CntDate[!], 
        Max(Case When DateDown < '20070101' Then DateDown End) As MaxDate[/!]
FROM    DownTime
WHERE   UnitFk = 1050
GROUP BY Unitfk

Next, I see that you have a group by on the outer query. This is not necessary because you are aggregating anything in the outer query. If you remove the group by, you will get the same results, but SQL will do less work to obtain them, and will therefore perform faster.

Technically speaking, the queries would not be the same if you removed the group by. However, I see that the derived tables are grouping on UnitFK so there cannot be multiples there, and UnitPK appears to be the primary key of the Unit table, so there cannot be duplicates there.

When you use a group by clause without aggregates, it is functionally equivalent to DISTINCT. Since your query prevents duplicates, you don't need to use a group by or a DISTINCT.

Next, I would suggest that you remove the Where clause criteria from the derived table. Since you are using the UnitFK column to join to the Unit table, and you have a WHERE clause on the outer table, you don't need it on the derived table. This is a minor point, however, if you decide to make this a stored procedure, or the basis for other code, there would only be one place where you have the filter condition instead of multiple places.

Next, I would suggest that you give some thought about the Left join. Inner joins perform better than left joins. If you don't need the left join, then make it an inner join. For example, if your design is such that there will always be at least one row in the DownTime table for each Unit, then you don't need a left join. Or, perhaps you are writing a query for a report and you only want to show vehicles with down time, again, you wouldn't need the left join. If it turns out that you do need the left join, then keep it a left join. I only mention this because some times developers have a habit of making everything a left join even when it doesn't need to be.

I would re-write the query like this:

SELECT U.UnitPK, U.UnitNo, U.UnitYear, AA.MaxDate, AA.CntDate
FROM   Unit as U
       Left OUTER JOIN (
         SELECT Unitfk, 
                COUNT(DateDown) as CntDate,
                MAX(Case When DateDown < '20070101' Then DateDown End) as MaxDate
         FROM   DownTime
         GROUP BY Unitfk) as AA 
           on U.UnitPK = AA.Unitfk
WHERE U.UnitPK = 1050
ORDER BY U.UnitPK, U.UnitNo

This query should return the same data as your original, but it should be slightly faster.

2. What if I now want the values from the matching 'Max' record? Not quite sure how to approach this one.

In this case, you would take the query above and join it back to the downtime table, like this:

SELECT U.UnitPK, U.UnitNo, U.UnitYear, AA.MaxDate, AA.CntDate,
       DateDown.*
FROM   Unit as U
       Left OUTER JOIN (
         SELECT Unitfk, 
                COUNT(DateDown) as CntDate,
                MAX(Case When DateDown < '20070101' Then DateDown End) as MaxDate
         FROM   DownTime
         GROUP BY Unitfk) as AA 
           on U.UnitPK = AA.Unitfk
       Left Join DownTime
           On AA.UnitFK = DownTime.UnitFK
           And AA.MaxDate = DownTime.DateDown
WHERE U.UnitPK = 1050
ORDER BY U.UnitPK, U.UnitNo

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Excellent, thanks George. That was sooo much more than I expected. I enjoy reading all of your posts in these forums.

Auguy
Sylvania/Toledo Ohio