fetch latest status of customer

  • Hi friends,

    I m having CUST_STATUS table which shows statuscode and effectivedate table.status 1 means customer is active.Customer can deactive,close or transfer stats respectively as 2,3, and 4.Statu can chagne at any time.

    example:- Below are two customers having CustID 1001 and 2001.I want to show only 2001 customer as his latest status is 1 for related MAX date.

    I understands it can happen by fetching max effectivedate where status =1 but not getting proper answer. Please help.

    Effetivedate status CustID

    2/6/12 8:15 AM 4 1001

    2/2/12 12:00 AM 1 1001

    1/11/12 11:15 AM2 1001

    10/12/11 8:22 AM1 1001

    10/12/11 12:00 AM3 1001

    10/12/11 12:00 AM2 1001

    10/10/11 8:00 AM1 1001

    8/8/11 2:31 AM 2 1001

    5/28/10 10:46 PM 1 2001

    12/5/09 6:43 PM 2 2001

    3/3/03 12:29 AM 3 2001

    1/23/03 12:28 AM 2 2001

    1/23/03 12:27 AM 1 2001

    1/9/03 12:26 AM 4 2001

    1/9/03 12:23 AM 2 2001

    11/25/02 12:25 AM 1 2001

    Regards

    Abhas.

  • Can you post DDL and your sample data in readily consumable format?

    Also, if you've already written a query, post that and a clear explanation of the result set you want. That will help immensely.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain the query as below.

    select CustID fromcust_status

    where EXISTS (select top 1 custid,EffectiveDate from cust_status

    where status= 1 order by EffectiveDate desc)

    data is as below.now i want to show only CustID = 2001 single record as only this customer is active.

    EffetivedatestatusCustID

    6-Feb-12 4 1001

    2-Feb-12 1 1001

    11-Jan-12 2 1001

    12-Oct-11 1 1001

    12-Oct-11 3 1001

    12-Oct-11 2 1001

    10-Oct-11 1 1001

    8-Aug-11 2 1001

    28-May-10 1 2001

    5-Dec-09 2 2001

    3-Mar-03 3 2001

    23-Jan-03 2 2001

    23-Jan-03 1 2001

    9-Jan-03 4 2001

    9-Jan-03 2 2001

    25-Nov-02 1 2001

    Abhas

  • abhas (9/3/2012)


    Dwain the query as below.

    select CustID fromcust_status

    where EXISTS (select top 1 custid,EffectiveDate from cust_status

    where status= 1 order by EffectiveDate desc)

    data is as below.now i want to show only CustID = 2001 single record as only this customer is active.

    EffetivedatestatusCustID

    6-Feb-12 4 1001

    2-Feb-12 1 1001

    11-Jan-12 2 1001

    12-Oct-11 1 1001

    12-Oct-11 3 1001

    12-Oct-11 2 1001

    10-Oct-11 1 1001

    8-Aug-11 2 1001

    28-May-10 1 2001

    5-Dec-09 2 2001

    3-Mar-03 3 2001

    23-Jan-03 2 2001

    23-Jan-03 1 2001

    9-Jan-03 4 2001

    9-Jan-03 2 2001

    25-Nov-02 1 2001

    Abhas

    This will probably work but it is untested because your sample data is not in readily consumable form.

    ;WITH CS AS (

    SELECT custid, EffectiveDate, status

    ,rn=ROW_NUMBER() OVER (PARTITION BY custid ORDER BY EffectiveDate DESC)

    FROM cust_status)

    SELECT CustID

    FROM CS

    WHERE rn = 1 AND status = 1


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • This should also work:

    SELECTCI1.CustomerID,CI1.CustomerStatus,CI1.EffectiveDate

    FROMtempdb.dbo.CustomerInfo CI1

    WHERECI1.EffectiveDate= (SELECTMAX(CI2.EffectiveDate)

    FROMCustomerInfo CI2

    WHERECI2.CustomerID = CI1.CustomerID

    )

    ANDCI1.CustomerStatus = 1


    Sujeet Singh

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

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