Duplicated Records - SQL Server Forums

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?

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-10 : 16:05:15

Yes gbritton, I need the newest record

thanks

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)

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

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.

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|

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)

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))

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)

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-18 : 11:50:40

Please somebody help me

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

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|

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-12-18 : 12:22:19