July 21, 2010 at 9:57 am
I need to pull one recod per load_id I need the min(A.TMS_EXT_REF_ID ) city,state etc.. information that goes with that number but I also need to display the max(A.TMS_EXT_REF_ID ) on the same line
example of what I would like to have for load_id 0000000059
LOAD_ID,NAME1,HM_TRAILER,HM_ARRIVAL_DATE, CITY, STATE, min, max,
59,Norman Cofield,478,NULL,SAN ANTONIO,TX,10, 50,
SELECT DISTINCT A.LOAD_ID
,D.NAME1
,A.HM_TRAILER
,B.HM_ARRIVAL_DATE
,A.CITY
,A.STATE
,A.TMS_EXT_REF_ID
FROM PS_HM_LOAD_STOP_VW A , PS_LOAD_INV B , PS_MEMBER_ADDRESS C , PS_MEMBER_PERSON D
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND C.SETID=D.SETID
AND C.SETID='CORP1'
AND A.BUSINESS_UNIT='HM001'
AND C.SUPPORT_TEAM_MBR=D.SUPPORT_TEAM_MBR
AND A.LOAD_ID=B.LOAD_ID
AND C.SUPPORT_TEAM_MBR=A.SUPPORT_TEAM_MBR
GROUP BY A.LOAD_ID,D.NAME1 ,A.CITY ,A.STATE,A.HM_TRAILER,B.HM_ARRIVAL_DATE,A.TMS_EXT_REF_ID
ORDER BY A.LOAD_ID
CSV data
LOAD_ID,NAME1,HM_TRAILER,HM_ARRIVAL_DATE,CITY,STATE,TMS_EXT_REF_ID
59,Norman Cofield,478,NULL,BELTON,TX,20
59,Norman Cofield,478,NULL,TYLER,TX,40
59,Norman Cofield,478,NULL,WHITE OAK,TX,50
59,Norman Cofield,478,NULL,SAN ANTONIO,TX,10
59,Norman Cofield,478,NULL,DALLAS,TX,30
63,Wallace Evans,405,NULL,LAFAYETTE,LA,20
63,Wallace Evans,405,NULL,GONZALES,LA,40
63,Wallace Evans,405,NULL,GRETNA,LA,50
63,Wallace Evans,405,NULL,GONZALES,LA,30
63,Wallace Evans,405,NULL,NEW ORLEANS,LA,10
64,George Morelan,386,NULL,BETHLEHEM,PA,10
65,Anthony Slaton, ,NULL,LAKE BUTLER,FL,30
65,Anthony Slaton, ,NULL,ORLANDO,FL,20
65,Anthony Slaton, ,NULL,TAMPA,FL,10
66,Danny Cole, ,NULL,JACKSONVILLE,FL,90
66,Danny Cole, ,NULL,DESTIN,FL,40
66,Danny Cole, ,NULL,FT WALTON BEACH,FL,20
66,Danny Cole, ,NULL,PANAMA CITY,FL,70
66,Danny Cole, ,NULL,PENSACOLA,FL,10
66,Danny Cole, ,NULL,JACKSONVILLE,FL,100
66,Danny Cole, ,NULL,PANAMA CITY BEACH,FL,60
66,Danny Cole, ,NULL,TALLAHASSEE,FL,80
66,Danny Cole, ,NULL,FORT WALTON BEACH,FL,30
67,Ronnie Gray,485,NULL,MURPHY,NC,120
67,Ronnie Gray,485,NULL,SANFORD,NC,30
67,Ronnie Gray,485,NULL,MARION,NC,70
67,Ronnie Gray,485,NULL,MARION,NC,80
67,Ronnie Gray,485,NULL,FAYETTEVILLE,NC,20
67,Ronnie Gray,485,NULL,VALDESE,NC,60
67,Ronnie Gray,485,NULL,GASTONIA,NC,50
67,Ronnie Gray,485,NULL,MONROE,NC,10
67,Ronnie Gray,485,NULL,WAYNESVILLE,NC,110
67,Ronnie Gray,485,NULL,ASHEVILLE,NC,100
67,Ronnie Gray,485,NULL,ASHEVILLE,NC,90
67,Ronnie Gray,485,NULL,CHARLOTTE,NC,40
July 21, 2010 at 10:05 am
Without having table defenitions it's not easy to understand what you really want here. DDL with some sample data would help a lot, therefore click the link in my signature to find how the case should be presented to the forum for quicker help and better answer...
July 21, 2010 at 10:07 am
Wht abt the value of City, State??? Wht city/state u want in ur record...???
July 21, 2010 at 11:28 am
City and State should be the City and State that goes with the min stop the 10 or 010 must of the time but not all the time. Just what every in the MIN of A.TMS_EXT_REF_ID
July 21, 2010 at 11:48 am
try this
declare @t table(
LOAD_ID int,
NAME1 varchar(30),
HM_TRAILER int,
HM_ARRIVAL_DATE smalldatetime,
CITY varchar(20),
STATE varchar(2),
TMS_EXT_REF_ID int)
insert into @t
select '59','Norman Cofield','478',Null,'BELTON','TX','20' union all
select '59','Norman Cofield','478',Null,'TYLER','TX','40' union all
select '59','Norman Cofield','478',Null,'WHITE OAK','TX','50' union all
select '59','Norman Cofield','478',Null,'SAN ANTONIO','TX','10' union all
select '59','Norman Cofield','478',Null,'DALLAS','TX','30' union all
select '63','Wallace Evans','405',Null,'LAFAYETTE','LA','20' union all
select '63','Wallace Evans','405',Null,'GONZALES','LA','40' union all
select '63','Wallace Evans','40',Null,'GRETNA','LA','50' union all
select '63','Wallace Evans','40',Null,'GONZALES','LA','30' union all
select '63','Wallace Evans','405',Null,'NEW ORLEANS','LA','10' union all
select '64','George Morelan','386',Null,'BETHLEHEM','PA','10' union all
select '65','Anthony Slaton',' ',Null,'LAKE BUTLER','FL','30' union all
select '65','Anthony Slaton',' ',Null,'ORLANDO','FL','20' union all
select '65','Anthony Slaton',' ',Null,'TAMPA','FL','10' union all
select '66','Danny Cole',' ',Null,'JACKSONVILLE','FL','90' union all
select '66','Danny Cole',' ',Null,'DESTIN','FL','40' union all
select '66','Danny Cole',' ',Null,'FT WALTON BEACH','FL','20' union all
select '66','Danny Cole',' ',Null,'PANAMA CITY','FL','70' union all
select '66','Danny Cole',' ',Null,'PENSACOLA','FL','10' union all
select '66','Danny Cole',' ',Null,'JACKSONVILLE','FL','100' union all
select '66','Danny Cole',' ',Null,'PANAMA CITY BEACH','FL','60' union all
select '66','Danny Cole',' ',Null,'TALLAHASSEE','FL','80' union all
select '66','Danny Cole',' ',Null,'FORT WALTON BEACH','FL','30' union all
select '67','Ronnie Gray','485',Null,'MURPHY','NC','120' union all
select '67','Ronnie Gray','485',Null,'SANFORD','NC','30' union all
select '67','Ronnie Gray','485',Null,'MARION','NC','70' union all
select '67','Ronnie Gray','485',Null,'MARION','NC','80' union all
select '67','Ronnie Gray','485',Null,'FAYETTEVILLE','NC','20' union all
select '67','Ronnie Gray','485',Null,'VALDESE','NC','60' union all
select '67','Ronnie Gray','485',Null,'GASTONIA','NC','50' union all
select '67','Ronnie Gray','485',Null,'MONROE','NC','10' union all
select '67','Ronnie Gray','485',Null,'WAYNESVILLE','NC','110' union all
select '67','Ronnie Gray','485',Null,'ASHEVILLE','NC','100' union all
select '67','Ronnie Gray','485',Null,'ASHEVILLE','NC','90' union all
select '67','Ronnie Gray','485',Null,'CHARLOTTE','NC','40'
select b.Load_ID, NAME1, HM_TRAILER, HM_ARRIVAL_DATE, CITY, STATE , MinTMS_EXT_REF_ID, MaxTMS_EXT_REF_ID
from @t b
inner join
(select Load_ID, min(TMS_EXT_REF_ID) MinTMS_EXT_REF_ID, max(TMS_EXT_REF_ID) MaxTMS_EXT_REF_ID
from @t
group by Load_ID) a
on a.Load_ID = b.Load_ID and a.MinTMS_EXT_REF_ID = b.TMS_EXT_REF_ID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 12:00 pm
If your data is such that you could possibly get ties for your minimum value, you can also do this:
;WITH cteTemp AS
(
SELECT LOAD_ID,
NAME1,
HM_TRAILER,
CITY,
STATE,
TMS_EXT_REF_ID,
ROW_NUMBER() OVER(PARTITION BY LOAD_ID ORDER BY TMS_EXT_REF_ID ASC) RN_asc,
ROW_NUMBER() OVER(PARTITION BY LOAD_ID ORDER BY TMS_EXT_REF_ID DESC) RN_desc
FROM #Temp
)
SELECT c_min.LOAD_ID,
c_min.NAME1,
c_min.HM_TRAILER,
c_min.CITY,
c_min.STATE,
c_min.TMS_EXT_REF_ID min_REF_ID,
c_max.TMS_EXT_REF_ID max_REF_ID
FROM cteTemp c_min
JOIN cteTemp c_max
ON c_max.LOAD_ID = c_min.LOAD_ID
AND c_max.RN_desc = 1
WHERE c_min.RN_asc = 1
You would just need to modify the ROW_NUMBER functions' ORDER BY to establish how you would want to break ties.
July 21, 2010 at 12:21 pm
I am sorry but I am way lost.....
SELECT b.Load_ID
, NAME1
, HM_TRAILER
, HM_ARRIVAL_DATE
, CITY
, STATE
, MinTMS_EXT_REF_ID
, MaxTMS_EXT_REF_ID
FROM @t b INNER JOIN (
SELECT Load_ID
, MIN(TMS_EXT_REF_ID) MinTMS_EXT_REF_ID
, MAX(TMS_EXT_REF_ID) MaxTMS_EXT_REF_ID
FROM @t
GROUP BY Load_ID) a ON a.Load_ID = b.Load_ID
AND a.MinTMS_EXT_REF_ID = b.TMS_EXT_REF_ID
I have gotten this for in my select but I am not sure how to get one row for each load and show the information that goes with the min(A.TMS_EXT_REF_ID) and display the
max(A.TMS_EXT_REF_ID) on that same line...This will end up as a view that I will report off of.
as it is now I return 4 million rows of data and would like to cut down on that.
SELECT DISTINCT A.LOAD_ID
,D.NAME1
,A.HM_TRAILER
,B.HM_ARRIVAL_DATE
,A.CITY
,A.STATE
,A.TMS_EXT_REF_ID
FROM PS_HM_LOAD_STOP_VW A , PS_LOAD_INV B , PS_MEMBER_ADDRESS C , PS_MEMBER_PERSON D
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND C.SETID=D.SETID
AND C.SETID='CORP1'
AND A.BUSINESS_UNIT='HM001'
AND C.SUPPORT_TEAM_MBR=A.SUPPORT_TEAM_MBR
AND C.SUPPORT_TEAM_MBR=D.SUPPORT_TEAM_MBR
AND A.LOAD_ID=B.LOAD_ID
GROUP BY A.LOAD_ID,D.NAME1 ,A.CITY ,A.STATE,A.HM_TRAILER,B.HM_ARRIVAL_DATE,A.TMS_EXT_REF_ID
ORDER BY A.LOAD_ID
July 21, 2010 at 12:28 pm
Not sure what you are asking. The Code I gave you will return the Load_ID, Min and Max as well as all associated data with the Min record all on one line. Run the sample data and code that I provided and you can see the row for Load_ID 59 matches what your expectations were
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2010 at 1:57 pm
I do not know how to convert your sql into a usable view on my server.
July 22, 2010 at 6:17 am
I used the table names you provided in the original to try to guestimate the joins for your view. Try this:
Create View OneRecordPerLoadID as
select A.Load_ID, D.NAME1, A.HM_TRAILER, A.HM_ARRIVAL_DATE, A.CITY, A.STATE ,
V.MinTMS_EXT_REF_ID, V.MaxTMS_EXT_REF_ID
from PS_HM_LOAD_STOP_VW A
inner join PS_LOAD_INV B
on A.BUSINESS_UNIT=B.BUSINESS_UNIT
and A.LOAD_ID=B.LOAD_ID
inner join PS_MEMBER_ADDRESS C
on C.SUPPORT_TEAM_MBR=A.SUPPORT_TEAM_MBR
AND C.SETID='CORP1'
inner join PS_MEMBER_PERSON D
on C.SUPPORT_TEAM_MBR=D.SUPPORT_TEAM_MBR
and C.SETID=D.SETID
inner join
(select Load_ID, min(TMS_EXT_REF_ID) MinTMS_EXT_REF_ID, max(TMS_EXT_REF_ID) MaxTMS_EXT_REF_ID
from PS_HM_LOAD_STOP_VW
group by Load_ID) V
on v.Load_ID = A.Load_ID and V.MinTMS_EXT_REF_ID = A.TMS_EXT_REF_ID
Where A.BUSINESS_UNIT='HM001'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply