Problem with TimeStamp Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with TimeStamp Query

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2004-03-03 : 06:12:38

Hi,

I have a problem with a query involving a timestamp. I need to delete duplicate rows for a revenueitem when inserting into a table called omApertureinfo.

I do this by using the following query by deleting the orderstatus that is gereater than the minimum order status. I also use a time stamp in the query. The problem is sometimes it works and deletes the duplicate row and sometimes it does not. Should I be at all using a timestamp with a query like this. I am executing the delete Query straight after I insert into the table:

Any advice would be appreciated.

Thanks

Here is the Delete Query:

DELETE AI
FROM omApertureinfo AI
WHERE AI.chOrderStatus >= ( SELECT MIN(AI1.chOrderStatus) From omApertureinfo AI1
WHERE AI.chShowCd = AI1.chShowCd AND
AI.iShowYear = AI1.iShowYear AND
AI.chRevItemCd = AI1.chRevItemCd AND AI.TS < AI1.TS)
AND chShowCd = @chShowCd AND
iShowYear = @iShowYear

Here is the Insert Statement

INSERT INTO omApertureInfo(chShowCd,
iShowYear,
chRevItemCd,
vchCustomerName,
iOrderNumber,
dBoothLength,
dBoothWidth,
dBoothArea,
mTotalPrice,
chOrderStatus,
dtContractRecvd,
dtDepRecvd,
dtInsertDate,
vchCustomerShortName,
vchIndustrySeg,
iMultipleReadStatus,
iPackage,
iShell)
SELECT chShowCd,
iShowYear,
chRevItemCd,
vchCustomerName,
iOrderNumber,
dBoothLength,
dBoothWidth,
dBoothArea,
mTotalPrice,
chOrderStatus,
dtContractRecvd,
dtDepRecvd,
dtInsertDate,
vchCustomerShortName,
vchIndustrySeg,
iMultipleReadStatus,
iPackage,
iShell
FROM #ApertInfo A
ORDER BY A.chRevItemCd

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 06:51:36

Do you mean timestamp or datetime?

DELETE AI
FROM omApertureinfo AI
WHERE AI.chOrderStatus >= ( SELECT MIN(AI1.chOrderStatus) From omApertureinfo AI1
WHERE AI.chShowCd = AI1.chShowCd AND
AI.iShowYear = AI1.iShowYear AND
AI.chRevItemCd = AI1.chRevItemCd AND AI.TS < AI1.TS)

This takes the min order status for those recs with TS greater than the current TS

so for

chOrderStatus = 1, TS = 1
chOrderStatus = 2, TS = 2

for the first rec the value from the subquery will be 2 so no delete
for the second rec the value from the subquery will be null so no delete

chOrderStatus = 1, TS = 1
chOrderStatus = 2, TS = 1
for the first rec the value from the subquery will be null so no delete
for the second rec the value from the subquery will be null so no delete

Not sure what you really want from this but probably not this.
If you just want the last timestamp entry left then

DELETE AI
FROM omApertureinfo AI
WHERE AI.TS < ( SELECT max(AI1.TS) From omApertureinfo AI1
WHERE AI.chShowCd = AI1.chShowCd AND
AI.iShowYear = AI1.iShowYear AND
AI.chRevItemCd = AI1.chRevItemCd)

or

DELETE AI
FROM omApertureinfo AI
WHERE AI.TS <> ( SELECT max(AI1.TS) From omApertureinfo AI1
WHERE AI.chShowCd = AI1.chShowCd AND
AI.iShowYear = AI1.iShowYear AND
AI.chRevItemCd = AI1.chRevItemCd)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

miranwar
Posting Yak Master

125 Posts

Posted - 2004-03-03 : 10:06:43

Yes thats right I wanted the last timestamp entry. Used your query It works
Thanks very much!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources