SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SELECT statement to show MIN() ,MAX() & DATEDIFF()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
43 Posts

Posted - 06/28/2013 :  08:46:22  Show Profile  Reply with Quote

If I have a table like the one below which contains employees and their multiple periods of employment with a company, then what select statement could I write to display a table of the employees and their earliest start date, their latest laving date and the DATEDIFF() value in days between these two dates, whilst avoiding the aggregates in select statement rules. So the resulting table would like the lower table.


employee_id  start_date  leaving_date
234          2011-09-01  2012-05-26
234          2012-11-03  2013-04-09
567          2010-06-23  2012-12-18
890          2009-12-08  2010-09-23
890          2010-12-09  2011-06-07
890          2012-01-21  2013-02-26



employee_id  start_date  leaving_date  DATEDIFF
234          2011-09-01  2013-04-09    586
567          2010-06-23  2012-12-18    909
890          2009-12-08  2013-02-26    1176

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/28/2013 :  08:58:37  Show Profile  Reply with Quote
select employee_id,MIN(start_date),MAX(leaving_date),datediff(dd,MIN(start_date),MAX(leaving_date)) from TableName
Group by employee_id

Cheers
MIK
Go to Top of Page

stamford
Starting Member

United Kingdom
43 Posts

Posted - 06/28/2013 :  09:28:07  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008

select employee_id,MIN(start_date),MAX(leaving_date),datediff(dd,MIN(start_date),MAX(leaving_date)) from TableName
Group by employee_id

Cheers
MIK



Of course, thank you, it all seems so simple now
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/28/2013 :  09:51:04  Show Profile  Reply with Quote
You're welcome~

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/28/2013 :  11:00:49  Show Profile  Reply with Quote
@Stamford
One question here
How does taking the DATEDIFF between earliest start and latest leaving date gives you total duration of employee?
do you mean you dont need to consider the gaps in between?

Logically i think this is what would give you accurate duration of employement for the employee


SELECT employee_id,
SUM(DATEDIFF(dd,start_date,leaving_date)) AS TotalDuration
FROM TableName
GROUP BY employee_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/29/2013 :  04:48:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
		(
			EmployeeID SMALLINT NOT NULL,
			StartDate DATE NOT NULL,
			LeaveDate DATE NOT NULL
		);

INSERT	@Sample
	(
		EmployeeID,
		StartDate,
		LeaveDate
	)
VALUES	(234, '20110901', '20120526'),
	(234, '20121103', '20130409'),
	(567, '20100623', '20121218'),
	(999, '20130101', '20130131'),
	(999, '20130106', '20130110'),
	(890, '20091208', '20100923'),
	(890, '20101209', '20110607'),
	(890, '20120121', '20130226');

-- Flawed
SELECT		EmployeeID,
		MIN(StartDate) AS FirstDate,
		MAX(LeaveDate) AS LastDate, 
		DATEDIFF(DAY, MIN(StartDate), MAX(LeaveDate)) AS Original,
		SUM(1 + DATEDIFF(DAY, StartDate, LeaveDate)) AS SwePeso
FROM		@Sample
GROUP BY	EmployeeID;

-- SwePeso
WITH cteSource(EmployeeID, theDate)
AS (
	SELECT		s.EmployeeID,
			DATEADD(DAY, v.Number, s.StartDate) AS theDate
	FROM		@Sample AS s
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND DATEDIFF(DAY, s.StartDate, s.LeaveDate)
)
SELECT		EmployeeID,
		MIN(theDate) AS FirstDate,
		MAX(theDate) AS LastDate,
		1 + DATEDIFF(DAY, MIN(theDate), MAX(theDate)) AS [Better original flawed],
		COUNT(theDate) AS [Regardless of overlapping],
		COUNT(DISTINCT theDate) AS [Possible overlapping]
FROM		cteSource
GROUP BY	EmployeeID;

Edited by - SwePeso on 06/29/2013 04:52:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000