Can anyone help me figure out what's missing here?

[Lenar]

I'm trying to query several different tables using INNER JOINs but there's something missing in my code. I get all the results I need except that what I want in this case is that for example, when we make partial shipments, I need to have all the different shipping dates, not just the latest one. Here's the code, maybe someone can help me:

SELECT     somast.fcompany AS CUSTOMER, somast.fstatus AS [SALES ORDER STATUS], SUBSTRING(a.fsokey, 1, 6) AS [SO #], SUBSTRING(a.fsokey, 7, 3) AS [SALES ORDER ITEM #], 
                      a.fpartno AS [PART #], a.frev AS [PART REV], a.forderqty AS [ORDER QTY], sorels.fduedate AS [DUE DATE], a.fshipqty AS [SHIP QTY], 
                      shmast.fshipdate AS [SHIP DATE], a.fshipno AS [SHIPPING #], sorels.funettxnpric AS [SALES ORDER $ UNIT USD], sorels.funetprice AS [SALES ORDER $ UNIT MEX], 
                      sorels.funettxnpric * b.fquantity AS [ORD QTY  $ TOT USD], sorels.funetprice * b.fquantity AS [ORD QTY SO $ TOT MEX], 
                      sorels.funettxnpric * a.fshipqty AS [SHIP QTY $ TOT USD], sorels.funetprice * a.fshipqty AS [SHIP QTY $ TOT MEX], YEAR(b.fduedate) 
                      AS [DUE DATE YEAR], MONTH(b.fduedate) AS [DUE DATE MONTH]
FROM         shmast INNER JOIN
                      shitem a ON shmast.fshipno = a.fshipno RIGHT OUTER JOIN
                      sorels INNER JOIN
                      soitem b ON sorels.fenumber = b.fenumber AND sorels.fsono = b.fsono INNER JOIN
                      somast ON b.fsono = somast.fsono ON SUBSTRING(a.fsokey, 1, 6) + SUBSTRING(a.fsokey, 7, 3) = b.fsono + b.finumber
WHERE     (LEN(a.fsokey) <> 0) AND (YEAR(b.fduedate) > '2010')
ORDER BY sorels.fduedate

Any suggestions will be greatly appreciated.

[BitBlt]

It's kind of hard to tell anything without knowing what your data structures look like, but just from eyeballing you statement it appears that your ship date comes from the shmast table (which I assume is either about a ship or a shipment). I'm going out on a limb here, but is there somewhere on one of the other tables that also contains a ship date? Assuming that there's some order that has been fulfilled by different ships/shipments, you may want to see if there's a ship date elsewhere tied to the line item of the order.

Just guessing, mind you. I hope it helps.

[Lenar]

I'll be looking into it and let you know of any results. Thanks a bunch in advance.

[Lenar]

It's kind of hard to tell anything without knowing what your data structures look like, but just from eyeballing you statement it appears that your ship date comes from the shmast table (which I assume is either about a ship or a shipment). I'm going out on a limb here, but is there somewhere on one of the other tables that also contains a ship date? Assuming that there's some order that has been fulfilled by different ships/shipments, you may want to see if there's a ship date elsewhere tied to the line item of the order.

Just guessing, mind you. I hope it helps.

I'll be looking into it and let you know of any results. Thanks a bunch in advance.

[crishlay]

what error you are getting in your query ?

[Lenar]

what error you are getting in your query ?

No errors. It's just that I can't think of the way to get the results the way I need them. If we ship partially an item the date gets registered, but when we ship the rest of the lot the date refreshes to the new shipping date, but I want to keep the first shipping date on record. I'm calling the results into an excel worksheet. Maybe opening up a new column where I could say "if the date is different than the date registered, register new date" or something like that. What do you think?