如何获得日期的总运行总和 [英] How to get a total running sum of the Dates

问题描述

我可以得到这样一个字段的运行总和：

I can get the running sum of a field like this:

SELECT a.[DateId], a.InstallementID, (SELECT SUM(b.InstallementID)
                       FROM [dbo].[Date] b
                       WHERE b.[DateId] <= a.[DateId]) as Summation
FROM   [dbo].[Date] a

这是输出：

This is the output:

DateId InstallementId Date                       Summation 
1      1              2013-01-01 00:00:00.000      1
2      1              2013-02-05 00:00:00.000      2
3      1              2013-03-11 00:00:00.000      3
4      1              2013-04-01 00:00:00.000      4
5      2              2013-05-21 00:00:00.000      6
6      2              2013-06-23 00:00:00.000      8
7      3              2013-07-25 00:00:00.000      11
8      3              2013-08-02 00:00:00.000      14

但我要找的是得到日期之间的区别然后得到每天运行总和，如果日期之间的差异大于30天，则收取10％。

这个代码我可以收取10％的费用，其中日期之间的差异大于30天：

but what I am looking for is to get the difference between the date then get the running sum by day and charge 10% where the difference between dates is greater than 30 days.
whith this code I can charge 10% where the difference between Dates is greater than 30 days:

select *, '10%' as interest from [dbo].[Date] D
  where ISNULL(DATEDIFF(dd,D.Date, (select [Date] from [dbo].[Date] where DateId = D.DateId + 1
   )),0)>30

所以我想要的就是得到日期总和大于或等于60天的日期和总费用10％。



帮助解决这个问题。

so what I want is to get the total running sum of the Dates and charges 10% where the sum of the dates is greater or equal to 60 days.

Help resolve this.

推荐答案

你好，



我没有得到你的要求，但我在这里分享一个例子并且非常肯定会这样做帮助你。

Hello,

I am not getting your requirement, but here I am sharing an example and pretty much sure this will help you out.

SELECT
    DateID,
    TotalDays,
    CASE WHEN (TotalDays>=60) THEN 20 ELSE 10 END As Percentage
FROM
(
    SELECT DateId , ISNULL(DATEDIFF(dd,D.Date, (select [Date] from [dbo].[Date] where DateId = D.DateId + 1 )),0) TotalDays
    FROM [dbo].[Date] D
    WHERE ISNULL(DATEDIFF(dd,D.Date, (select [Date] from [dbo].[Date] WHERE DateId = D.DateId + 1 )),0)>30

) As Tab1

您可以根据自己的要求进行自定义。如果您仍然遇到问题，请与表格结构和示例分享完整的详细信息。

You can customize it as per your requirement. If you still facing problem then please share complete details with table structure and example.

这篇关于如何获得日期的总运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！