Anyone fancy helping me with a query?

  • Hi

    I'm trying to do a query on a call logging database, but want to return the call number with the latest history update.

    SELECT Call_No, Call_Log_Date, Call_Resolve_Time, Call_Downtime,

    Call_Resolve_Downtime, Call_Description,

    ACTIONS_DESC AS Last_Action, HISTORY_ORDER , RV_CALL.Partition

    FROM RV_CALL

    INNER JOIN CL_PROBLEM_HISTORY

    ON RV_CALL.Call_No = CL_PROBLEM_HISTORY.CALL_NUMBER

    WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'

    AND Call_Last_Action_Date = HISTORY_DATE

    The problem is I'm joining the latest history update from another table and the last action date has two or three history entries, but I only want the last or first.

    For example, returned values for query might be:

    Call No Call Desc Last Action History Order

    ------ -------- ----------- ------------

    18 Blah Blah Call Closed by user 7

    18 Blah Blah Automated e-mail on call closure 8

    20 Ping Pong Advised that this is now resolved 5

    20 Ping Pong Call Closed by user 6

    20 Ping Pong Automated e-mail on call closure 7

    I only want to get something like:

    Call No Call Desc Last Action History Order

    ------ -------- ----------- ------------

    18 Blah Blah Call Closed by user 7

    20 Ping Pong Advised that this is now resolved 5

    I've tried using MAX(HISTORY_ORDER) in various places to no avail and I'd rather not do this using a cursor or creating another table.

    Thanks,

    Akeel.

  • [font="Verdana"]

    Have you tried your query this way? It work well for me. This query will return the last History Orders. And you need the first one then just only replace the Max with Min.

    SELECT Call_No

    ,Call_Log_Date

    ,Call_Resolve_Time

    ,Call_Downtime

    ,Call_Resolve_Downtime

    ,Call_Description

    ,ACTIONS_DESC AS Last_Action

    ,Max(HISTORY_ORDER)

    ,RV_CALL.Partition

    FROM RV_CALL INNER JOIN CL_PROBLEM_HISTORY

    ON RV_CALL.Call_No = L_PROBLEM_HISTORY.CALL_NUMBER

    WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'

    AND Call_Last_Action_Date = HISTORY_DATE

    GROUP BYCall_No

    ,Call_Log_Date

    ,Call_Resolve_Time

    ,Call_Downtime

    ,Call_Resolve_Downtime

    ,Call_Description

    ,ACTIONS_DESC AS Last_Action

    ,RV_CALL.Partition

    confirm on this,

    Mahesh

    [/font]

    MH-09-AM-8694

  • Hi Mahesh,

    Thanks for replying.

    I have already tried this and it works partially, but only if I remove the call description and the action description as they're string data types, they cannot be sorted in the Group by clause. If I remove them, then I don't really get the data that I need, I just get the numbers and not the text descriptions of what the actions were, or what the call description is.

    Akeel.

  • Ok, maybe give this a try...

    It's not tested, since you didn't give table structure or sample data

    SELECT Call_No,

    Call_Log_Date,

    Call_Resolve_Time,

    Call_Downtime,

    Call_Resolve_Downtime,

    Call_Description,

    ACTIONS_DESC AS Last_Action,

    LatestHistory,

    RV_CALL.Partition

    FROM RV_CALL

    INNER JOIN CL_PROBLEM_HISTORY ON RV_CALL.Call_No = CL_PROBLEM_HISTORY.CALL_NUMBER

    INNER JOIN

    (SELECT Call_Number, Max(History_Order) LatestHistory FROM CL_PROBLEM_HISTORY group by Call_Number) AS LatestAction

    ON CL_PROBLEM_HISTORY.Call_number = LatestAction.Call_number

    AND CL_PROBLEM_HISTORY.History_order = LatestAction.LatestHistory

    WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'

    AND Call_Last_Action_Date = HISTORY_DATE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Akeel..

    You can try the below..

    Because i did the same in my project while i was in that kind of situation like you met..

    Its worked well..

    Just add the column name as u want from the problem history table..

    SELECT Call_No, Call_Log_Date, Call_Resolve_Time, Call_Downtime,

    Call_Resolve_Downtime, Call_Description,

    ACTIONS_DESC AS Last_Action, HISTORY_ORDER , RV_CALL.Partition,

    (select Top 1 from CL_PROBLEM_HISTORY where CALL_NUMBER = RV_CALL.Call_No order by HistoryOrder desc)

    FROM RV_CALL

    WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'

    AND Call_Last_Action_Date = HISTORY_DATE

    Ramkumar . K
    Senior Developer
    ######################
    No Surrender... No Give Up....
    ######################

  • That will work. Just be aware that it has the potential to become a major performance problem when the tables get large.

    The subquery in the select clause runs once for every row in the outer result set. It is esentially a cursor in disguise.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for replying guys. Gilamonster, that worked perfectly, thanks.

    It was really annoying me and I knew it would be pretty straightforward.

  • Hi Gail..

    Greetings..

    Thanks for your kind notification against my reply..

    I have doubt with Linked server concept..

    Can u help in that????

    Specified Below :

    I am using the below statements to create a linked server with the Excel file and also to view the contents of that...

    I would like to insert new data into that excel file from Sql server 2005 using liked server concept.

    Is it possible to insert data in that way..?

    Can any describe that or offer any other better solution??

    Example:

    --Use Test

    --

    --EXEC sp_addlinkedserver 'ImportData',

    --

    --'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',

    --

    --'C:\ExcelData1.xls',

    --

    --NULL,

    --

    --'Excel 8.0'

    SELECT *

    FROM OPENQUERY(ImportData, 'SELECT * FROM [EmployeeDataTesting$]')

    Ramkumar . K
    Senior Developer
    ######################
    No Surrender... No Give Up....
    ######################

  • Please create a new thread for your question.

    Thanks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

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