September 3, 2012 at 1:11 am
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.
September 3, 2012 at 1:25 am
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 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
September 3, 2012 at 1:44 am
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
September 3, 2012 at 2:02 am
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 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
September 3, 2012 at 2:58 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply