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
 Duplicated Records

Author  Topic 

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-10 : 15:09:41
Hi everyone, I have a sql 2008 view that shows me the available stock, the problem is that I'm receiving duplicated records, I searching by lot number, one lot can have several items so I need display all except the duplicated lot with the same itemcode, example

itemcode|LotNumber|QtyAvailable|PurchasePrice|StartDate |
code-01| G500 | 10 | 8.16 |2014-10-01|
code-01| G500 | 10 | 9.22 |2014-10-03|
code-02| G500 | 20 | 4.50 |2014-10-01|

The code-01 is duplicated on the G500 lot but with diferent price, here I need the newest record like this:


itemcode|LotNumber|QtyAvailable|PurchasePrice|StartDate |
code-01| G500 | 10 | 9.22 |2014-10-03|
code-02| G500 | 20 | 4.50 |2014-10-01|

and my code is this :

SELECT     TOP (100) PERCENT dbo.VK_INVENTARIO3.ItemCode, dbo.VK_INVENTARIO3.Description, dbo.VK_INVENTARIO3.LotNumber, dbo.View_VK_LOT_PRICES.Location, 
dbo.VK_INVENTARIO3.StartDate, dbo.VK_INVENTARIO3.DueDate, dbo.VK_INVENTARIO3.Status, dbo.VK_INVENTARIO3.QtyReceived, dbo.VK_INVENTARIO3.QtyUsed,
dbo.VK_INVENTARIO3.QtyReserved, dbo.VK_INVENTARIO3.QtyAvailable, dbo.View_VK_LOT_PRICES.PurchasePrice, dbo.View_VK_LOT_PRICES.CUR,
dbo.View_VK_LOT_PRICES.XRate
FROM dbo.VK_INVENTARIO3 LEFT OUTER JOIN
dbo.View_VK_LOT_PRICES ON dbo.VK_INVENTARIO3.LotNumber = dbo.View_VK_LOT_PRICES.LotNumber AND
dbo.VK_INVENTARIO3.ItemCode = dbo.View_VK_LOT_PRICES.ItemCode
WHERE (dbo.VK_INVENTARIO3.QtyAvailable <> 0)
ORDER BY dbo.VK_INVENTARIO3.ItemCode


Thanks in advance....

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-10 : 15:22:04
YOU can see from your output that there is a Startdate column with different dates for the two rows with code-01. So they are not really duplicates. You will need to determine which row you want. Is it the one with the newest date?
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-10 : 16:05:15
Yes gbritton, I need the newest record


thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-10 : 16:21:46
OK, what you need to do is take the MAX(startdate) and group by the other columns, then add a HAVING clause:

HAVING StartDate = Max(StartDate)
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-10 : 17:27:42
I followed your recommendations but isn't working, I don't know where the error is because I'still receiving the duplicated row, this is the code :

SELECT     TOP (100) PERCENT dbo.VK_INVENTARIO3.ItemCode, dbo.VK_INVENTARIO3.Description, dbo.VK_INVENTARIO3.LotNumber, dbo.View_VK_LOT_PRICES.Location, 
dbo.VK_INVENTARIO3.StartDate, dbo.VK_INVENTARIO3.DueDate, dbo.VK_INVENTARIO3.Status, dbo.VK_INVENTARIO3.QtyReceived, dbo.VK_INVENTARIO3.QtyUsed,
dbo.VK_INVENTARIO3.QtyReserved, dbo.VK_INVENTARIO3.QtyAvailable, dbo.View_VK_LOT_PRICES.PurchasePrice, dbo.View_VK_LOT_PRICES.CUR,
dbo.View_VK_LOT_PRICES.XRate
FROM dbo.VK_INVENTARIO3 LEFT OUTER JOIN
dbo.View_VK_LOT_PRICES ON dbo.VK_INVENTARIO3.LotNumber = dbo.View_VK_LOT_PRICES.LotNumber AND
dbo.VK_INVENTARIO3.ItemCode = dbo.View_VK_LOT_PRICES.ItemCode
WHERE (dbo.VK_INVENTARIO3.QtyAvailable <> 0)
GROUP BY dbo.VK_INVENTARIO3.ItemCode, dbo.VK_INVENTARIO3.Description, dbo.VK_INVENTARIO3.LotNumber, dbo.View_VK_LOT_PRICES.Location,
dbo.VK_INVENTARIO3.StartDate, dbo.VK_INVENTARIO3.DueDate, dbo.VK_INVENTARIO3.Status, dbo.VK_INVENTARIO3.QtyReceived, dbo.VK_INVENTARIO3.QtyUsed,
dbo.VK_INVENTARIO3.QtyReserved, dbo.VK_INVENTARIO3.QtyAvailable, dbo.View_VK_LOT_PRICES.PurchasePrice, dbo.View_VK_LOT_PRICES.CUR,
dbo.View_VK_LOT_PRICES.XRate

