[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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
0
Lucia
Asked:
Lucia
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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]
0
 
LuciaAuthor Commented:
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
0
 
vandalesmCommented:
Im not sure about Access but in sql server it can be done by using a CASE statement in the SELECT clause.

example:
SUM ( DATEDIFF("d",
  (CASE WHEN start_date < '2010-1-1' then '2010-1-1 ELSE start_date END),
   end_date)
)
0
 
LuciaAuthor Commented:
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]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now