Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query advice on removing duplicate fields Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 4:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
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 !
:)

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

Post #1536711
Posted Friday, January 31, 2014 4:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
can you share the sample data and expected output, its still a bit unclear
Post #1536713
Posted Friday, January 31, 2014 4:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
yes i do think i have ex pained it quit poorly !

ill see if i can get some of the data in
Post #1536718
Posted Friday, January 31, 2014 5:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
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
Post #1536723
Posted Friday, January 31, 2014 5:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
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 ?
Post #1536732
Posted Friday, January 31, 2014 5:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
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
Post #1536734
Posted Friday, January 31, 2014 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
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 !
Post #1536737
Posted Friday, January 31, 2014 6:05 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
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.
Post #1536738
Posted Friday, January 31, 2014 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:05 AM
Points: 147, Visits: 328
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!
Post #1536742
Posted Friday, January 31, 2014 6:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
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
Post #1536746
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse