Retrieving status data from an audit/history table

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)

Retrieving status data from an audit/history table

Author

Topic

liddle_feesh
Starting Member

1 Post

Posted - 2003-02-11 : 05:09:39

Hi Guys! I hope this is the right folder to post in (I'm a newbie), as I have a (simple?!) SQL query that's beginning to cause hair loss! :-

The below table is a log of a product being scanned at 5 stages to provide a logged audit trail. At each stage a monitor will display the number of barcodes at that stage.

I am attempting (!) to derive a list of barcodes in any given status, such that only the data for the barcode is listed, and not repeated.

ID BARCODE DATE/TIME TYPE STAGE DATA
120 POZZ21816 2003-02-10 15:17:16.300 1 1 21816
121 POZZ21833 2003-02-10 13:10:04.300 1 1 21833
122 POZZ21816 2003-02-10 14:10:04.300 1 2 21816
123 POZZ21816 2003-02-10 15:10:04.300 1 3 21816
124 POZZ21816 2003-02-10 15:10:04.300 1 4 21816
125 POZZ21816 2003-02-10 16:10:04.300 1 5 21816
126 MDPP42398 2003-11-02 09:18:30.000 1 1 42398
127 POZZ21816 2003-02-11 16:14:04.300 1 1 21816

I need to display a distinct list of the barcode data at each stage, such that:

At stage (4) - data 21816 is displayed.
At stage (1) - data 21816, 21833, 42398 is displayed.
At stage (5) - data 21816 is displayed.

What would the SQL be for each of those stages?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-02-11 : 09:13:46

select distinct(stage), data from table1

this will give you data in seperate rows...if you need data all on the same row, invvestigate the coalesce command (there are many examples around here)

only issue you need to also checkup is whether or not your answer needs to be across all products, or particular to 1 product....as there doesn't "seem" to be anything on the record to identify a record with a particular product.

Barcode
Starting Member

11 Posts

Posted - 2012-12-03 : 21:42:02

I wanna the similar thing in my warehouse barcodes management. I am using SQL 2005.

barcode@www.aspper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources