Select last occurrence of a date(s)
Need to return select list containing each [Equip#] where the last date entered is [CheckOutDate],
or that is those items currently checked out.
See Code Pane.
or that is those items currently checked out.
See Code Pane.
Table structure;
CREATE TABLE [dbo].[EquipmentHistory](
[Rec#] [int] IDENTITY(1,1) NOT NULL,
[Receipt#] [int] NULL,
[Equip#] [nvarchar](50) NULL,
[Qty] [int] NULL DEFAULT ((0)),
[Emp#] [varchar](6) DEFAULT (''),
[CheckOutDate] [datetime] NULL,
[CheckInDate] [datetime] NULL,
) ON [PRIMARY]
Sample Data;
Rec# EMP# Equip# CheckOutDate CheckInDate Check
----------- ------ -------------------------------------------------- ----------------------- ----------------------- -----
137890 1145 4854 2009-10-26 10:29:01.680 NULL OUT
137811 1145 4855 NULL 2009-10-24 11:42:36.283 IN
137809 1145 4855 2009-10-24 11:39:02.860 NULL OUT
137777 1145 5055 NULL 2009-10-23 19:20:37.820 IN
137760 1145 5055 2009-10-23 16:19:01.937 NULL OUT
137465 1145 4949 2009-10-22 08:04:13.933 NULL OUT
136832 1145 5919 NULL 2009-10-16 11:10:19.957 IN
136831 1145 6025 NULL 2009-10-16 11:10:13.113 IN
136830 1145 6021 NULL 2009-10-16 11:10:06.063 IN
136483 1145 4843 NULL 2009-10-14 15:59:50.610 IN
136482 1145 4477 NULL 2009-10-14 15:59:32.877 IN
136472 1145 4843 2009-10-14 15:12:45.610 NULL OUT
136471 1145 4477 2009-10-14 15:12:34.063 NULL OUT
136418 1145 4482 2009-10-14 10:54:25.340 NULL OUT
136325 1145 4126 NULL 2009-10-14 07:43:49.467 IN
136324 1145 6166 NULL 2009-10-14 07:43:32.357 IN
136206 1145 4126 2009-10-13 12:47:26.793 NULL OUT
136205 1145 6166 2009-10-13 12:47:19.887 NULL OUT
ASKER
aneeshattingal:
This query returns only the last record. What I am shooting for is; each Equip# that has been checked out and not yet checked in by a specified Emp#.
This query returns only the last record. What I am shooting for is; each Equip# that has been checked out and not yet checked in by a specified Emp#.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
from EqupmentHistory e
Where CheckOutDate = (SELECT Max(CheckOutDate) from EquipmentHistory WHERE [Equip#] = e. [Equip#] AND [Check] = 'Out' )