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

 Posted on 25/09/2018 2:06:11 PM

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
profile
Asked by Shailesh Chaudhary on 25/09/2018 2:06:11 PM Score: 501 points
Add Comment:
Submit

Comments

i'm still waiting for answer
25/10/2018   10:22:38   Shailesh Chaudhary

1 Answer

0 Corrected Answers
0
Profile
Answered by Pink Penthar on Nov 2 2018 3:07PM Score: 28 points

You can get solution using sql scalar function

CREATE FUNCTION [dbo].[UDF_S_CalculateExperience] (@UserId bigint)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @ExpYr INT=0,@ExpMon INT=0
    
select 
@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

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

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

END

Add Comment:

Post Your Answer

  •  
  •  

Existing Members

Sign in to your account
Email Address
Password
...or Join us
Download, Vote, Comment, Publish.
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.