sql query - SQL Server Forums

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)

sql query

Author

Topic

sohrabkhan84
Starting Member

3 Posts

Posted - 2013-12-15 : 05:05:47

hi experts,
i have a table with following structure

UserID startdate enddate leavetype
10 13/10/2013 15/10/2013 sick
10 20/12/2013 23/12/2013 sick
10 26/12/2013 30/12/2013 Annual

now i want to count total leave with each leave type of an employee between 2 dates which shown following:

userid leavetype total
10 sick 5
10 annual 4

thanks in advance

jethrow
Starting Member

37 Posts

Posted - 2013-12-16 : 01:58:10

[code]SELECT userid, leavetype, Sum(DateDiff(day, startdate, enddate)) total
FROM table
GROUP BY userid, leavetype;[/code]

Microsoft SQL Server Noobie

sohrabkhan84
Starting Member

3 Posts

Posted - 2013-12-16 : 03:05:24

thanks dear,its helped me completely

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 04:02:09

how is the total 5 for sick? shouldnt it be 7? (3 days from 13-15 and 4 days from 20-23? Similarly for annual it should be 5 (26,27,28,29,30)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources