Solved

counting partial days in a date range in Microsoft Access 2007

Posted on 2011-03-11
4
467 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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