Solved

counting partial days in a date range in Microsoft Access 2007

Posted on 2011-03-11
4
465 Views
Last Modified: 2012-05-11
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
Comment
Question by:Lucia
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35109147
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
 

Author Comment

by:Lucia
ID: 35109363
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
 
LVL 1

Accepted Solution

by:
vandalesm earned 500 total points
ID: 35109619
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
 

Author Comment

by:Lucia
ID: 35110201
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question