SELECT statement to show MIN() ,MAX() & DATEDIFF()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SELECT statement to show MIN() ,MAX() & DATEDIFF()

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2013-06-28 : 08:46:22

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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 08:58:37

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

stamford
Starting Member

47 Posts

Posted - 2013-06-28 : 09:28:07

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 09:51:04

You're welcome~

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 11:00:49

@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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-29 : 04:48:43

[code]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;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources