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.
| Author |
Topic |
|
sheela_justina
Starting Member
2 Posts |
 Posted - 2013-09-25 : 06:28:51
|
| i have to calculate employee experience in years,months and days,which is in db as more than one record,each record for each experience as exp1,exp2etc.... in form of fromdate,todate |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-25 : 08:16:27
|
quote:
Originally posted by sheela_justina
i have to calculate employee experience in years,months and days,which is in db as more than one record,each record for each experience as exp1,exp2etc.... in form of fromdate,todate
First take the sum of all the experiences using SUM(datediff(day,fromdate,todate)). Then divide by 365 for the number of years, (365%30)/12 for number of months, and %30 for days. This way of calculating years, months, and days is going to be approximate. Can be made more precise depending on your specific requirements. |
 |
|
|
sheela_justina
Starting Member
2 Posts |
Posted - 2013-09-26 : 00:20:11
|
| thank you james.....my actual problem is,main database is having application no and post no of the candidates...other table is having experience details of candidates as exp1,exp2 in form of fromdate,todate... where applno and post no are the primary keys.so in grid view i have to display experience of candidates in years,months and days...looking forward for ur reply |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-26 : 08:30:34
|
Sounds like you need to join the two tables.....FROM MainTable a INNER JOIN OtherTable b ON a.applno = b.applno and a.postno = b.postno But, I am speculating here - it is very difficult to offer constructive suggestions based on the limited information you have provided. If you can post some sample data, DDL for the tables, and expected output that would make it much easier to provide useful answers. Help on posting is available in this blog: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|
|
display experience as years,months and days