Sum where date is between two dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum where date is between two dates

Author

Topic

katenash
Starting Member

3 Posts

Posted - 2014-08-31 : 09:23:05

I have two tables, one contains a list of month end dates and one contains a list of resource requirements. I want to show the resource required by month, where the month end date falls between the start and end of the resource requirement date. So far I have:

Select Sum(tblResRequirements.FTE) As RequiredFTE,
vwMonthEnds.MonthEnd
From tblResRequirements,
vwMonthEnds
Inner Join on MonthEnd between StartDate and EndDate
Group By vwMonthEnds.MonthEnd

Which isn't working as the Join is not correct

what is the right code to use to get the join to work?

thanks

Kate

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-31 : 17:27:21

[code]select sum(tblResRequirements.FTE) as RequiredFTE
,vwMonthEnds.MonthEnd
from tblResRequirements
inner join vwMonthEnds
on MonthEnd between StartDate and EndDate
group by vwMonthEnds.MonthEnd[/code]

katenash
Starting Member

3 Posts

Posted - 2014-09-01 : 03:00:51

Thanks for this! However, it is returning a value of 174 for each month end, not the value for the month (174 is the total FTE in the whole table)

What am I doing wrong?

select sum(tblResRequirements.FTE) as RequiredFTE
,vwMonthEnds.MonthEnd
from tblResRequirements
inner join vwMonthEnds
on MonthEnd between StartDate and EndDate
group by vwMonthEnds.MonthEnd

Kate

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-01 : 12:36:06

Please provide sample data, the output you get from query (based on the sample data) and the expected output (based on the sample data).

katenash
Starting Member

3 Posts

Posted - 2014-09-02 : 03:26:38

Thanks, what is the best way to get a data sample onto the thread (apologies, I am new to the forum!)

Kate

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-02 : 10:56:56

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]