January 31, 2014 at 4:42 am
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..
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
January 31, 2014 at 4:50 am
can you share the sample data and expected output, its still a bit unclear
January 31, 2014 at 4:58 am
yes i do think i have ex pained it quit poorly !
ill see if i can get some of the data in
January 31, 2014 at 5:07 am
i have broken down the results of each sub query in this
do you understand my issue?
m
January 31, 2014 at 5:31 am
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 CUSTOMERPickPalletCountcon_count
AL75152 CD113 AS029 2 21
AL75152 GI005 AS029 3 21
What is your expected output ?
January 31, 2014 at 5:50 am
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
January 31, 2014 at 6:04 am
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:
January 31, 2014 at 6:05 am
Below code is to demonstrate the working which can be performed on the original query
DECLARE @SubQuery1 Table (
LoadNumbervarchar(20)
,CUSTOMERvarchar(20)
,PickPalletCountint
)
DECLARE @SubQuery2 TABLE(
LoadNumbervarchar(20)
,CUSTOMERvarchar(20)
,con_countINT
)
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.
January 31, 2014 at 6:14 am
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!
January 31, 2014 at 6:19 am
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 🙂
January 31, 2014 at 6:25 am
cheers for help, i think you have helped kick my brain into operation!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply