Best Practice to understand what a query is doing

Question

Post reply

Best Practice to understand what a query is doing

Data Rat

SSC Eights!

Points: 943
More actions
October 24, 2019 at 6:59 pm
Go to Answer
#3691739

Hi Friends,
I am relatively new to SQL development and I have this query with no comments in it and I am looking at it and wondering WTF. But I was wondering if I could get some ideas concerning how others best understand what a query is doing. Thanks !
SELECT dbo.tblRequest2.DateOrder, dbo.tblRequest2.DateClosed, dbo.tblRequest2.DateCancelled, dbo.tblRequest2.DateCompleted, dbo.tblDivision.Division, dbo.tblRequest2.CostCenter, dbo.tblRequest2.WorkOrderID, dbo.tblRequest2.Topic, dbo.tblRequest2.Status, dbo.qryWorkHoursForWeek.Employee,dbo.qryWorkHoursForWeek.WeekOf,dbo.qryWorkHoursForWeek.HoursForWeek ,dbo.tblRequest2.PriorityNumber, dbo.tblRequest2.Description, UserName AS Requestor
FROM dbo.tblCostCenter INNER JOIN
dbo.tblDivision ON dbo.tblCostCenter.DivisionID = dbo.tblDivision.DivisionID
RIGHT OUTER JOIN
dbo.tblRequest2 ON dbo.tblCostCenter.CostCenterName = dbo.tblRequest2.CostCenter
LEFT OUTER JOIN dbo.qryWorkHoursForWeek ON dbo.tblRequest2.WorkOrderID = dbo.qryWorkHoursForWeek.WorkOrderID
WHERE (dbo.tblRequest2.DateCancelled BETWEEN @FromDate AND @ToDate)
AND (dbo.qryWorkHoursForWeek.WeekOf BETWEEN @FromDate AND @ToDate) OR
(dbo.tblRequest2.DateCompleted BETWEEN @FromDate AND @ToDate)
AND (dbo.qryWorkHoursForWeek.WeekOf BETWEEN @FromDate AND @ToDate) OR
(dbo.tblRequest2.DateOrder <= @ToDate) AND (dbo.tblRequest2.DateCancelled IS NULL) AND
(dbo.tblRequest2.DateCompleted IS NULL) AND (dbo.qryWorkHoursForWeek.WeekOf BETWEEN @FromDate AND
@ToDate) AND (dbo.tblRequest2.DateClosed IS NULL) OR
(dbo.tblRequest2.DateOrder <= @ToDate) AND (dbo.tblRequest2.DateCancelled IS NULL) AND
(dbo.tblRequest2.DateCompleted IS NULL) AND (dbo.qryWorkHoursForWeek.WeekOf IS NULL) AND (dbo.tblRequest2.DateClosed IS NULL)

Phil Parkin

SSC Guru

Points: 246987

October 24, 2019 at 7:46 pm

Answer

#3691756

Step 1. Use table aliases and consistent query formatting. This is the same query, but notice how much easier it is to begin to see what is happening.

SELECT r2.DateOrder
      ,r2.DateClosed
      ,r2.DateCancelled
      ,r2.DateCompleted
      ,div.Division
      ,r2.CostCenter
      ,r2.WorkOrderID
      ,r2.Topic
      ,r2.Status
      ,hrs.Employee
      ,hrs.WeekOf
      ,hrs.HoursForWeek
      ,r2.PriorityNumber
      ,r2.Description
      ,Requestor = UserName
FROM dbo.tblCostCenter cc
    INNER JOIN dbo.tblDivision div
        ON cc.DivisionID = div.DivisionID
    RIGHT OUTER JOIN dbo.tblRequest2 r2
        ON cc.CostCenterName = r2.CostCenter
    LEFT OUTER JOIN dbo.qryWorkHoursForWeek hrs
        ON r2.WorkOrderID = hrs.WorkOrderID
WHERE (r2.DateCancelled
      BETWEEN @FromDate AND @ToDate
      )
      AND (hrs.WeekOf
      BETWEEN @FromDate AND @ToDate
          )
      OR (r2.DateCompleted
      BETWEEN @FromDate AND @ToDate
         )
         AND (hrs.WeekOf
      BETWEEN @FromDate AND @ToDate
             )
      OR (r2.DateOrder <= @ToDate)
         AND (r2.DateCancelled IS NULL)
         AND (r2.DateCompleted IS NULL)
         AND (hrs.WeekOf
      BETWEEN @FromDate AND @ToDate
             )
         AND (r2.DateClosed IS NULL)
      OR (r2.DateOrder <= @ToDate)
         AND (r2.DateCancelled IS NULL)
         AND (r2.DateCompleted IS NULL)
         AND (hrs.WeekOf IS NULL)
         AND (r2.DateClosed IS NULL);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 5 posts - 1 through 4 (of 4 total)

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

Data Rat SSC Eights! Points: 943 More actions · Answer 1

I still cant make heads or tails out of it. Although you are right that it can be in a neat format. why did they do a left join then a right join when they are the same thing. Nobody should ever release uncommented code. I was wondering is there a way to test by running the select statements.

This reply was modified 5 years, 2 months ago by Data Rat .

drew.allen SSC Guru Points: 76997 More actions · Answer 2

A left join and a right join are not the same thing. They are mirrors of each other.

The main issue here is that they are using NULL values for unknown dates. This requires them to write very complex queries to handle the NULL values when using artificial dates would generally make these queries much simpler. I tend to use '1900-01-01' for unknown dates in the past and '9999-12-30' for unknown dates in the future.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

To understand what this query is doing, think about the sets of data this represents. To me, this query says:

Look at all of the requests that were either cancelled, completed, ordered, or still open in the date range
Do not include requests that don't have any work hours for the date range unless the request is still open
Not every request has a cost center and division
Not every request has hours

I don't think magic numbers for the status dates will help simplify the WHERE clause logic, the complexity of the WHERE clause logic is mainly trying to deal with the denormalized status dates and including open requests without work hours.