• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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.
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

Open in new window

0
Greg Rowland
Asked:
Greg Rowland
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
not sure what exactly you are looking for

SELECT *
from EqupmentHistory e
Where  CheckOutDate = (SELECT Max(CheckOutDate) from EquipmentHistory WHERE [Equip#] =  e. [Equip#]  AND  [Check] = 'Out' )
0
 
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAuthor Commented:
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#.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT *
from EqupmentHistory e
Where  [Check] = 'Out'  and not exists (SELECT 1 from EquipmentHistory WHERE [Equip#] =  e.[Equip#] and CheckInDate IS not null     )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now