Difference between dates along with year in SQL SERVER

Question

0.00/5 (No votes)

See more:

Hello Everybody,

How to write stored procedure for below conditions
Ex:
TABLE A: TABLE B:

Emp_Id Date Emp_Id Salary
1 2014-04-01 1 10000
1 2015-07-30

Inputs are Emp_id, fromdate and todate

If fromdate and todate fall in same month and year means no problem

Illustration: Input is Emp_Id=1, fromdate='2014-04-01', todate='2014-04-30'

For above illustration,i calculated total number of days in month April and selecting date from TABLE A where Emp_Id=1 and date between fromdate and todate

SQL

SELECT @Total_Days=DATEDIFF(DAY, DATEADD(DAY, 1-DAY(@fromdate),@fromdate),
              DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@fromdate), @fromdate)))

Select sal_calculation=((salary from Table B where Emp_Id=1)/@Total_Days)*(PresentDays)

OUTPUT
------
sal_calculation

10000(no absent)
9657(1 day absent)

Incase of Input is Emp_Id=1,fromdate='2014-04-01', todate='2015-07-30'

how to calculate salary

OUTPUT should be
------
sal_calculation

150000(almost 15 months)--

how to get result for second one: salary/total days of month should be keep on adding till it reaches todate

THANKS IN ADVANCE

Posted 21-Apr-14 23:42pm

Member 10017719

Updated 22-Apr-14 2:16am

v2

Add a Solution

Comments

anup.bhunia 22-Apr-14 7:54am

What you exactly wants as @TotalDays? I might assist if you still looking for the solution.

Member 10017719 22-Apr-14 8:10am

Thank u anup:) I need salary calculation as Output ex:if fromdate='2014-04-20' and todate='2014-04-23' Working days=3 so, Salary Calculation=(10000/30)*3=1000

Member 10017719 22-Apr-14 8:12am

For same month and year no problem, i need solution for different month and year

Andrius Leonavicius 22-Apr-14 8:02am

Hi,

Could you please clarify the formula for salary calculation? Let's say that fromdate='2014-04-01', todate='2014-05-31' and the salary is 10000. Assuming that April has 21 working days and May has 22 working days. How are you getting working days? Is 10000 the weekly or monthly salary? How are you calculating the salary per day?

Member 10017719 22-Apr-14 8:09am

Thank u Andrius, 10000 is montly salary, salary per day=(montly salary/total days of month)
if fromdate='2014-04-20' and todate='2014-04-23' Working days=3 so, Salary Calculation=(10000/30)*3=1000

Andrius Leonavicius 22-Apr-14 8:58am

What version of SQL Server are you using?

4 solutions

Solution 1

Let me know this works for you.

SQL

select (10000/30)* datediff(day,'2014/04/01','2014/05/15') as sal

Posted 22-Apr-14 3:05am

anup.bhunia

Solution 4

If you love simplicity, you might use following solution; It returns total salary inclusive of TodDate and FromDate.

SQL Server 2012

SQL

DECLARE @Salary DECIMAL(18, 2), @FromDate datetime, @ToDate datetime,@MonthDiff int

SET @Salary = 10000;
SET @FromDate = '2014-04-01';
SET @ToDate = '2014-05-22';
SET @MonthDiff=datediff(mm,@FromDate, @ToDate)


IF @MonthDiff=0
BEGIN
    SELECT @Salary * (datediff(dd,@FromDate, @ToDate)+1)/ day(EOMONTH(@FromDate))
END
ELSE
BEGIN
    SELECT @Salary * (datediff(dd,@FromDate, EOMONTH(@FromDate))+1)/ day(EOMONTH(@FromDate))
            +@Salary * day(@ToDate)/ day(EOMONTH(@ToDate))
            +@Salary * (@MonthDiff-1)
END

Posted 26-Apr-14 20:36pm

anup.bhunia

Solution 3

With salary 10000/month
Total salary from '2014-04-01' to '2015-07-31' would be 160000, but you calculating with 2015-07-30, in which case salary should be one day less i.e 150000 + 10000*30/31=159677.

I think you need minor rectification(need to add 1 in tow places) in the code as below:

SQL

DECLARE @Salary DECIMAL(18, 2), @FromDate DATE, @ToDate DATE;

SET @Salary = 10000;
SET @FromDate = '2014-04-22';
SET @ToDate = '2014-05-08';

WITH [Salary] ( [Date], [WorkingDays], [TotalDays])
     AS ( (SELECT DATEADD(dd, 1, EOMONTH(@FromDate, -1)) AS [Date],
                ( CASE
                    WHEN (@ToDate <= EOMONTH (@FromDate)) THEN (DATEDIFF(dd, @FromDate, @ToDate)+1)
                    ELSE (DATEDIFF(dd, @FromDate, EOMONTH(@FromDate)) + 1)
                  END )                                 AS [WorkingDays],
                DAY(EOMONTH(@FromDate))                 AS [TotalDays] )
         UNION ALL
         (SELECT DATEADD(mm, 1, [Date])  AS [Date],
                ( CASE
                    WHEN (@ToDate <= EOMONTH (DATEADD(mm, 1, [Date]))) THEN (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), @ToDate)+1)
                    ELSE (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), EOMONTH(DATEADD(mm, 1, [Date]))) + 1)
                  END )                 AS [WorkingDays],
                DAY(EOMONTH([Date], 1)) AS [TotalDays]
         FROM   [Salary]
         WHERE  (MONTH([Date]) < MONTH(@ToDate)) OR (YEAR([Date]) < YEAR(@ToDate))) )

SELECT CONVERT(DECIMAL(18, 2), ( SUM(@Salary * [WorkingDays] / [TotalDays]) )) AS [Salary]
FROM   [Salary]
OPTION (MAXRECURSION 32747);

Posted 26-Apr-14 10:40am

anup.bhunia

Updated 26-Apr-14 10:44am

v2

Comments

Andrius Leonavicius 26-Apr-14 21:30pm

Hi,

First of all, if you see something bad, then let the author know about it by posting a comment. I just accidentally noticed this...

My code is good because I clearly stated that @ToDate is not included. "If that day would be included, the result would be 160000.00 (16 months)." - I was thinking about a time period from 2014-04-01 to 2015-07-31 (sorry, my bad). For a time period from 2014-04-01 to 2015-07-30 (the last day is included) the result would be 159677.42.

You're right about one thing: adding +1 for including @ToDate. But this wasn't my intention at all. I just made an assumption that @ToDate should not be included because OP gave this example: "fromdate='2014-04-20' and todate='2014-04-23' Working days=3" - 3, not 4...

By the way, I haven't rated "your" solution.

anup.bhunia 27-Apr-14 2:39am

I misunderstood that the sample code is provided by the Author of the actual question.
I regret the inconvenience caused.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Accepted Answer · 2014-04-22T04:53:00

Here's my sample (SQL Server 2012):

SQL

DECLARE @Salary DECIMAL(18, 2), @FromDate DATE, @ToDate DATE;
 
SET @Salary = 10000;
SET @FromDate = '2014-04-22';
SET @ToDate = '2014-05-08';
 
WITH [Salary] ( [Date], [WorkingDays], [TotalDays])
     AS ( (SELECT DATEADD(dd, 1, EOMONTH(@FromDate, -1)) AS [Date],
                ( CASE
                    WHEN (@ToDate <= EOMONTH (@FromDate)) THEN (DATEDIFF(dd, @FromDate, @ToDate))
                    ELSE (DATEDIFF(dd, @FromDate, EOMONTH(@FromDate)) + 1)
                  END )                                 AS [WorkingDays],
                DAY(EOMONTH(@FromDate))                 AS [TotalDays] )
         UNION ALL
         (SELECT DATEADD(mm, 1, [Date])  AS [Date],
                ( CASE
                    WHEN (@ToDate <= EOMONTH (DATEADD(mm, 1, [Date]))) THEN (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), @ToDate))
                    ELSE (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), EOMONTH(DATEADD(mm, 1, [Date]))) + 1)
                  END )                 AS [WorkingDays],
                DAY(EOMONTH([Date], 1)) AS [TotalDays] 
         FROM   [Salary]
         WHERE  (MONTH([Date]) < MONTH(@ToDate)) OR (YEAR([Date]) < YEAR(@ToDate))) )
 
SELECT CONVERT(DECIMAL(18, 2), ( SUM(@Salary * [WorkingDays] / [TotalDays]) )) AS [Salary]
FROM   [Salary]
OPTION (MAXRECURSION 32747);

This is important: If you're using a previous version of SQL Server (2005, 2008, 2008 R2), you need to change the EOMONTH function with other functions or create a user defined function, which returns the last day of the month. Also, you can optimize this by creating a function, which returns the first day of the month as well.

So how this works? First of all, I am getting working days and total days for each month:

XML

Month	WorkingDays	TotalDays
April	9		30
May	7		31

Note: @ToDate is not included! Then I am calculating the sum of salary using this formula: Salary = @Salary * [WorkingDays] / [TotalDays]. The result is 5258.06 or ((10000 * 9 / 30) + (10000 * 7 / 31)).

If @FromDate = '2014-04-01' and @ToDate = '2015-07-30', then the result is 159354.84. The last day (2015-07-30) is not included. If that day would be included, the result would be 160000.00 (16 months).

Difference between dates along with year in SQL SERVER

4 solutions

Solution 2

Solution 1

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us