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.
| Author |
Topic |
|
ashley.s
Starting Member
9 Posts |
 Posted - 2012-04-27 : 08:48:33
|
| Hi All,This is the an overview of how my DB is.Index Item No Manifest Status Date Time1 AG000001-2 A In Container 2012-04-23 14:51:002 AG000002-2 A In Container 2012-04-23 14:51:003 AG000003-2 A In Container 2012-04-23 14:51:004 AG000001-2 A Out for CV 2012-04-24 15:00:005 AG000001-2 A In after CV 2012-04-24 15:09:006 AG000001-2 A Out for INV 2012-04-24 15:55:00Item can move in and out of stock and I need to keep a count of what is going out and coming in. As you can see, I have 3 fresh items in. One was out at 3pm and then back in again at 3.09 and then out again at 3.55. I have multiples queries looking for Out for CV and out of INV every 5 secs. So at 4pm, it should show that the item AG000001-2 is out for INV because that is the latest status. These are my queries below.Select SerialNumber, max([Index]) from [Cage 1] where Status='Out for Custom Verification' and Delbit='0' and Transbit='1' group by SerialNumberSelect SerialNumber, max([Index]) from [Cage 1] where Status='Out for Invoicing' and Delbit='0' and Transbit='1' group by SerialNumberI guess they are wrong. It should show me that item AG000001-2 is out for INV. That is what I want to do. Is that possible? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-27 : 15:27:03
|
its possibleSELECT t.*FROM table tCROSS APPLY (SELECT MAX(Index) AS Latest FROM table WHERE [Date] =t.[Date] AND Time<= t.[Time] AND [Item No] = t.[Item No] )t1 WHERE [Date] = '2012-04-24'AND [Time] < '16:00'AND t.Index = t1.Latest ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Select Query