none
need help in writing query to get right result to display latest w.r.t. date RRS feed

  • Question

  • Hi Experts,

    I've a table with a name - 'Call' and in it I've data as shown below...

    I want the ID of the last call with a distribution for a customer that happened on or before 31/7

    For example customer 1 (CustID = 12) had the following call cards:

    CallID            CustID        CreatedDate     Distribution                    Orders                  Response
    123700         12                   1/4/12                1                              1                         1
    123789         12                   1/5/12                0                              0                         1
    124567         12                   15/6/12               1                             0                         1
    125678         12                    2/7/12                1                             0                         1
    125999         12                   30/7/12               0                             1                         1
    130982         12                    2/8/12                1                             1                         1

    Then I want the query to return call Id =125678
    As this was the latest call to include a distribution that was on or before the specified date of 31/7/12

     Please help in writing this query.

    Thanks in advance.

    • Edited by SQL2012BI Tuesday, August 7, 2012 6:36 AM
    Tuesday, August 7, 2012 6:34 AM

Answers

  • Uri's solution too would work. However, you can use that when you are trying to retrieve the information for a particular customer. If you would like to retrieve the information for multiple customers from one query, you can use CTE...

    Murali Krishnan

    • Marked as answer by SQL2012BI Tuesday, August 7, 2012 6:58 AM
    Tuesday, August 7, 2012 6:44 AM

All replies

  • SELECT TOP 1 * FROM tbl WHERE CreatedDate <='20120731' ORDER BY CreatedDate DESC

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/


    Tuesday, August 7, 2012 6:40 AM
    Answerer
  • Try

    select a.*,row_number over (partition by custid order by CreatedDate desc) rn
    from tabl1 a
    where  Distribution>0
    and convert(varchar,CreatedDate,112)<='20120731'
    and rn=1;


    Many Thanks & Best Regards, Hua Min


    • Edited by Ai Guo Tuesday, August 7, 2012 6:41 AM
    Tuesday, August 7, 2012 6:41 AM
  • Use Row_Number() function.

    Not tested

    With CTE as

    (

    select *, Row_Number() OVER(Partition by CustID Order by CreatedDate Desc) RN from table where createddate < '31 July 2012' and distribution = 1

    )

    select CallID, * from CTE where RN = 1


    Murali Krishnan


    • Edited by Murali_CHN Tuesday, August 7, 2012 6:42 AM
    • Proposed as answer by Rishabh K Tuesday, August 7, 2012 6:42 AM
    Tuesday, August 7, 2012 6:41 AM
  • Uri's solution too would work. However, you can use that when you are trying to retrieve the information for a particular customer. If you would like to retrieve the information for multiple customers from one query, you can use CTE...

    Murali Krishnan

    • Marked as answer by SQL2012BI Tuesday, August 7, 2012 6:58 AM
    Tuesday, August 7, 2012 6:44 AM
  • Hi Murali Thanks a lot.
    Tuesday, August 7, 2012 6:58 AM