Basic join problem - newb level

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic join problem - newb level

Author

Topic

danver
Starting Member

3 Posts

Posted - 2009-09-14 : 03:54:26

Hello All,

Can someone help a newb

The first script below works however I am having trouble adding a basic join on to it.

-- get the latest date for an inventory item.
SELECT DOCNUMBR,ITEMNMBR,DOCDATE,TRXQTY,UOFM,UNITCOST,TRXLOCTN
FROM IV30300 m
WHERE DOCDATE =
(
SELECT MAX (DOCDATE)
FROM IV30300
WHERE ITEMNMBR = m.ITEMNMBR
)
and Trxloctn = 'Fleet'
ORDER BY ITEMNMBR;

I need to join to a table called IV00102 with the key field being ITEMNMBR and grabbing the QTYONHND field. I have tried the below script but it is returning too many values.(ie alot of duplications)

Is anybody able to identify where I am going wrong?

SELECT DOCNUMBR,m.ITEMNMBR,DOCDATE,TRXQTY,UOFM,UNITCOST,TRXLOCTN,
QTYONHND
FROM IV30300 m
INNER JOIN IV00102
ON m.ITEMNMBR = IV00102.ITEMNMBR
WHERE DOCDATE =
(
SELECT MAX (DOCDATE)
FROM IV30300
WHERE ITEMNMBR = m.ITEMNMBR
)
and Trxloctn = 'Fleet'
ORDER BY ITEMNMBR;

Thanks
Daniel

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-14 : 04:34:56

Now, i'm just guessing, but I would say that you have a one to many relationship between IV30300 and IV00102.

If this is the case, is there a particular record you want to get (such as the one for each itemnumber with the same date) or do you need to sum the qtyonhnd value?!?

Please provide sample data (nneds to be the same type of data as the real table) and expected results.

danver
Starting Member

3 Posts

Posted - 2009-09-14 : 08:33:57

Hello RickD,

The IV00300 table stores transactions for the various ITEMNMBR. I want to grab the latest transaction for each ITEMNMBR.

Which I can using

SELECT DOCNUMBR,ITEMNMBR,DOCDATE,TRXQTY,UOFM,UNITCOST,TRXLOCTN
FROM IV30300 m
WHERE DOCDATE =
(
SELECT MAX (DOCDATE)
FROM IV30300
WHERE ITEMNMBR = m.ITEMNMBR
)
and Trxloctn = 'Fleet'
ORDER BY ITEMNMBR;

The output is: (ie the latest dated transaction for each ITEMNMBR)
DOCNUMBR ITEMNMBR DOCDATE TRXQTY UOFM UNITCOST TRXLOCTN
0000016 11000 2007-11-30 00:00:00.000 -1.00000 Each 1 0.00000 FLEET
0000020 11001 2009-02-28 00:00:00.000 -1.00000 Each 1 49.04000 FLEET
0000016 11002 2007-11-30 00:00:00.000 -4.00000 Each 1 31.29000 FLEET

Now I need to get various columns from IV00102 which stores the summary data for each item. ie Qty on Hand, Qty Allocated etc. The Key field is ITEMNMBR.

IV00102 has the following format
ITEMNMBR LOCNCODE RCRDTYPE LSRCPTDT QTYONORD QTYONHND
11000 FLEET 2 1900-01-01 00:00:00.000 0.00000 2.00000
11001 FLEET 2 2001-08-31 00:00:00.000 0.00000 0.00000

Now I need the output to look like: (using ITEMNMBR 11000 as an example)
DOCNUMBR ITEMNMBR DOCDATE TRXQTY UOFM UNITCOST TRXLOCTN QTYONHND
0000016 11000 2007-11-30 00:00:00.000 -1.00000 Each 1 0.00000 FLEET 2.00000

Sorry, I hope this a bit clearer now.

Thanks
Daniel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 11:29:28

[code]SELECT m1.DOCNUMBR, m1.ITEMNMBR, m1.DOCDATE, m1.TRXQTY, m1.UOFM, m1.UNITCOST,m1.TRXLOCTN, i.QTYONHND
FROM IV30300 m1
INNER JOIN (SELECT ITEMNMBR,MAX(DOCDATE) AS Latest
FROM IV30300
GROUP BY ITEMNMBR)m2
ON m2.ITEMNMBR=m1.ITEMNMBR
AND m2.Latest= m1.DOCDATE
INNER JOIN IV00102 i
ON m1.ITEMNMBR = i.ITEMNMBR
WHERE Trxloctn = 'Fleet'
ORDER BY m1.ITEMNMBR[/code]

danver
Starting Member

3 Posts

Posted - 2009-09-15 : 02:48:30

Hello visakh16,

I tried your script but I get the same duplication issues as my script.

Thanks for trying.

Daniel

raj4582
Starting Member

16 Posts

Posted - 2009-09-15 : 04:02:32

The above query posted by visakh16 seems correct to me.
Check that there is no data isue.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-15 : 04:33:54

The only way you will get duplicates is if you have more than 1 ITEMNUMBR for the same DOCDATE, if this is the case and you don't use the time part of the date, then you have issues with your data as how are you supposed to know which is the latest?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources