Count Number of days between dates

  • Thread starter Thread starter blinton25
  • Start date Start date
B

blinton25

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?
 
Hi,


Sure. It is basically a running sum over +1 and -1.


Save the following query (qu1) :


SELECT StartDate As TheDate, +1 As Offset FROM myTable
UNION ALL
SELECT StartDate+NumberOfDays, -1 FROM myTable




then, make a running sum over Offset, ordering through TheDate


SELECT a.TheDate, SUM(b.offset)
FROM qu1 As a INNER JOIN qu1 As b
ON b.TheDate < a.TheDate OR ( b.TheDate=a.TheDate AND b.Offset <
a.Offset )
GROUP BY a.TheDate
ORDER BY a.TheDate

That should do it. Note that the last OrderBy is just for presentation, not
required for the computation.


Also note I tried to account for people leaving BEFORE people arrives, that
is why the join is a little bit complex, it just "order" internally the "b"
to occur "before" whaterver is the actual a.TheDate:
ON b.TheDate < a.TheDate OR ( b.TheDate=a.TheDate AND b.Offset
< a.Offset )





Hoping it may help,
Vanderghast, Access MVP
 
Hello,

Thanks for the information.
-----Original Message-----
Hi,


Sure. It is basically a running sum over +1 and -1.


Save the following query (qu1) :


SELECT StartDate As TheDate, +1 As Offset FROM myTable
UNION ALL
SELECT StartDate+NumberOfDays, -1 FROM myTable




then, make a running sum over Offset, ordering through TheDate


SELECT a.TheDate, SUM(b.offset)
FROM qu1 As a INNER JOIN qu1 As b
ON b.TheDate < a.TheDate OR (
Click to expand...
b.TheDate=a.TheDate AND b.Offset <
a.Offset )
GROUP BY a.TheDate
ORDER BY a.TheDate

That should do it. Note that the last OrderBy is just for presentation, not
required for the computation.


Also note I tried to account for people leaving BEFORE people arrives, that
is why the join is a little bit complex, it just "order" internally the "b"
to occur "before" whaterver is the actual a.TheDate:
ON b.TheDate < a.TheDate OR (
Click to expand...
b.TheDate=a.TheDate AND b.Offset
 
Back
Top