CODEWITHSUNDEEP
sql-serversql-server-2008
A.Goutam
A.Goutam

Reputation: 3494

return results on the basis of the first 5 characters

I have a table ORDE_ with column OrderId (int), ordernum varchar(50), pickup varchar(50) , dropoff varchar(50),DADDR varchar(50) ,DRV1NUM varchar(10),READYDATE datetime and i am trying to get the value on following condition where there the first 5 characters of ORDE_.DADDR are the same and there is more than one ORDE_.DRV1NUM for the orders with the same 5 first characters of ORDE_.DADDR

i try the below query but not giving me correct value 

SELECT ordernum, pickup, dropoff,DADDR,DRV1NUM,READYDATE FROM orde_ 
WHERE substring(DADDR,1,5) IN (
  SELECT substring(DADDR,1,5)
  FROM orde_
GROUP BY substring(DADDR,1,5)
HAVING COUNT(*) > 1)
 AND READYDATE ='01/18/2016'
 AND DISPID=56 
 AND DADDR <> ''
 ORDER BY DADDR DESC

can you please help me to resolve this query. Thanks for your time

you can understand like this 

DADDR            DRV1NUM
------           -------
ABCD             1214
ABCD             1214
ABCD             1215
AACD             1216
AACD             1216
AACD             1218
AAAA             12121
AAAA             12121
AAAA             12121

OUTPUT Will be 

DADDR            DRV1NUM
------           -------
ABCD             1215
AACD             1218

because the same DADDR With have more than 1 DRV1NUM

Upvotes: 2

Views: 55

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This will get you all rows where the first 5 characters of DADDR combined with the first 5 characters of DRV1NUM has at least one match.

;WITH CTE as
(
  SELECT
    ordernum, pickup, dropoff,DADDR,DRV1NUM,READYDATE,
    count(*) over (partition by LEFT(DADDR,5), LEFT(DRV1NUM,5)) cnt1,
    count(*) over (partition by LEFT(DADDR,5)) cnt2
  FROM orde_ 
  WHERE
    READYDATE ='2016-01-18'
    AND DISPID=56 
    AND DADDR <> ''
)
SELECT
 ordernum, pickup, dropoff,DADDR,DRV1NUM,READYDATE
FROM CTE
WHERE
  cnt1 = 1
  AND cnt2 > 1

Upvotes: 1

Related Questions