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
Go to Top of Page

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
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 09:51:04
You're welcome~

Cheers
MIK
Go to Top of Page

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
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -