SQL SERVER中日期和年份之间的差异 [英] Difference between dates along with year in SQL SERVER
问题描述
Hello Everybody，
如何为以下条件编写存储过程
Ex：
表A ：表B：
Emp_Id日期Emp_Id工资
1 2014-04-01 1 10000
1 2015 -07-30
输入为Emp_id，fromdate和todate
如果fromdate和todate属于同一个月份和年份表示没问题
插图：输入为Emp_Id = 1，fromdate ='2014-04-01'，todate ='2014-04-30'
如上图所示，我计算了4月份的总天数，并从表A中选择了日期，其中Emp_Id = 1，日期介于fromdate和todate之间
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
SELECT @Total_Days=DATEDIFF(DAY, DATEADD(DAY, 1-DAY(@fromdate),@fromdate),
DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@fromdate), @fromdate)))
选择sal_calculation =（（表B中的工资，其中Emp_Id = 1）/ @ Total_Days）*（PresentDays）
输出
------
sal_calculation
10000（无缺席）
9657（缺席1天）
输入内容为Emp_Id = 1，fromdate ='2014-04-01'，todate ='2015- 07-30'
如何计算工资
OUTPUT应为
------
sal_calculation
150000（差不多15个月） -
如何获得第二个结果：工资/月总天数应该一直保持增加直到达到todate
感谢提前
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
推荐答案
这是我的样本（ SQL Server 2012 ）：
Here's my sample (SQL Server 2012):
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);
这很重要：如果您使用的是以前版本的 SQL Server （2005,2008,2008 R2），则需要使用其他功能更改 EOMONTH 功能或创建用户定义的函数，返回该月的最后一天。此外，您可以通过创建一个函数来优化它，该函数也返回该月的第一天。
那么这是如何工作的？首先，我每个月都有工作日和总天数：
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:
Month WorkingDays TotalDays
April 9 30
May 7 31
注意： @ToDate 不包括在内！然后我用这个公式计算工资总和：薪水= @Salary * [WorkingDays] / [TotalDays]。结果是 5258.06 或（（10000 * 9/30）+（10000 * 7/31））。
如果 @FromDate ='2014-04-01'和 @ToDate ='2015-07-30'，结果是< b> 159354.84 的。最后一天（2015-07-30）不包括在内。如果包含那一天，结果将是 160000.00 （16个月）。
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).
让我知道这对你有用。
Let me know this works for you.
select (10000/30)* datediff(day,'2014/04/01','2014/05/15') as sal
如果您喜欢简单，可以使用以下解决方案;它返回包括TodDate和FromDate在内的总薪水。
SQL Server 2012
If you love simplicity, you might use following solution; It returns total salary inclusive of TodDate and FromDate.
SQL Server 2012
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
这篇关于SQL SERVER中日期和年份之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
