April 15, 2009 at 9:22 am
I need one row per cust_id with the max Effdt.
the Query:
SELECT CUST_ID,EFFDT,SHIP_TYPE_ID
FROM PS_CUST_SHIPTO_OPT
WHERE CUST_ID IN
(SELECT SOLD_TO_CUST_ID
FROM PS_ORD_HEADER
WHERE ORDER_STATUS='O' AND ORDER_GRP='CPU')
The results
CUST_ID EFFDT SHIP_TYPE_ID
0000000000800042008-07-24 00:00:00.000CPU
0000000000800042008-07-25 00:00:00.000CPU
00000800832008-10-02 00:00:00.000CPU
00000801182008-10-30 00:00:00.000CPU
00000801742009-01-27 00:00:00.000CPU
00000801752009-01-29 00:00:00.000CPU
00000802192009-03-11 00:00:00.000CPU
00000802292009-03-18 00:00:00.000CPU
1142 2008-04-07 00:00:00.000CPU
1142 2008-11-12 00:00:00.000CPU
12022 2008-04-07 00:00:00.000HDNTRK
1358 2008-04-07 00:00:00.000CPU
15 2008-04-07 00:00:00.000HDNTRK
15 2009-01-01 00:00:00.000CPU
15 2009-01-02 00:00:00.000CPU
15 2009-01-05 00:00:00.000
April 15, 2009 at 10:09 am
Give this a try. I obviously couldn't test it without you providing some test data, but I think it's what you're looking for. In the future, if you would write a little script to declare a temp table and put a little data into it, you will get a tested solution back much quicker... and the volunteers who are trying to help will love you for it. Questions accompanied by scripts to set up the data almost always get tackled before questions without such scripts.
;with cte as
(SELECT CUST_ID,EFFDT,SHIP_TYPE_ID
,ROW_NUMBER over (partition by CUST_ID order by EFFDT desc) as rowID
FROM PS_CUST_SHIPTO_OPT
WHERE CUST_ID IN
(SELECT SOLD_TO_CUST_ID
FROM PS_ORD_HEADER
WHERE ORDER_STATUS='O' AND ORDER_GRP='CPU')
)
select CUST_ID, EFFDT, SHIP_TYPE_ID
from cte
where rowID = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 15, 2009 at 10:14 am
Well I would do that but I do not know how and I would like to know how. I also would like to know how you posted you query in color and formatted???
Thank You very much for your help and guidance
April 15, 2009 at 10:26 am
Hey TW:
I'm glad to be of assistance. 🙂
Let's use your posted results as sample data. All you have to do is this. Anyone can cut and paste it and they are ready to start coding and testing solutions.
------------------------------------------------------------------------------------------
declare @sample table (CUST_ID varchar(20), EFFDT datetime, SHIP_TYPE_ID varchar(10))
insert into @sample
select '000000000080004' ,'2008-07-24 00:00:00.000','CPU' union all
select '000000000080004' ,'2008-07-25 00:00:00.000','CPU' union all
select '0000080083' ,'2008-10-02 00:00:00.000','CPU' union all
select '0000080118' ,'2008-10-30 00:00:00.000','CPU' union all
select '0000080174' ,'2009-01-27 00:00:00.000','CPU' union all
select '0000080175' ,'2009-01-29 00:00:00.000','CPU' union all
select '0000080219' ,'2009-03-11 00:00:00.000','CPU' union all
select '0000080229' ,'2009-03-18 00:00:00.000','CPU' union all
select '1142' ,'2008-04-07 00:00:00.000','CPU' union all
select '1142' ,'2008-11-12 00:00:00.000','CPU' union all
select '12022' ,'2008-04-07 00:00:00.000','HDNTRK' union all
select '1358' ,'2008-04-07 00:00:00.000','CPU' union all
select '15' ,'2008-04-07 00:00:00.000','HDNTRK' union all
select '15' ,'2009-01-01 00:00:00.000','CPU' union all
select '15' ,'2009-01-02 00:00:00.000','CPU'
--
select * from @sample
------------------------------------------------------------------------------------
Formatting the above code in color is simple. Just wrap it between a [c o d e] and a [/c o d e].
I added spaces between the letters, or the "tags" wouldn't display. You should just type code and /code between the square brackets. Give it a try.
Also, there is a really good article on setting up your questions here[/url].
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply