August 15, 2010 at 11:41 pm
Hi Guys,
I need your help in writing the script which gives the employee and his duration with the company. Below are the DDL and DML Scripts and the expected output result:
Thanks in advnace:
create table script:
CREATE TABLE [dbo].[Duration](
[EmpId] [int] NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[DeptId] [int] NULL
) ON [PRIMARY]
Insert Data Script:
INSERT INTO [duration] ([EmpId],[StartDate],[EndDate],[DeptId])
VALUES
(1,'Jan 1 2010 12:00:00:000AM','Jan 31 2010 12:00:00:000AM',1)
,(1,'Feb 1 2010 12:00:00:000AM','Feb 28 2010 12:00:00:000AM',2)
,(1,'Mar 1 2010 12:00:00:000AM','Mar 31 2010 12:00:00:000AM',3)
,(2,'Jan 1 2010 12:00:00:000AM','Jan 31 2010 12:00:00:000AM',1)
,(2,'Feb 1 2010 12:00:00:000AM',NULL,2)
,(3,'Jan 1 2010 12:00:00:000AM','Jan 31 2010 12:00:00:000AM',1)
,(3,'Mar 1 2010 12:00:00:000AM',NULL,1)
expected output:
EmpIDStartDateEndDate
12010-01-01 00:00:00.0002010-03-31 00:00:00.000
22010-01-01 00:00:00.0002010-08-15 22:29:56.590
32010-01-01 00:00:00.0002010-01-31 00:00:00.000
32010-01-01 00:00:00.0002010-08-15 22:29:56.590
I have written the below script:
SELECT x.EmpID,x.StartDate,x.EndDate
FROM(
SELECT EmpID,MIN(startdate) AS startdate ,MAX(enddate) AS EndDate FROM Duration
group by EmpId
UNION
SELECT EmpID, MIN(startdate),MAX(ISNULL(enddate,Getdate())) As EndDate FROM Duration
group by EmpId
) x
but my script gives and extra record
EmpIDStartDate EndDate
12010-01-01 00:00:00.0002010-03-31 00:00:00.000
22010-01-01 00:00:00.0002010-01-31 00:00:00.000 (extra record)
22010-01-01 00:00:00.0002010-08-15 22:35:09.853
32010-01-01 00:00:00.0002010-01-31 00:00:00.000
32010-01-01 00:00:00.0002010-08-15 22:35:09.853
thanks in advance.
August 16, 2010 at 12:00 am
This is enough to give you the required answer. No need of UNION with the other statement.
SELECT EmpID, MIN(startdate),MAX(ISNULL(enddate,Getdate())) As EndDate FROM Duration
group by EmpId
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 16, 2010 at 12:16 am
There is gap for empid= 3 so i need two records for empid=3 in the result, hope this make sense.
ex:
select * from duration
where EmpId = 3
ouptput:
EmpIdStartDateEndDate
32010-01-01 00:00:00.0002010-01-31 00:00:00.000
32010-03-01 00:00:00.000NULL
Your result set looks like below:
EmpIDstartdateEndDate
12010-01-01 00:00:00.0002010-03-31 00:00:00.000
22010-01-01 00:00:00.0002010-08-15 23:14:58.970
32010-01-01 00:00:00.0002010-08-15 23:14:58.970
but resultSet needed:
EmpIDStartDateEndDate
12010-01-01 00:00:00.0002010-03-31 00:00:00.000
22010-01-01 00:00:00.0002010-08-15 23:14:20.633
32010-01-01 00:00:00.0002010-01-31 00:00:00.000
32010-01-01 00:00:00.0002010-08-15 23:14:20.633
August 16, 2010 at 3:16 am
Can't think of an easy solution for this. Lets see if somebody comes up with a quick solution. I will try it myself in my free time.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 16, 2010 at 3:32 am
is there any fix period for the gap for any employee..
like an employee can have max 22 days of leave in year or something like with other holidays/leave.
you can put that in your filter condition and then use the query which already sugegsted.
----------
Ashish
August 16, 2010 at 4:36 am
The Gap was due to the employee was out of the company for sometime and he joined the company again after some time.
Hope this make sense.
Thanks in Advance.
August 16, 2010 at 4:41 am
but in that case there will be new empid for him/her. and then everything from fresh. Thats what I am assuming. Is that not the case?
----------
Ashish
August 16, 2010 at 4:44 am
We are not creating a new empid if the employee already existed.
August 16, 2010 at 4:55 am
then in that case if a employee having two joining date then you can filter those records in your where condition
----------
Ashish
August 16, 2010 at 5:00 am
Use this. This will give ur exact output
SELECT x.EmpID,x.StartDate,x.EndDate
FROM(SELECT EmpID,MIN(startdate) AS startdate ,MAX(enddate) AS EndDate FROM Duration where EmpId=3
group by EmpId UNION SELECT EmpID, MIN(startdate),MAX(ISNULL(enddate,Getdate())) As EndDate FROM Duration
group by EmpId
) x
August 16, 2010 at 5:42 am
@Kavitha: Thanks for the reply, Can't we make it a generic querry rather than filtering the records by hardcoding the empid = 3
@Ashish: I am sorry i didn't get your filter condition.
Thanks
August 16, 2010 at 6:31 am
Do you need the last employee id has 2 different end dates. isn't it
August 16, 2010 at 6:44 am
I mean if there is any discontinuity for an employee in his work, (For e.x EmpID=3 EndDate is jan31st and started his work on march 1st again ), so i should see two records for him in that case.
Otherwise i should see the empid, HisStartDate and EndDate.
Thanks
August 17, 2010 at 12:07 am
Hi there,
Here's the solution i came up with.. I hope it helps..:-)
;WITH cte AS
(
SELECTEmpId
, StartDate
, ISNULL(EndDate,GETDATE()) as EndDate
, ROW_NUMBER() OVER (PARTITION BY Empid ORDER BY StartDate) rn
FROM Duration d
)
SELECTa.EmpId
,MIN(a.StartDate) AS StartDate
,MAX(a.enddate) AS EndDate
FROM cte a
LEFT JOIN cte b ON a.rn=b.rn+1 AND a.EmpId=b.EmpId
GROUP BY a.empid,CASE WHEN a.StartDate=ISNULL(DATEADD(dd,1,b.enddate),a.startdate) THEN a.empid ELSE -a.rn END
ORDER BY EmpId,StartDate,EndDate
August 17, 2010 at 10:10 pm
This helps...Thanks a Lot shield 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply