calculation based on condition

Question

I need a suggestion for interest calculation on sql server.Below is the account transaction detail of Mr.x in a month of jan

Opening balance: 100

 DATE     Deposit  W/d  BALANCE
1         10000          10100
2                        10100
3                        10100
4                        10100
5         40000          50100
6                        50100
7                        50100
8                        50100
9                 45000   5100

below are the balance details of Mr.X's of Month Jan

Opening Bal = 100

On 1st july= Rs. 10,100

On 5th july= Rs.50,100

On 9th july= Rs.5000 (after he has withdrawn Rs.45,000)

Now i need to insert below info to some table on month end

FromDate    Todate  Noofdays    Balance
01.01.2017  04.01.2017  4        10100
05.01.2017  08.01.2017  4        50100
09.01.2017  31.01.2017  23        5000

please any one help me to get number of days and balance details in sql server

Answer 1

I did not use the balance column, the script will calculate balance each day automatically

the " CROSS APPLY(VALUES(100,1,2017)) AS i(openingbalance,mon,yr)" is input parameter.

    CREATE TABLE #tt(D datetime,Deposit MONEY,[W/d] money,BALANCE MONEY)
    INSERT INTO #tt
    SELECT '01/01/2017',10000,NULL,10100 UNION
    SELECT '01/02/2017',NULL,NULL,10100 UNION
    SELECT '01/03/2017',NULL,NULL,10100 UNION
    SELECT '01/04/2017',NULL,NULL,10100 UNION
    SELECT '01/05/2017',40000,NULL,50100 UNION
    SELECT '01/06/2017',NULL,NULL,50100 UNION
    SELECT '01/07/2017',NULL,NULL,50100 UNION
    SELECT '01/08/2017',NULL,NULL,50100 UNION
    SELECT '01/09/2017',NULL,45000,5100
    SELECT * FROM #tt
            SELECT CONVERT(DATE,MIN(t.d)) AS FromDate,CONVERT(DATE,MAX(t.d)) AS Todate,t.BALANCE,DATEDIFF(d,MIN(t.d),MAX(t.d))+1  AS Noofdays
    FROM (
        SELECT d.d,a.Deposit,a.[W/d] 
                ,i.openingbalance+b.balance AS BALANCE
        FROM master.dbo.spt_values AS sv 
        CROSS APPLY(VALUES(100,1,2017)) AS i(openingbalance,mon,yr)
        CROSS APPLY(VALUES(DATEADD(DAY,sv.number,DATEADD(MONTH,i.mon-1,DATEADD(YEAR,i.yr-1900,0))))) d(d)
        LEFT JOIN #tt AS a ON DATEDIFF(d,a.d,d.d)=0
        OUTER APPLY(
                SELECT SUM(ISNULL(tt.Deposit,0))-SUM(ISNULL(tt.[W/d],0)) AS balance 
                FROM #tt AS tt WHERE DATEDIFF(d,tt.d,d.d)>=0
        ) b
        WHERE sv.type='P' AND sv.number BETWEEN 0 AND 30
        AND MONTH(d.d)=i.mon
        -- ORDER BY  d.d
    ) AS t
    GROUP BY t.BALANCE
    ORDER BY MIN(t.d)

FromDate   Todate     BALANCE               Noofdays
---------- ---------- --------------------- -----------
2017-01-01 2017-01-04 10100.00              4
2017-01-05 2017-01-08 50100.00              4
2017-01-09 2017-01-31 5100.00               23

--Version 2 , with customer id --------

CREATE TABLE #tt(CustID INT,D datetime,Deposit MONEY,[W/d] money,BALANCE MONEY)
INSERT INTO #tt
SELECT 1,'01/01/2017',10000,NULL,10100 UNION
SELECT 1,'01/02/2017',NULL,NULL,10100 UNION
SELECT 1,'01/03/2017',NULL,NULL,10100 UNION
SELECT 1,'01/04/2017',NULL,NULL,10100 UNION
SELECT 1,'01/05/2017',40000,NULL,50100 UNION
SELECT 1,'01/06/2017',NULL,NULL,50100 UNION
SELECT 1,'01/07/2017',NULL,NULL,50100 UNION
SELECT 1,'01/08/2017',NULL,NULL,50100 UNION
SELECT 1,'01/09/2017',NULL,45000,5100 UNION
SELECT 2,'01/06/2017',1000,NULL,NULL UNION
SELECT 2,'01/07/2017',2000,NULL,NULL UNION
SELECT 2,'01/08/2017',5000,NULL,NULL UNION
SELECT 2,'01/09/2017',NULL,4000,NULL

--   SELECT * FROM #tt
SELECT t.CustID, CONVERT(DATE,MIN(t.d)) AS FromDate,CONVERT(DATE,MAX(t.d)) AS Todate,t.BALANCE,DATEDIFF(d,MIN(t.d),MAX(t.d))+1  AS Noofdays
FROM (
    SELECT  i.CustID, d.d,a.Deposit,a.[W/d] 
            ,i.openingbalance+isnull(b.balance,0) AS BALANCE
    FROM master.dbo.spt_values AS sv 
    CROSS APPLY(VALUES(1,100,1,2017),(2,500,1,2017)) AS i(CustID,openingbalance,mon,yr)
    CROSS APPLY(VALUES(DATEADD(DAY,sv.number,DATEADD(MONTH,i.mon-1,DATEADD(YEAR,i.yr-1900,0))))) d(d)
    LEFT JOIN #tt AS a ON DATEDIFF(d,a.d,d.d)=0 AND a.CustID=i.CustID
    OUTER APPLY(
            SELECT SUM(ISNULL(tt.Deposit,0))-SUM(ISNULL(tt.[W/d],0)) AS balance 
            FROM #tt AS tt WHERE DATEDIFF(d,tt.d,d.d)>=0 AND tt.CustID=i.CustID

    ) b
    WHERE sv.type='P' AND sv.number BETWEEN 0 AND 30
    AND MONTH(d.d)=i.mon
   -- ORDER BY i.CustID,d.d
) AS t
GROUP BY t.CustID, t.BALANCE
ORDER BY t.CustID, MIN(t.d)

CustID      FromDate   Todate     BALANCE               Noofdays
----------- ---------- ---------- --------------------- -----------
1           2017-01-01 2017-01-04 10100.00              4
1           2017-01-05 2017-01-08 50100.00              4
1           2017-01-09 2017-01-31 5100.00               23
2           2017-01-01 2017-01-05 500.00                5
2           2017-01-06 2017-01-06 1500.00               1
2           2017-01-07 2017-01-07 3500.00               1
2           2017-01-08 2017-01-08 8500.00               1
2           2017-01-09 2017-01-31 4500.00               23

Answer 2

Considering your Date column is stored in DATE type

Here is one way

WITH data
     AS (SELECT *,
                Row_number()OVER(ORDER BY date) - 
                Row_number()OVER(partition BY BALANCE ORDER BY date) AS grp
         FROM   yourtable)
SELECT Min(date),
       Max(date),
       Datediff(dd, Min(date), Max(date)) + 1,
       BALANCE
FROM   data
GROUP  BY grp,
          BALANCE