asked on

counting partial days in a date range in Microsoft Access 2007

Here is the query which was partially derived by an EE ...
select
Emplid,
[start date],
[end date],
sum(DATEDIFF("d",[start date], [end date])) as DaysWorked
from table1

WHERE [end date] > #01/01/2010# and
[start date] < #12/31/2010#

group by emplid,[start date], [end date]

So this will give me all three date ranges but I want only partial number of days in the

Emplid      start date      end date      DaysWorked
9999999      09/08/2009      29/01/2010      173
9999999      02/01/2010      30/04/2010      118
9999999      09/07/2010      20/12/2010      164

but I want the record 9999999      09/07/2010      20/12/2010      164
to only reflect days worked from Jan 1, 2010. Is this possible?

Thanks, Nigluc

Rey Obrero (Capricorn1)

try this

select
Emplid,
[start date],
[end date],
sum(DATEDIFF("d",[start date], [end date])) as DaysWorked
from table1

WHERE [end date] between #01/01/2010# and #12/31/2010#
and [start date] Between #01/01/2010# and #12/31/2010#

group by emplid,[start date], [end date]

Brock

ASKER

Hi Capricorn1,

The result from your query:

Emplid      start date      end date      DaysWorked
9999999      02/01/2010      30/04/2010      118
9999999      09/07/2010      20/12/2010      164

I still want the 3 date range but I only want it to reflect the date range in the criteria so it would have something like 29 days

Thanks,
Nigluc

ASKER CERTIFIED SOLUTION

vandalesm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brock

ASKER

Hi,

I don't normally work in access but I don;t think it has case. I was able to get my sql server going...as I am at my home. And yes, the solution provided works fine. Thanks to all who replied.

Nigluc

select
Emplid,
[start date],
[end date],
SUM ( DATEDIFF("d",
(CASE WHEN [start date] < '2010-1-1' then '2010-1-1' ELSE [end date] END),
[end date])) AS DAYS_WORKED
from dbo.days_worked_2
WHERE [end date] > '01/01/2010'
and [end date] < '12/31/2010'
and [start date] < '01/01/2010'
AND EMPLID='4147737'
group by emplid,[start date], [end date]

UNION

select
Emplid,
[start date],
[end date],
SUM ( DATEDIFF("d",
(CASE WHEN [start date] > '2010-1-1' then [start date] ELSE [end date] END),
[end date])) AS 'DAYS_WORKED'
from dbo.days_worked_2
WHERE [end date] > '01/01/2010'
and [end date] < '12/31/2010'
AND [start date] > '01/01/2010'
AND EMPLID='4147737'
group by emplid,[start date], [end date]