asked on

SQL Sum using Left Outer Join not returning all results

I'm trying to get the sum from 2 different views and noticed that when there is only a value in vWIP_Install_Sub and NOT vWIP_Install_TimeSheets, there is no sum value returned. I need to ensure that even if there is no data in vWIP_Install_TimeSheets that the value in vWIP_Install_Sub is shown. Customer ID for both views exist in the Customers table.

SELECT TOP (100) PERCENT C.FullName, CASE WHEN S.HOURS_WORKED IS NULL THEN CONVERT(nvarchar(4), SUM(DATEDIFF(minute, 0, Duration)) / 60)
+ '.' + CONVERT(nvarchar(2), SUM(DATEDIFF(minute, 0, Duration)) % 60) ELSE CONVERT(nvarchar(4), SUM(DATEDIFF(minute, 0, Duration)) / 60)
+ '.' + CONVERT(nvarchar(2), SUM(DATEDIFF(minute, 0, Duration)) % 60) + S.Hours_Worked END AS install_hours_worked
FROM CUSTOMERS AS C LEFT OUTER JOIN
dbo.vWIP_Install_TimeSheets AS T ON C.ID = T.CustomerId LEFT OUTER JOIN
dbo.vWIP_Install_Sub AS S ON C.ID = S.ItemCustomerId
WHERE C.JobStatus = 'INPROGRESS' AND T.CustomerName IS NOT NULL
GROUP BY C.FullName, S.Hours_Worked
ORDER BY C.FullName
sum-issue-1.png
sum-issue-2.png

Kevin Cross

You have a filter "AND T.CustomerName IS NOT NULL". When there is no record, CustomerName will be NULL and as such excluded.

Jeremy Poisson

ASKER

Yup, sorry, that was an error on my part HOWEVER, still the same issue post modification.
sum-issue-3.png

Kevin Cross

Remove S.Hours_Worked from group by. To test do a select all on the query without the grouping and sums. If you see the correct records, leave the S columns out of grouping. Then in the sum, use conditional aggregate.

E.g. SUM(CASE WHEN ... END)

Snarf0001

You need to pull the customer name out of the where clause.

You have:

FROM CUSTOMERS AS C 
LEFT OUTER JOIN dbo.vWIP_Install_TimeSheets AS T ON C.ID = T.CustomerId 
LEFT OUTER JOIN dbo.vWIP_Install_Sub AS S ON C.ID = S.ItemCustomerId
WHERE C.JobStatus = 'INPROGRESS' AND T.CustomerName IS NOT NULL
GROUP BY C.FullName, S.Hours_Worked
ORDER BY C.FullName

Open in new window

When you put "T.CustomerName is not null", that will automatically exclude the row and make it in effect an inner join.
You need to move that condition in as part of the join clause instead:

FROM CUSTOMERS AS C 
LEFT OUTER JOIN dbo.vWIP_Install_TimeSheets AS T ON C.ID = T.CustomerId  and T.CustomerName IS NOT NULL
LEFT OUTER JOIN dbo.vWIP_Install_Sub AS S ON C.ID = S.ItemCustomerId
WHERE C.JobStatus = 'INPROGRESS'
GROUP BY C.FullName, S.Hours_Worked
ORDER BY C.FullName

Open in new window

Also, when you're creating the string, if the rows aren't in there you're going to get a null value, which when combined with the other strings is going to result in a null value again.
Not sure which columns in teh select are part of "T", but make sure you're doing an isnull check against it.

Mark Edwards

As stated above. This looks like a classic case of "not knowing how joins and criteria interact".
Because of the criteria T.CustomerName IS NOT NULL, the From and Where clause:

FROM CUSTOMERS AS C 
LEFT OUTER JOIN dbo.vWIP_Install_TimeSheets AS T ON C.ID = T.CustomerId 
WHERE C.JobStatus = 'INPROGRESS' AND T.CustomerName IS NOT NULL

Open in new window

acts as thought it is written this way:

FROM CUSTOMERS AS C INNER JOIN dbo.vWIP_Install_TimeSheets AS T ON 
C.ID = T.CustomerId 
WHERE C.JobStatus = 'INPROGRESS' AND T.CustomerName IS NOT NULL

Open in new window

In cases in like this the "T" table needs to be made a subquery and the subquery LEFT JOINed to C table.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeremy Poisson

ASKER

Kevin - thank you very much. This looks very close to what I now need. The only question I have is related to the MINUTES. For example, the actual install hours worked for 3T-INNOVATIONS:10-18-9629 3TI Phone Install Phase 1 is 1,590.38 (one thousand five hundred ninety and thirty eight minutes) however, the new result is one thousand five hundred ninety and 58. Is there any way to have the minutes reflected. Another customers has 5695.85 (only 60 minutes in an hour). These customers were always accurate. The reason for I'm asking the experts is that the team threw another billing method into the mix (#3) and now want o ensure that even jobs with 0 hours/minutes worked are included.

Again, thank you VERY much as I'm nearly there with your help.

Oh, and JobStatus must always = InProgress. That one can stay. Even if there are 0 hours worked the project managers still need to see those jobs in the queue.
SQL-Minutes.png

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeremy Poisson

ASKER

Perfect - thank you, Kevin. I'm going to award this solution to you but open another ticket for the sum of hours. I'm just not getting the right results with the previous method. Again, thank you for sticking with me and for you valued expertise!

Snarf, Mark - many thanks for your contributions as well!

Jeremy Poisson

ASKER

Kevin Cross

You are most welcome, Jeremy!

Just let us know what the current code and challenge is in the new question and sure folks here will love to help.
Take care and happy coding,

Kevin