How to get a total running sum of the Dates

Question

0.00/5 (No votes)

See more:

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

SQL

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:

VB

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

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:

SQL

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

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.

Posted 3-Oct-13 1:13am

El Dev

Add a Solution

Comments

coded007 3-Oct-13 7:53am

try to take result of first query in temp table in join with date table in second query check the result

El Dev 3-Oct-13 8:26am

am not getting what u are saying...Pliz give an example.or just give an idea or code on how can I charges 10% to someone if the total running sum of Dates is greater than 60 days.

1 solution

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)

Padam Agrawal · Answer 1 · 2013-10-05T04:48:00

Solution 1

Hello,

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

SQL

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.

Posted 5-Oct-13 4:48am

Padam Agrawal

Comments

El Dev 8-Oct-13 5:32am

Hi Padam!I am still facing a problem...what it I want a total running sum of the TotalDays.
try this code out and see what I mean:

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

see the summation result, I want to get samething with the TotalDays field.
Please help fixing this.

Padam Agrawal 8-Oct-13 10:20am

I didn't get your requirement. if you want solution provide me following,

1. Your table structure query (Create statement)
2. Some insertion queries for sample data (Insert statement)
3. and your clear requirement with output of given sample data

I will provide solution if logically.

Thanks,