How to sum using a Join In a report



How to sum  using a Join in a report

I am trying to run the query below. When I run it, it shows no results. I am trying to list out each AppUsers name and there hours for the current month.

Select a.UserNm, sum(isnull(b.hrs, 0))Hours
from Appuser a
    JOIN EquipWorkOrderHrs b 
        on a.UserID = b.UserID
Where a.DelFlg = 0 and DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group by a.UserNm
Order by a.UserNm

Looks like you are adding a month with that inner DATEDIFF and then subtracting a month with the outer DATEADD, which will get you right back to the getDate() value. Meaning your start and end for the BETWEEN check is identical. Try replacing the start and end with static datetime values to troubleshoot to make sure the query is returning what you expect for a date range you have manually checked. If it works with static datetime values, you need to correct these date calculations.

Retracted so as not to cause confusion (see pturmel's response below).

Actually, no. That construct is common in T-SQL for truncating datetime to date.

1 Like

A similar query works and produces the expected results for me, so the query syntax is good. This seems to indicate that something in the data is not as you would expect.

  1. There are no rows in Table A that match UserID with Table B. ( I would find this highly unlikely, otherwise why would you be writing the query.)
  2. You did not specify which table DateTm exists in. This indicates that it is unique between the two tables and it makes the most since for it to be in Table B. So perhaps there are no users in Table A that had ‘hrs’ in the last month. (Again, I find this highly unlikely)
  3. There are no users in Table A with a ‘DelFlg’ = 0 that had ‘hrs’ in the last month. I find this to be the most likely culprit. If you remove the ‘DelFlg’ constraint from the where clause, does the query return data?

after reading your post I realized I have to add a third table to join in the mix, where the DateTm is. There is a DateTm in The Appuser table which would show no results for this month.

When I run this, the error say The multi-part identifier “c.EquipWorkOrderID” could not be bound.

Select a.UserNm, sum(isnull(c.hrs, 0))Hours
from Appuser a
    JOIN EquipWorkOrder b 
        on b.EquipWorkOrderID = c.EquipWorkOrderID
    JOIN EquipWorkOrderHrs c 
            on a.UserID = c.UserID
Where a.DelFlg = 0 and b.DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group by a.UserNm
Order by a.UserNm

You’re joins are out of order. You are using ‘c’ before you have defined what table it is to represent.

Try:

Select a.UserNm, sum(isnull(c.hrs, 0))Hours
from Appuser a
    JOIN EquipWorkOrderHrc c 
            on a.UserID = c.UserID
    JOIN EquipWorkOrder b
            on b.EquipWorkOrderID = c.EquipWorkOrderID
Where a.DelFlg = 0 and b.DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group by a.UserNm
Order by a.UserNm

Also, you said you needed the third table for the DateTm but then specified it as coming from Table B.

1 Like

That worked!


Select a.UserNm, sum(isnull(c.hrs, 0))Hours 
from Appuser a 
       JOIN EquipWorkOrderHrs c 
                 on a.UserID = c.UserID 
       JOIN EquipWorkOrder b 
                 on b.EquipWorkOrderID = c.EquipWorkOrderID 
Where a.DelFlg = 0 and b.DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1) Group by a.UserNm Order by a.UserNm

Oh weird...that’s ugly. Thanks for the correction.

It's Microsoft. Ugly is par for the course. Not everyone can have a nice, clean, efficient date_trunc implementation.

3 Likes