Click here to monitor SSC
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
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 413
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2596
can you share the sample data and expected output, its still a bit unclear
dopydb
dopydb
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

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

ill see if i can get some of the data in
dopydb
dopydb
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 413
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2596
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
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 413
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
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 413
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2596
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
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 413
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2596
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