Results 1 to 6 of 6

Thread: Calculate Time

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Calculate Time

    Hi all I am trying to Convert the total number of Hours on a table. For instance if a Student [Req hours]
    Mon Tues Wed thurs
    30 30 30 30 = 2.0 hrs

    How exactly do I calculate time.


    Code:
    ALTER PROCEDURE [dbo].[ClientHours]
    @Beginning_ContactDate datetime =NULL,
    @End_ContactDate datetime = NULL,
    @Enter_ParentLastName nvarchar(255) = null
    AS
    SELECT     Contact_tbl.[Earned hours], Contact_tbl.[Referral Date], Contact_tbl.[Catagory for hours], Contact_tbl.[Services Covered], Contact_tbl.[State Catagory], SUM(Contact_tbl.[Earned hours]) As TotalEarned,
                          Contact_tbl.[Contact Date], Parent_Sc.[Parent First Name], Parent_Sc.[Parent Last Name], Parent_Sc.[Parent ID], SUM(Parent_Sc.[Req hours]) 
                          AS TotalRequiredHrs, Parent_Sc.[Req hours], Parent_Sc.[Mo Hours], Parent_Sc.[Req hours]
    FROM         Contact_tbl INNER JOIN
                          Parent_Sc ON Contact_tbl.[Parent ID] = Parent_Sc.[Parent ID]
    WHERE (@Beginning_ContactDate is null or [Contact Date] >= @Beginning_ContactDate)
    AND (@End_ContactDate is null or [Contact Date] <= @End_ContactDate)
    AND ([Parent Last Name] like '%'+ @Enter_ParentLastName + '%' or @Enter_ParentLastName is null)
    GROUP BY    Contact_tbl.[Earned hours], Contact_tbl.[Referral Date], Contact_tbl.[Catagory for hours], Contact_tbl.[Services Covered], Contact_tbl.[State Catagory], 
                          Contact_tbl.[Contact Date], Parent_Sc.[Parent First Name], Parent_Sc.[Parent Last Name], Parent_Sc.[Parent ID], Parent_Sc.[Req hours],
                          Parent_Sc.[Mo Hours]

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    So the [Req hours] is actually in minutes, not hours? Try:

    SELECT SUM([Req hours])/60.0 AS Hrs, ...

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    No sorry it is hours, my mistake I need to know how to subtract. for instance if they need 20hrs and they do 1.33. Sorry about

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I think you need to be more specific if you want a useful answer. Subtract is just "col1 - col2". Now what are you really asking? Please give an example of what your data looks like and what result you want.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Sorry dportas, I think this is what I am looking for. I have a bad tendency to make things harder then what they really are. What I am trying to do is


    Hours Required
    40

    Hours Earned
    1.5

    Hours Due
    38.5 more hours to do

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    ALTER PROCEDURE [dbo].[ClientHours]
    @Beginning_ContactDate datetime =NULL,
    @End_ContactDate datetime = NULL,
    @Enter_ParentLastName nvarchar(255) = null
    AS
    SELECT     Contact_tbl.[Earned hours], Contact_tbl.[Referral Date], Contact_tbl.[Catagory for hours], Contact_tbl.[Services Covered], Contact_tbl.[State Catagory], Contact_tbl.[Contact Date], Parent_Sc.[Parent First Name], Parent_Sc.[Parent Last Name], Parent_Sc.[Parent ID],  SUM(Parent_Sc.[Req hours]/60.0 - Contact_tbl.[Earned hours]) as HRSDue, Parent_Sc.[Mo Hours], Parent_Sc.[Req hours]
    FROM         Contact_tbl INNER JOIN
                          Parent_Sc ON Contact_tbl.[Parent ID] = Parent_Sc.[Parent ID]
    WHERE (@Beginning_ContactDate is null or [Contact Date] >= @Beginning_ContactDate)
    AND (@End_ContactDate is null or [Contact Date] <= @End_ContactDate)
    AND ([Parent Last Name] like '%'+ @Enter_ParentLastName + '%' or @Enter_ParentLastName is null)
    GROUP BY    Contact_tbl.[Earned hours], Contact_tbl.[Referral Date], Contact_tbl.[Catagory for hours], Contact_tbl.[Services Covered], Contact_tbl.[State Catagory], 
                          Contact_tbl.[Contact Date], Parent_Sc.[Parent First Name], Parent_Sc.[Parent Last Name], Parent_Sc.[Parent ID], Parent_Sc.[Req hours],
                          Parent_Sc.[Mo Hours]
    Last edited by desireemm; 05-23-09 at 15:38.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok all taken care of now. Now I just need to format the ending result. Thank you dportas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •