Solved

Sql Query

Posted on 2011-03-10
10
231 Views
Last Modified: 2012-05-11
I would like to do a sql that does the following:

Sum the number of days over a non-consecutive date range per emplid.  Here is data example
emplid          start date       end date
9999999      02/01/2010      04/30/2010
9999999      09/08/2009      01/29/2010      
9999999      09/07/2010      12/20/2010      

The query would give the total number of days worke within the date range of
Jan 1 2010 - Dec 31 2010

Any help would be greatly appreciated,

Nigluc
0
Comment
Question by:Lucia
  • 5
  • 5
10 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35099452
try

select  emplid, sum(DATEDIFF(DD, [start date], [end date]) [Days Worked]
from table1
where startdate between '01/01/2010' and '12/31/2010'
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35099463
you need a group by as well

select  emplid, sum(DATEDIFF(DD, [start date], [end date]) [Days Worked]
from table1
where startdate between '01/01/2010' and '12/31/2010'
group by emplid
0
 

Author Comment

by:Lucia
ID: 35108150
Hi ewangoya,

If I run this query, I get the following answer:

SELECT * FROM [TableName] WHERE Date BETWEEN #12/31/1900# AND #12/31/2000# group on emplid, start date , end date

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 part of the 3 record that applies to be included... ie

09/07/2010      12/20/2010      

I have to think about this .....

Do you have any ideas.  

Thanks,
Nigluc
I still wanty
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Lucia
ID: 35108361
Sorry I meant this record:

9999999      09/08/2009      01/29/2010      

Thanks, Nigluc
0
 

Author Comment

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

9999999      09/08/2009      01/29/201

ie only those that applly to the  Jan 1, 2010.

Is this possible?
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35109151
Your where clause does not seem to be correct

select  
Emplid,
[start date],
[end date],
sum(DATEDIFF("d",[start date], [end date])) as DaysWorked
from table1
WHERE [start date] >= #01/01/2010#
and  [end date] <=  #12/31/2010#
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35109163


select  
Emplid,
[start date],
[end date],
sum(DATEDIFF("d",[start date], [end date])) as DaysWorked
from table1
WHERE [start date] >= #01/01/2010#
and  [end date] <=  #12/31/2010#
group by emplid,[start date], [end date]
0
 

Author Comment

by:Lucia
ID: 35109230
Hi ewangoya,

I still want that third record.  The client still wants to count days that are in date ranges that would sill have the jan 1 -2010  onward.  so for the range in question I would like to see something like  29 days worked instead of 173....

9999999      09/08/2009      01/29/2010    

Thank you,
Nigluc
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35109294
If you want the days worked for each record entry, you dont need the group by


select  
Emplid,
[start date],
[end date],
DATEDIFF(DD,[start date], [end date]) as DaysWorked
from table1
WHERE [start date] >= #01/01/2010#
and  [end date] <=  #12/31/2010#


0
 

Author Comment

by:Lucia
ID: 35110233
Hi  ewangoya,

This worked fine for me:

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]


Results in :

'4147737'         2009-09-08 00:00:00      2010-01-29 00:00:00      28
'4147737'           2010-02-01 00:00:00      2010-04-30 00:00:00      88
'4147737'      2010-09-07 00:00:00      2010-12-20 00:00:00      104

Thanks for your  help.  

Nigluc
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql, case when & top 1 14 30
RAISERROR WITH NOWAIT 2 18
Microsoft CRM 4.0 26 22
DataType for bit Comparison 11 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

820 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