SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query advice on removing duplicate fields


Query advice on removing duplicate fields

Author
Message
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
hi all

i have a query - which i may be best going back to the start on, but ill ask first if someone has a solution for me !
Smile

the output is like..
    Load CUSTOMER A CUSTOMER B
    AL74232 NULL 0 BQ395 20
    AL74446 EV007 2 WE092 20
    AL74446 HO702 1 WE092 20
    AL74526 ST209 6 ST209 20
    AL74891 WE092 5 WE092 20



basically every time i have more than 1 customer having an A or B per load, then obviously enough ill get a duplicate field

eg AL74446
Customer H0702 has 1 A and EV007 has 2 A's, but now i have WE092 repeating with 20 B, hence totals will be wrong

my code is

select distinct ln.LoadNumber,z.CUSTOMER, isnull(PickPalletCount,0)as PickPalletCount  
,q.CUSTOMER,isnull(con_count,0) as con_count
from LoadToPickNumber ln

------
------ custom pallets
left join(
select DISTINCT LoadNumber,PR.CUSTOMER,
COUNT(DISTINCT CUSTOM_PALLET_ID)as PickPalletCount from LoadToPickNumber lp
LEFT JOIN CUSTOM_PALLETS cp on cp.PICK_NO = lp.PICK_NO
LEFT JOIN PICK_HEADER PH ON PH.PICK_NO = lp.PICK_NO
LEFT JOIN PICK_RELEASED_ORDERS PR ON PR.PICKING_HEADER_ID = PH.PICKING_HEADER_ID
WHERE CUSTOM_PALLET_ID is not null
GROUP BY LoadNumber,PR.CUSTOMER)z on z.LoadNumber = ln.LoadNumber

----
---- full containers
Left join(
select ln.LoadNumber,CUSTOMER,count(distinct CONTAINER_ID)as con_count from LoadToPickNumber ln
join(
SELECT distinct PH.PICK_NO,CONTAINER_ID,LoadNumber,PO.CUSTOMER FROM PICK_RELEASED_ORDERS PO
JOIN PICK_DETAIL PKD ON PKD.PICKING_HEADER_ID = PO.PICKING_HEADER_ID AND PKD.RELEASE_ID = PO.RELEASE_ID
JOIN PICK_HEADER PH ON PH.PICKING_HEADER_ID = PKD.PICKING_HEADER_ID
left join LoadToPickNumber LP on LP.PICK_NO = PH.PICK_NO
WHERE WAREHOUSE = 'PE' and WHOLE_CONTAINER <>'0' and CONTAINER_ID is not null )q on q.LoadNumber = ln.LoadNumber
group by ln.LoadNumber,CUSTOMER) q on q.LoadNumber = ln.LoadNumber

left join SorDetail s on s.LoadNumber = ln.LoadNumber
left Join SorMaster m on m.SalesOrder = s.SalesOrder

WHERE ln.LoadNumber like 'AL%%' and OrderDate > = '2014-01-01'
order by con_count desc


twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5410 Visits: 2692
can you share the sample data and expected output, its still a bit unclear
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
yes i do think i have ex pained it quit poorly !

ill see if i can get some of the data in
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
https://skydrive.live.com/redir?resid=A27225A35A5E094C!169&authkey=!ANdqlkPfcG7dymU&ithint=file%2c.xlsx

i have broken down the results of each sub query in this

do you understand my issue?

m
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5410 Visits: 2692
its a vehicle information system rite, which means one number can be associated with multiple customers.
and its can have standard number and customer number. if this understanding is rite then

(Data you shared after joining the both subqueries)

LoadNumber CUSTOMER CUSTOMER PickPalletCount con_count
AL75152 CD113 AS029 2 21
AL75152 GI005 AS029 3 21


What is your expected output ?
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
not far off, its order picking data grouped by distribution load

each load no can consist of orders for 1 or more customers,
some of these orders have part pallets of multiple product types - pickpalletcount
some of the orders have full pallets of 1 product - con_count,
and some orders have a combination of both, i am trying to show the breakdown perload/customer

the out put id like is

LoadNumber - CUSTOMER - - PickPalletCount - Customer - con_count
AL75152 - CD113     2 - AS029 21
AL75152 - GI005   - 3 - null 0

where i dont have a second AS029 record of 21, as this is only repeating because i have 2 different customers with pick pallets
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
i think i am probably best going back to the drawing board, the query wasnt written with the customer break down in mind, so i could easily sum per load, but managers have a habit of asking awkward questions ! w00t
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5410 Visits: 2692
Below code is to demonstrate the working which can be performed on the original query



DECLARE @SubQuery1 Table (
LoadNumber varchar(20)
,CUSTOMER varchar(20)
,PickPalletCount int
)
DECLARE @SubQuery2 TABLE(
LoadNumber varchar(20)
,CUSTOMER varchar(20)
,con_count INT
)

INSERT INTO @SubQuery1
Select 'AL75152','CD113', 2 UNION ALL
Select 'AL75152','GI005', 3


INSERT INTO @SubQuery2
SELECT 'AL75152','AS029', 21

SELECT X.LoadNumber, X.CUSTOMER1, X.PickPalletCount
, CASE WHEN X.ROWNUM = 1 THEN X.CUSTOMER2 ELSE NULL END AS CUSTOMER2
, CASE WHEN X.ROWNUM = 1 THEN X.con_count ELSE NULL END AS con_count
FROM (
SELECT A.LoadNumber, A.CUSTOMER AS CUSTOMER1, A.PickPalletCount, B.CUSTOMER AS CUSTOMER2
, B.con_count
, ROW_NUMBER() OVER (PARTITION BY A.LoadNumber, B.LoadNumber ORDER BY A.LoadNumber) ROWNUM
FROM @SubQuery1 A
JOIN @SubQuery2 B ON A.LoadNumber = B.LoadNumber
) X



Hope it helps.
dopydb
dopydb
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1338 Visits: 550
cheers for , this i must have a play with it to see if i can use it!

the problem is i only want the customer record ignored if it is repeated in the same load! hence just selecting row number =1 would ignore potential correct results

though i guess i could partition by load and customer!
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5410 Visits: 2692
dopydb (1/31/2014)
cheers for , this i must have a play with it to see if i can use it!

the problem is i only want the customer record ignored if it is repeated in the same load! hence just selecting row number =1 would ignore potential correct results

though i guess i could partition by load and customer!


its better because only you knows what you exactly want, its just to give you an idea :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search