# How to sum using a Join In a report **URL:** \ **Category:** Ignition\ **Created:** [October 26, 2020, 6:54pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029 "2020-10-26T18:54:13Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [October 26, 2020, 6:54pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/1 "2020-10-26T18:54:14Z")
```auto 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 ``` ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [October 26, 2020, 7:17pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/2 "2020-10-26T19:17:36Z")
> [@jshaffer09](#): > > DATEADD(month, DATEDIFF(month, -1, getDate()), -1) ~~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](http://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/3)). ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 27, 2020, 11:23am UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/3 "2020-10-27T11:23:21Z")
> [@WillMT10](#): > > Meaning your start and end for the BETWEEN check is identical. Actually, no. That construct is common in T-SQL for truncating datetime to date. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [October 27, 2020, 12:34pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/4 "2020-10-27T12:34:44Z")
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? ---
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [October 27, 2020, 12:52pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/5 "2020-10-27T12:52:27Z")
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. ```auto 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 ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [October 27, 2020, 1:08pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/6 "2020-10-27T13:08:42Z")
You’re joins are out of order. You are using ‘c’ before you have defined what table it is to represent. Try: ```auto 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. ---
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [October 27, 2020, 1:18pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/7 "2020-10-27T13:18:16Z")
That worked! ```auto 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 ``` ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [October 27, 2020, 1:47pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/8 "2020-10-27T13:47:27Z")
> [@pturmel](#): > > Actually, no. That construct is common in T-SQL for truncating datetime to date. Oh weird...that’s ugly. Thanks for the correction. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 27, 2020, 2:02pm UTC](https://forum.inductiveautomation.com/t/how-to-sum-using-a-join-in-a-report/40029/9 "2020-10-27T14:02:59Z")
> [@WillMT10](#): > > Oh weird…that’s ugly It's Microsoft. Ugly is par for the course. Not everyone can have a nice, clean, efficient [date\_trunc](https://www.postgresql.org/docs/10/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC) implementation.