0

I have a table that records each time a fee is collected. The composite key is made up of:
- Timestamp
- Bank Number
- Branch Number
- Operator Number
- Cashbox Number

The last four are not always uniquely identifying on their own. (That's why the timestamp is in there.)

When a fee is refunded, I want to delete the row from the database. How would I go about doing this?

C T
  • 1
  • Why don't you simple use the timestamp to select wich record to delete? Are those timestamp unique? – ericpap Jun 04 '14 at 14:11
  • How would I get the timestamp down to the millisecond after it has been saved off? The timestamps are unique: YYYY-MM-DD HH:MM:SS.MSS – C T Jun 04 '14 at 14:16
  • Can you add an Unique Identity field to the table? – ericpap Jun 04 '14 at 14:34
  • That would require a database change, which is above my permission level. – C T Jun 04 '14 at 14:59

1 Answers1

1

Primary key or columns that make the primary key make a unique set. Use the Primary key/ Composite columns to delete the record. 

Delete
FROM <Table Name>
Where Timestamp = @Timestamp
AND [Bank Number] = @BranchNumber
AND [Branch Number] = @[BranchNumber
AND [Operator Number] = @OperatorNumber
AND [Cashbox Number] = @CashboxNumber

Another option: Append the table with two columns Refunded smallint deleted or inactive smallint

Instead of actually deleting the records, make them so you know you received and the record is inactive. This way you have tracking in 1 table without having to do a lot of joins to pull a complete history of what happened over time. - Just a thought.

J. Frank Johnson
  • 24
  • 2
  • Right, I know how to write a delete query. I'm curious how to get that timestamp without having to add a surrogate key. That would require a database change. [Edit]: We have discussed the option to add a reversal flag, but the people above me want it to be deleted. – C T Jun 04 '14 at 14:55
  • Not sure I am fully understanding the question: You want to get the timestamp without using the surrogate key. Are you having difficulty isolating the record you want to delete? When you get a refund, do you have sufficient data attributes to work with, so you can isolate record in the table you mentioned about? – J. Frank Johnson Jun 04 '14 at 15:44
  • I also wanted to add that a timestamp, doesn't actually have anything to do with date time. It's a binary representation of a consecutive number. Read here for some good details: http://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format – J. Frank Johnson Jun 04 '14 at 15:55