HAVING (StartDate)=MAX(StartDate)
ORDER BY dbo.VK_INVENTARIO3.ItemCode
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-11 : 09:12:52
YOu have dbo.VK_INVENTARIO3.StartDate in the GROUP BY clause. Think about what that means. You need to remove it.
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-15 : 11:30:56
The problem is with the Purchase Price, it's diferent, one lot number has diferent prices but I just need the last record, the diferent fields are the ID and sysmodified date(this just for a few secs), I tried with Distinct and having max but without success, this is a Query Result, I don't need to delete table rows, just the rows showing on the query results.

this is the new code :

SELECT     ID, LotNumber, LotDate, PurchasePrice, sysmodified
FROM dbo.View_VK_LOT_PRICES
WHERE (ItemCode = '8-IRROMPIBLE')
GROUP BY LotDate, ID, LotNumber, PurchasePrice, sysmodified
HAVING (ID = MAX(ID))


I'm receinving this :

ID |LotNumber|PurchasePrice| Sysmodified |
2678543| 29350 | 169.82 |2014-10-30 10:20:06.000|
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

And I need this :

ID |LotNumber|PurchasePrice| Sysmodified |
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 17:06:05
That's not the having clause you were told to use.

you want

HAVING StartDate = Max(StartDate)
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-15 : 21:15:16
That's because the LotDate is practically the same (just 2 secs of diference) I used the ID field because is unique but with the having instruction I had the same results it dosnt works.


SELECT     ID, LotNumber, LotDate, PurchasePrice, sysmodified
FROM dbo.View_VK_LOT_PRICES
WHERE (ItemCode = '8-IRROMPIBLE')
GROUP BY LotDate, ID, LotNumber, PurchasePrice, sysmodified
HAVING (ID = MAX(ID))
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 21:20:32
remove sysmodified from the group by, add it to the selected columns as max(sysmodified) (you don't use it anyway)
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-18 : 11:50:40
Please somebody help me
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 12:05:01
" used the ID field because is unique"

Which is precisely why you are getting multiple rows back
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-18 : 12:22:09
The razon is that some lotnumber have diferent prices so I need just the last price of every lot, so when somebody makes changes on lot prices then id is increase so I'm trying to get the higer id of every lot number :

ID |LotNumber|PurchasePrice| Sysmodified |
2678543| 29350 | 169.82 |2014-10-30 10:20:06.000|
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

And I need this :

ID |LotNumber|PurchasePrice| Sysmodified |
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-18 : 12:22:19
The razon is that some lotnumber have diferent prices so I need just the last price of every lot, so when somebody makes changes on lot prices then id is increase so I'm trying to get the higer id of every lot number :

ID |LotNumber|PurchasePrice| Sysmodified |
2678543| 29350 | 169.82 |2014-10-30 10:20:06.000|
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

And I need this :

ID |LotNumber|PurchasePrice| Sysmodified |
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 12:25:38
Can you post some input data that includes the rows selected by your query. Let's see if we can fix it!
Go to Top of Page

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-22 : 12:54:02
The razon is that some lotnumber have diferent prices so I need just the last price of every lot, so when somebody makes changes on lot prices then id is increase so I'm trying to get the higer id of every lot number :

ID |LotNumber|PurchasePrice| Sysmodified |
2678543| 29350 | 169.82 |2014-10-30 10:20:06.000|
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|

And I need this :

ID |LotNumber|PurchasePrice| Sysmodified |
2678623| 29350 | 161.38 |2014-10-30 10:20:08.000|
2720288| 29838 | 161.38 |2014-11-17 16:07:15.000|
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-22 : 13:17:12
Actually, I asked for input data, that is rows from the dbo.View_VK_LOT_PRICES table before running your query
Go to Top of Page
   

- Advertisement -