Count number of days based on date

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

Other Forums

MS Access

Count number of days based on date

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-17 : 12:25:37

Hello,

I have two fields, StartDate and NumberofDays

e.g:


StartDate NumberofDays
20040801        5
20040802        2

For any given day I want to find out how many people are present. So in the example above, on 20040801 I have 1 person, on 20040802 I have two people, and on 20040804 I will be back to one person since the person who is staying two days would have left.

My result set would look like:


20040801        1
20040802        2
20040803        2
20040803        1

but basically for an entire year.
Is there anyway to do this using SQL?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-17 : 13:26:13

would this do?

select StartDate, sum(NumberofDays) from MyTable
where (conditions)
group by StartDate
order by StartDate

Go with the flow & have fun! Else fight the flow :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-17 : 14:58:51

do you have at least 1 row in your table for each day? if not, do you need to return a row for everyday or just the ones present?

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-17 : 17:31:29

Hello,

Got it worked out with some assistance:


SELECT tblDateDriver.DateField, Sum(Iif((tblDateDriver.DateField >= tblGuestRegistration.StartDate) AND (tblDateDriver.DateField < DateAdd("d", tblGuestRegistration.[NumberofDays], tblGuestRegistration.[StartDate])), 1, 0))
FROM tblDateDriver, tblGuestRegistration
GROUP BY DateField;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources