SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retrieving status data from an audit/history table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

liddle_feesh
Starting Member

1 Posts

Posted - 02/11/2003 :  05:09:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 02/11/2003 :  09:13:46  Show Profile  Reply with Quote
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.

Go to Top of Page

Barcode
Starting Member

11 Posts

Posted - 12/03/2012 :  21:42:02  Show Profile  Reply with Quote
I wanna the similar thing in my warehouse barcodes management. I am using SQL 2005.

barcode@www.aspper.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000