Formatting a Date if already using MIN function

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • skifast
    New Member
    • Apr 2013
    • 1

    Formatting a Date if already using MIN function

    I have a query where I am using the MIN function to filter out duplicate Call Numbers (Call_No). The query works well and returns 34 records -- perfect. Here it is:

    Code:
    SELECT RV_CALL_HISTORY.Call_No as "Call Number",
    MIN(RV_CALL_HISTORY.Call_Log_Date) as "Call Log Date",
    MIN(RV_CALL_HISTORY.Call_Hist_Date) as "Call Hist Date"
    FROM RV_CALL_HISTORY
    WHERE
    RV_CALL_HISTORY.Call_Log_Date > '2012-04-01 00:00'
    AND
    RV_CALL_HISTORY.Call_Hist_Action_Officer='Joe Blow'
    AND
    RV_CALL_HISTORY.Call_Log_Date < GETDATE()
    GROUP BY RV_CALL_HISTORY.Call_No
    ---------------------------
    Now I would like to convert the Call_Hist_Date to:
    YYYY.MM.DD

    I have tried using the following, but then I lose the filter. I get 114 records:
    Code:
    SELECT RV_CALL_HISTORY.Call_No as "Call Number",
    MIN(RV_CALL_HISTORY.Call_Log_Date) as "Call Log Date",
    CONVERT(VARCHAR(10),RV_CALL_HISTORY.Call_Hist_Date,102) as [YYYY.MM.DD]
    FROM RV_CALL_HISTORY
    WHERE
    RV_CALL_HISTORY.Call_Log_Date > '2012-04-01 00:00'
    AND
    RV_CALL_HISTORY.Call_Hist_Action_Officer='Joe Blow'
    AND
    RV_CALL_HISTORY.Call_Log_Date < GETDATE()
    GROUP BY RV_CALL_HISTORY.Call_No,RV_CALL_HISTORY.Call_Hist_Date
    -------------------------
    Any idea how I can combine the MIN and CONVERT functions?
    I am using Microsoft SQL Management Studio (Microsoft SQL Server 2008 R2)

    Thanks,
    skifast
    Last edited by Rabbit; Apr 18 '13, 03:39 AM. Reason: Please use code tags when posting code.
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    Please use code tags when posting code.

    Do the conversion around the min.

    Comment

    Working...