Need SQL Query

See more:

SQL-Server

Good day,

I have been trying to write an SQL query that joins to tabels and counts the boxes that were scanned in a goods receiving project.

The two tables:
PackingLists
Containing the actual ShippingLabels (serial number being scanned)
But because this table is actually about the actual parts shipped there are more than 1 shippinglabel listed: (010014530362B 000001 x 17)
No Shipping Label Material Description

15 010014530362B 000001 BN68-02776A MANUAL USERS-03,IB;Comm,English, 250 PC
16 010014530362B 000001 BN96-12469G ASSY CABLE P;PN50C450,FFC CABLE,405MM,30 250 PC
17 010014530362B 000001 BP68-00052B LABEL-00,RATING;CCTV,TETRON PAPER,T0.05, 250 PC
1 010014530362B 000002 BN39-01285A LEAD CONNECTOR;PS50C550G1,UL1007#28,12p 250 PC
1 010014530362B 000003 BH68-00653C LABEL SERIAL-00;ALL MODEL,ART PAPER 90G, 500 PC
1 010014530362B 000004 BN68-02568E LABEL-PDP-POP;P430(50/42),PET,T0.05,100, 250 PC
The shipping label is composed from ProductionNumber + WorkGroup + BoxNo

StuffingLists
Container Work Group Box ID Box No
GLDU7083219 B SM 4

So:
PackingLists:

SELECT CondesedP.ProductionNo, CondesedP.Model, count(CondesedP.Shippinglabel) as Found
FROM
(
SELECT ProductionNo, Model, cast(right(ShippingLabel,6)as int) as BoxNo , ShippingLabel, boxStatus, WeekNo FROM PackingLists
Group By ProductionNo, Model, ShippingLabel, BoxStatus, WeekNo
) CondesedP 
WHERE CondesedP.WeekNo='W35' and CondesedP.BoxStatus='FOUND'
Group By
CondesedP.ProductionNo, CondesedP.Model
Order By 
CondesedP.ProductionNo, CondesedP.Model

Shows:
ProductionNo Model Qty Found
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54
10014530378 UA40C6200URSXA 53

AND:
StuffingLists:

SELECT ProductionNo, Model, count(BoxNo), WeekNo FROM StuffingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model,  WeekNo
Order By ProductionNo, Model

ProductionNo Model Qty
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54

Then I tried to join them together in order to show:
For weekno by specific production number and model how many have been found against the total number of boxes shipped.

SELECT S.ProductionNo, S.Model, Count(S.BoxNo), Count(P.BoxNos) 
FROM
(
SELECT ProductionNo, Model, cast(right(ShippingLabel,6)as int) as BoxNos , ShippingLabel, boxStatus, WeekNo FROM PackingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model, ShippingLabel, BoxStatus, WeekNo
) P 
 left Outer JOIN
(
SELECT ProductionNo, Model, BoxNo, WeekNo FROM StuffingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model, BoxNo, WeekNo
) S
on S.BoxNo = P.BoxNos
WHERE s.Model not like 'C%'
Group By S.ProductionNo, S.Model
Order By S.ProductionNo, S.Model

and i get: almost like it is counting all the weeks
10014530362 PS50C431A2SXA 1792 1792
10014530363 UA46C6200URSXA 656 656
10014530364 LA46C750R2RSXA 753 753
10014530365 PS42C431A2SXA 3453 3453
10014530366 PS42C430A1SXA 3509 3509
10014530367 PS50C430A1SXA 2414 2414
10014530368 PS50C450B1SXA 745 745
10014530369 PS50C550G1RSXA 2760 2760
10014530370 UA32C6900VRSXA 728 728
10014530378 UA40C6200URSXA 717 717

What is wrong with my query.