Click here to Skip to main content
15,918,889 members
Please Sign up or sign in to vote.
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
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

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.
 
Share this answer
 
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,

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900