How to calculate total experience from ExperienceHistory table in sql?Ask Questions

 Posted on 09/25/2018

I have table tblExperienceHistory


I want total experience like

ResumeId, Experience

17, 3 years 3 months

18, 4 year 8 months



Share On: facebook gplus twitter
Asked by Shailesh Chaudhary on 09/25/2018 Score: 808 points
Add Comment:


20/11/2018   08:18:08   Hardik Parekh
i'm still waiting for answer
25/10/2018   10:22:38   Shailesh Chaudhary

1 Answers

1 Corrected Answers
Aproved Answers
Answered by Pink Penthar on 11/2/2018 3:07:41 PM Score: 42 points

You can get solution using sql scalar function

CREATE FUNCTION [dbo].[UDF_S_CalculateExperience] (@UserId bigint)
RETURNS nvarchar(50)
DECLARE @ExpYr INT=0,@ExpMon INT=0
@ExpYr=(SUM(convert(int,DATEDIFF(MONTH, FromMonth+'/1/'+FromYear, (CASE CurrentlyWorking WHEN 'N' THEN ToMonth+'/1/'+ToYear ELSE GETDATE() END))/12))),
@ExpMon=(SUM(convert(int,DATEDIFF(MONTH, FromMonth+'/1/'+FromYear, (CASE CurrentlyWorking WHEN 'N' THEN ToMonth+'/1/'+ToYear ELSE GETDATE() END)) % 12)))
    FROM S_ResumeWorkHistory 
    where UserId=@UserId

declare @AddYr int
SET @AddYr=(@ExpMon/12)
SET @ExpYr=@Expyr+@AddYr
SET @ExpMon=@ExpMon- (@AddYr*12)

-- Return the result of the function
RETURN convert(nvarchar,@ExpYr)+' years '+convert(nvarchar,@ExpMon)+' months'



Add Comment:

Post Your Answers

Existing Members

Sign in to your account
Email Address
New Member?
Sign up and complete profile
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.