Trouble getting Lowest date in query

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trouble getting Lowest date in query

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-05-19 : 18:18:18

Hi,

I have the following query which extracts the contents of a table rm00300 into a temporary table lot which groups based on itemnmbr,lotnumbr, locncode, qtytyp and dtseqnum. The problem is that I want to pick up the lowest dtseqnum (date sequence number), but the query is not doing this.

DECLARE @intErrorCode VARCHAR(10);

SELECT itemnmbr as itemnmbr1,
lotnumbr as lotnmbr1,
min(daterecd) as receiptdate1,
sum(qtyrecvd) as qtyrecvd1,
IDENTITY(smallint, 1, 1) as dex_row_id1,
locncode as locncode1,
sum(qtysold) as qtysold1,
qtytype as qtytype1,
dtseqnum as dtseqnum1
into lot
FROM iv00300
group by itemnmbr,lotnumbr, locncode, qtytype,dtseqnum

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode = 0)
delete iv00300

SET IDENTITY_INSERT IV00300 ON

insert into iv00300 (itemnmbr, lotnumbr, daterecd,qtyrecvd,dex_row_id,locncode,qtysold,qtytype,dtseqnum)
select itemnmbr1, lotnmbr1, receiptdate1,qtyrecvd1,dex_row_id1,locncode1,qtysold1,qtytype1,dtseqnum1
from lot

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode = 0)
drop table lot

SET IDENTITY_INSERT IV00300 OFF

Can anyone help ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 20:03:32

[code]
SELECT itemnmbr as itemnmbr1,
lotnumbr as lotnmbr1,
min(daterecd) as receiptdate1,
sum(qtyrecvd) as qtyrecvd1,
IDENTITY(smallint, 1, 1) as dex_row_id1,
locncode as locncode1,
sum(qtysold) as qtysold1,
qtytype as qtytype1,
min(dtseqnum) as dtseqnum1
into lot
FROM iv00300
group by itemnmbr,lotnumbr, locncode, qtytype ~~,dtseqnum~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-06-09 : 19:00:31

Hi,

I have modified the query and used your suggestion. Howeever, when I run the query, it gives me a Violation of PRIMARY KEY constraint 'PK_IV00300'. Cannot insert duplicate key in object 'IV00300'.
The statement has been terminated. error message. The primary keys on the table are itemnmbr, locncode, daterecd, dtseqnum and qtytype. The dtseqnum seems to be problem - it is not grouping by it. What am I doing wrong ?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-06-09 : 19:10:16

You aren't getting that error on your select query. You are getting the error on the insert.

Your description of your scripts says it is extracting data from rm00300, however that is not what you posted. I think you have a table name incorrect in either the select or the insert.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-06-09 : 19:32:47

This is my query :

DECLARE @intErrorCode VARCHAR(10);
drop table iv00300temp
SELECT
ITEMNMBR AS ITEMNMBR,
LOCNCODE AS LOCNCODE,
DATERECD AS DATERECD,
DTSEQNUM AS DTSEQNUM,
LOTNUMBR AS LOTNUMBR,
QTYRECVD AS QTYRECVD,
QTYSOLD AS QTYSOLD,
ATYALLOC AS ATYALLOC,
UNITCOST AS UNITCOST,
RCTSEQNM AS RCTSEQNM,
vndrnmbr as vndrnmbr,
ltnumsld as ltnumsld,
qtytype as qtytype,
bin as bin,
mfgdate as mfgdate,
expndate as expndate,
dex_row_id as dex_row_id
into iv00300temp
from iv00300

SELECT itemnmbr as itemnmbr1,
lotnumbr as lotnmbr1,
min(daterecd) as receiptdate1,
sum(qtyrecvd) as qtyrecvd1,
IDENTITY(smallint, 1, 1) as dex_row_id1,
locncode as locncode1,
sum(qtysold) as qtysold1,
sum(atyalloc) as atyalloc1,
qtytype as qtytype1,
min(unitcost) as unitcost1,
min(rctseqnm) as rctseqnm1,
min(dtseqnum) as dtseqnum1
into lot
FROM iv00300
group by itemnmbr,lotnumbr, locncode, qtytype

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode = 0)
delete iv00300

SET IDENTITY_INSERT IV00300 ON

insert into iv00300 (itemnmbr, lotnumbr, daterecd,qtyrecvd,dex_row_id,locncode,qtysold,qtytype,dtseqnum,unitcost,rctseqnm,atyalloc)
select itemnmbr1, lotnmbr1, receiptdate1,qtyrecvd1,dex_row_id1,locncode1,qtysold1,qtytype1,dtseqnum1,unitcost1,rctseqnm1,atyalloc1
from lot

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode = 0)
drop table lot

SET IDENTITY_INSERT IV00300 OFF

I get the violation of primary key error when I run this query sometimes. It is making a copy of the iv00300 table into a iv00300temp table, and then grouping by itemnmbr,lotnumbr, locncode and qtytype into a table called lot. It then deletes the iv00300 table and then inserts from the lot table into the iv00300 table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources