Difference between dates...

Hi All...
What I would like to achieve:
Calculate the amount of time spent on a job (JDEID) per day. For eg:
Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
03/01/2005 etc.
This is how my query started:
SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd, DATEDIFF(hh,
Tbl_JMS_Manhours.DateTimeStart,
Tbl_JMS_Manhours.DateTimeEnd) AS Diff
FROM Tbl_JMS_Manhours LEFT OUTER JOIN
Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID = Tbl_MS_Employees.EmployeeID
WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
Currently my query is showing me the difference only in hours, but I want to
know the hrs and minutes spent on a job. Then I don't remember how to only
show the date (31/01/2005). If I can convert my general date to a short
date, then I can seperate the days.
This is some current sample info: (AssignedID = An employee id which is
linked to a name)
ID JDEID AssignedID DateTimeStart DateTimeEnd
24 12345 114 31/01/2005 13:13 31/01/2005 13:20
40 157837 110 02/02/2005 07:00 02/02/2005 16:19
41 157837 110 02/02/2005 17:34 02/02/2005 18:19
42 157837 110 03/02/2005 07:00 03/02/2005 16:19
43 157837 110 04/02/2005 17:34 04/02/2005 18:19
In the end I wanna see it something like:
JDEID AssignedID Date Worked
157837 110 02/02/2005 7:35
157837 110 03/02/2005 8.15
Or something like that.
Please any help..
ThanksLook up the convert function in Books on Line to see all of the date
formatting possibilites..I don't understand how converting the date to a
short date is going to help...
What I would do is to get the difference in minutes, then do a little math
to convert that to hours and minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rudi Groenewald" <noone@.paflof.com> wrote in message
news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
> Hi All...
> What I would like to achieve:
> Calculate the amount of time spent on a job (JDEID) per day. For eg:
> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
> 03/01/2005 etc.
> This is how my query started:
> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd, DATEDIFF(hh,
> Tbl_JMS_Manhours.DateTimeStart,
> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID => Tbl_MS_Employees.EmployeeID
> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
> Currently my query is showing me the difference only in hours, but I want
to
> know the hrs and minutes spent on a job. Then I don't remember how to
only
> show the date (31/01/2005). If I can convert my general date to a short
> date, then I can seperate the days.
> This is some current sample info: (AssignedID = An employee id which is
> linked to a name)
> ID JDEID AssignedID DateTimeStart DateTimeEnd
> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
>
> In the end I wanna see it something like:
> JDEID AssignedID Date Worked
> 157837 110 02/02/2005 7:35
> 157837 110 03/02/2005 8.15
>
> Or something like that.
> Please any help..
> Thanks
>|||convert function aint helpin much...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u59PnMfCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Look up the convert function in Books on Line to see all of the date
> formatting possibilites..I don't understand how converting the date to a
> short date is going to help...
> What I would do is to get the difference in minutes, then do a little math
> to convert that to hours and minutes...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rudi Groenewald" <noone@.paflof.com> wrote in message
> news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
>> Hi All...
>> What I would like to achieve:
>> Calculate the amount of time spent on a job (JDEID) per day. For eg:
>> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
>> 03/01/2005 etc.
>> This is how my query started:
>> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
>> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd,
>> DATEDIFF(hh,
>> Tbl_JMS_Manhours.DateTimeStart,
>> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
>> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
>> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID =>> Tbl_MS_Employees.EmployeeID
>> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
>> Currently my query is showing me the difference only in hours, but I want
> to
>> know the hrs and minutes spent on a job. Then I don't remember how to
> only
>> show the date (31/01/2005). If I can convert my general date to a short
>> date, then I can seperate the days.
>> This is some current sample info: (AssignedID = An employee id which is
>> linked to a name)
>> ID JDEID AssignedID DateTimeStart DateTimeEnd
>> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
>> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
>> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
>> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
>> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
>>
>> In the end I wanna see it something like:
>> JDEID AssignedID Date Worked
>> 157837 110 02/02/2005 7:35
>> 157837 110 03/02/2005 8.15
>>
>> Or something like that.
>> Please any help..
>> Thanks
>>
>|||Try something like this:
SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
convert(varchar,Tbl_JMS_Manhours.DateTimeStart,101) as DateWorked,
DATEDIFF(hh, Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd) +
':' +
datediff(mi, Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd)
AS TimeWorked
FROM Tbl_JMS_Manhours LEFT OUTER JOIN
Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID =Tbl_MS_Employees.EmployeeID
WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
Vipul
"Rudi Groenewald" wrote:
> convert function aint helpin much...
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:u59PnMfCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> > Look up the convert function in Books on Line to see all of the date
> > formatting possibilites..I don't understand how converting the date to a
> > short date is going to help...
> >
> > What I would do is to get the difference in minutes, then do a little math
> > to convert that to hours and minutes...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Rudi Groenewald" <noone@.paflof.com> wrote in message
> > news:ctt85n$cpj$1@.ctb-nnrp2.saix.net...
> >> Hi All...
> >>
> >> What I would like to achieve:
> >>
> >> Calculate the amount of time spent on a job (JDEID) per day. For eg:
> >> Assigned id 110 has worked 9 Hrs, 35 minutes on 02/01/2005, 8hrs on
> >> 03/01/2005 etc.
> >>
> >> This is how my query started:
> >>
> >> SELECT Tbl_JMS_Manhours.JDEID, Tbl_MS_Employees.Name,
> >> Tbl_JMS_Manhours.DateTimeStart, Tbl_JMS_Manhours.DateTimeEnd,
> >> DATEDIFF(hh,
> >> Tbl_JMS_Manhours.DateTimeStart,
> >> Tbl_JMS_Manhours.DateTimeEnd) AS Diff
> >> FROM Tbl_JMS_Manhours LEFT OUTER JOIN
> >> Tbl_MS_Employees ON Tbl_JMS_Manhours.AssignedID => >> Tbl_MS_Employees.EmployeeID
> >> WHERE (Tbl_JMS_Manhours.JDEID = @.JDEID)
> >>
> >> Currently my query is showing me the difference only in hours, but I want
> > to
> >> know the hrs and minutes spent on a job. Then I don't remember how to
> > only
> >> show the date (31/01/2005). If I can convert my general date to a short
> >> date, then I can seperate the days.
> >>
> >> This is some current sample info: (AssignedID = An employee id which is
> >> linked to a name)
> >> ID JDEID AssignedID DateTimeStart DateTimeEnd
> >> 24 12345 114 31/01/2005 13:13 31/01/2005 13:20
> >> 40 157837 110 02/02/2005 07:00 02/02/2005 16:19
> >> 41 157837 110 02/02/2005 17:34 02/02/2005 18:19
> >> 42 157837 110 03/02/2005 07:00 03/02/2005 16:19
> >> 43 157837 110 04/02/2005 17:34 04/02/2005 18:19
> >>
> >>
> >> In the end I wanna see it something like:
> >>
> >> JDEID AssignedID Date Worked
> >> 157837 110 02/02/2005 7:35
> >> 157837 110 03/02/2005 8.15
> >>
> >>
> >> Or something like that.
> >>
> >> Please any help..
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>