Solved

counting partial days in a date range in Microsoft Access 2007

Posted on 2011-03-11
4
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

717 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