Post reply

Showing column as decimal in select

  • June 30, 2009 at 7:08 pm

    #384886

    I have searched for a way to do this but can't find the answer. Maybe it is not possible? I am trying to calculated the % of minutes a patient is on a shift.

    Bascially it is total minutes patient on shift/total minutes in a shift. Here is the part of the script that calculates using date diff:

    DATEDIFF(n,dbo.Assgn_Patient_List.Start_DTime, dbo.Assgn_Patient_List.End_DTime) AS MinutesOnShift,

    DateDiff(n,dbo.Assgn_Shift_Summary.Start_DTime,dbo.Assgn_Shift_Summary.End_DTime) AS MinutesPerShift

    The above calculates the minutes correctly returning, as an example MinutesOnShift 120 and MinutesPerShift 720. I can manually do the math on this 120/720 = .17.

    When I try the below code:

    DATEDIFF(n,dbo.Assgn_Patient_List.Start_DTime, dbo.Assgn_Patient_List.End_DTime) /

    DateDiff(n,dbo.Assgn_Shift_Summary.Start_DTime,dbo.Assgn_Shift_Summary.End_DTime) AS PercentofTimeOnShift

    I end up with a result of PercentOfTimeOnShift = 0 rather then the .17 expected.

    What am I missing?

  • June 30, 2009 at 7:12 pm

    #1018088

    it's the shortcut SQL uses for data types; integer divided by integer yields an integer result.

    multiply either the numerator or denominator by 1.0 and you'll get your 0.17

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2009 at 7:25 pm

    #1018089

    Lowell,

    Thanks for the quick response. I have tried:

    DATEDIFF(n,dbo.Assgn_Patient_List.Start_DTime, dbo.Assgn_Patient_List.End_DTime)*1.0 /

    DateDiff(n,dbo.Assgn_Shift_Summary.Start_DTime,dbo.Assgn_Shift_Summary.End_DTime) AS PercentofTimeOnShift

    DATEDIFF(n,dbo.Assgn_Patient_List.Start_DTime, dbo.Assgn_Patient_List.End_DTime) /

    DateDiff(n,dbo.Assgn_Shift_Summary.Start_DTime,dbo.Assgn_Shift_Summary.End_DTime)*1.0 AS PercentofTimeOnShift

    but I end up with the same result of zero. Did I understand you correctly?

  • June 30, 2009 at 7:46 pm

    #1018095

    You did. Parentheses can make a difference.

    select datediff(n, '2009-06-30 20:37:16', '2009-06-30 20:49:00') / (datediff(n, '2009-06-30 05:00:00', '2009-06-30 10:00:00') * 1.0)

    should get you 0.0400

    An alternative is to explicitly convert one or the other numbers, or both

    select convert(decimal, datediff(n, '2009-06-30 20:37:16', '2009-06-30 20:49:00')) / (datediff(n, '2009-06-30 05:00:00', '2009-06-30 10:00:00') )

  • June 30, 2009 at 8:01 pm

    #1018100

    Lowell and ksullivan,

    Thanks to you both. Works great! ksullivan thanks for showing both options.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply