I am having a problem getting records between two date columns. My table is shown below.
SAP_ID JoinDate LeaveDate
1 2006-01-23 00:00:00.000 2006-04-12 00:00:00.000
2 2006-09-04 00:00:00.000 2007-10-10 00:00:00.000
3 2006-10-03 00:00:00.000 2007-01-01 00:00:00.000
4 2007-12-04 00:00:00.000 2007-10-10 00:00:00.000
From asp page i am getting joindate and leavedate . I need to show records between joindate and leavedate.
For ex:
If
JoinDate=2006-01-01
LeaveDate=2007-01-01
then
report should show 1,2,3 SAP No's. But My Query showing only 1, 3. Here is the my query.
select sap_id from tblsap where joindate >= '2006-01-01' and leavedate <='2007-01-01'
I am not able to figure out the problem. Can some one please post suggestions to my problem.
Rajesh
The row 3 has leaveDate greater than 2007-01-01. You can AND logic in where clause and that row doesn't match where clause. So your query won't catch this row. You need understand requirement and make a little change your code.
SAP_ID JoinDate LeaveDate
3 2006-10-03 00:00:00.000 2007-01-01 00:00:00.000
select sap_id from tblsap where joindate >= '2006-01-01' and leavedate <='2007-01-01'
|||
As I understand the problem, you want rows where the 'active' period included any part of the year 2006.
I think that this should accomplish the task.
Code Snippet
SET NOCOUNT ON
DECLARE @.MyTable table
( SAP_ID int,
JoinDate datetime,
LeaveDate datetime
)
INSERT INTO @.MyTable VALUES ( 1,'2006-01-23', '2006-04-12' )
INSERT INTO @.MyTable VALUES ( 2,'2006-09-04', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 3,'2006-10-03', '2007-01-01' )
INSERT INTO @.MyTable VALUES ( 4,'2007-12-04', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 5,'2005-05-15', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 6,'2007-01-01', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 7,'2005-05-15', '2005-12-31' )
SELECT
Sap_ID,
JoinDate,
LeaveDate
FROM @.MyTable
WHERE ( JoinDate < '2007-01-01' --Join Prior 2007 and
AND LeaveDate >= '2006-01-01' --Leave 2006 or After
)
No comments:
Post a Comment