Finding those Barcodes - 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

General SQL Server Forums

New to SQL Server Programming

Finding those Barcodes

Author

Topic

cxmoore
Starting Member

9 Posts

Posted - 2009-05-20 : 08:48:35

PLEASE SEE IF YOU CAN HELP ME RESOLVE MY ROAD BLOCK

1. SELECT each DISTINCT record FROM SortD.MissionComplete.Mission with the GREATEST (TIMESEQUENCE) for each DISTINCT mission from the missions complete table.

2. Using the records that were collected during the above query, COUNT
missions WHERE (Using multiple fields and based on the condition of the fields I can determine if the contain is in route from point A to B, or the mission is completed

I have something like this

SELECT COUNT (Mission)
FROM SortD.MissionComplete
Where IntendedLogicalDestinationID = 'C6RF' AND ScannerID='23'AND
EXISTS
(SELECT Mission,MAX(TimeSequence) ScannerID, IntendedLogicalDestinationID
FROM SortD.MissionComplete
WHERE(DateDiff(Day,SortD.MissionComplete.Timestamp,GETDATE())=0)AND NOT Mission Like'[9999]%'
AND NOT Mission Like '[?????????]%'
GROUP BY Mission,IntendedLogicalDestinationID, TimeSequence, ScannerID)

NOTE: The MissionComplete Table will have multilple instances of the same barcode but each of the records will have a UNIQUE “TimeSequence”. I’m looking to only grab the record that is the newest for each barcode. Then filter based on the values in a few specified fields not shown here but I can send if needed.

Thanks for all your help in resolving my problem.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-20 : 10:48:49

Maybe you can give sample table, data and wanted output.
Maybe you tell us the used Version of SQL Server.
Maybe you should not group by TimeSequence...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-20 : 10:55:44

Also, question mark is NOT a single character wild card in SQL Server.
Underscore is.

E 12°55'05.63"
N 56°04'39.26"

cxmoore
Starting Member

9 Posts

Posted - 2009-05-20 : 11:58:11

I'm not sure you will get a copy of this that is usable? I can email you the copy if you tell me where to email as the email function on this site is not up and running? That is the msg that I have pop-up

This is the table minus a few fields that are not of any real concern. As you can see I have the same barcode several times but I need to grab only the newest for each in the sub-query and then in the following query it links to do a COUNT on the missions based on a few conditions in the WHERE. Please let me know if I can send anything else that might help. Thanks

TimeStamp Mission Manual WaveID OrderID ShipmentID IntendedLogicalDestinationID RouteID TimeSequence ScannerID
5/20/2009 11:18 00000506201200740454 No 10543 None None C6RF None 46992207 3
5/20/2009 11:23 00000506201200740454 No 10543 None None C6RF None 46992976 3
5/20/2009 11:23 00000506201200740454 No 10543 None None C6RF None 46992947 3
5/20/2009 11:18 00000506201200740454 No 10543 None None C6RF None 46992187 3
5/20/2009 11:10 00000506201200740454 No 10543 None None C6RF None 46991173 3
5/20/2009 11:05 00000506201200740454 No 10543 None None C6RF None 46990567 3
5/20/2009 11:00 00000506201200740997 No 10543 None None C6RF None 46989636 3
5/20/2009 11:04 00000506201200740997 No 10543 None None C6RF None 46990394 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990973 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46992021 3
5/20/2009 11:00 00000506201200741239 No None None None 52 None 46989588 4
5/20/2009 11:04 00000506201200741239 No 10543 None None C6RF None 46990369 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990952 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46992002 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46991982 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990939 3
5/20/2009 11:04 00000506201200741239 No 10543 None None C6RF None 46990350 3

cxmoore
Starting Member

9 Posts

Posted - 2009-05-20 : 12:00:22

TimeStamp Mission Manual WaveID OrderID ShipmentID IntendedLogicalDestinationID RouteID TimeSequence ScannerID
5/20/2009 11:18 00000506201200740454 No 10543 None None C6RF None 46992207 3
5/20/2009 11:23 00000506201200740454 No 10543 None None C6RF None 46992976 3
5/20/2009 11:23 00000506201200740454 No 10543 None None C6RF None 46992947 3
5/20/2009 11:18 00000506201200740454 No 10543 None None C6RF None 46992187 3
5/20/2009 11:10 00000506201200740454 No 10543 None None C6RF None 46991173 3
5/20/2009 11:05 00000506201200740454 No 10543 None None C6RF None 46990567 3
5/20/2009 11:00 00000506201200740997 No 10543 None None C6RF None 46989636 3
5/20/2009 11:04 00000506201200740997 No 10543 None None C6RF None 46990394 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990973 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46992021 3
5/20/2009 11:00 00000506201200741239 No None None None 52 None 46989588 4
5/20/2009 11:04 00000506201200741239 No 10543 None None C6RF None 46990369 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990952 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46992002 3
5/20/2009 11:17 00000506201200741239 No 10543 None None C6RF None 46991982 3
5/20/2009 11:09 00000506201200741239 No 10543 None None C6RF None 46990939 3
5/20/2009 11:04 00000506201200741239 No 10543 None None C6RF None 46990350 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources