Increment value depending on months count

Joined:	14/01/2014
Level:	Bronze
Points:	90

Points:	3

Hi,

Employee annual leave limit per years is 30. Per month 2.5 is added as a annual leave balance based on his joining date.

For example say employee completed 3 months his annual leave balance count should be 7.5.

1st month - 2.5
2nd month - 5
3rd month - 7.5

How to achieve this using sql / .net

Regards,
Ayesha

Joined:	06/04/2012
Level:	Gold
Points:	4230

Points:	4

Hi Ayesha,

You can get it by using below query.

create table #student
(
name varchar(50),
doj date,
leavebalance numeric(5,2)
)

insert into #student(name,doj) values('Nirav', '2020-06-10')

select name, convert(varchar(10),doj,103), convert(varchar(10),GETDATE(),103) 'today''s date',
(datediff(MM,doj,EOMONTH(CAST(YEAR(getdate()) as varchar(4)) + '-' + CAST(MONTH(getdate()) as varchar(10)) + '-01')) * 2.5) 'leave balance'
from #student

Hope it will help you to get an understanding.

Thank you.

Regards,
Nirav Lalan
DNS Gold Member
"If you can dream it, you can do it."

Increment value depending on months count

About dotnetspider.com

Quick Links

General

Services