Summing up the records as per given conditions

Question

I have a table like below, What I need that for any particular fund and up to any particular date logic will sum the amount value. Let say I need the sum for 3 dates as 01/28/2015,03/30/2015 and 04/01/2015. Then logic will check for up to first date how many records are there in table . If it found more than one record then it'll sum the amount value. Then for next date it'll sum up to the next date but from the previous date it had summed up.

Id Fund   Date         Amount
1   A   01/20/2015      250
2   A   02/28/2015      300
3   A   03/20/2015      400
4   A   03/30/2015      200
5   B   04/01/2015      500
6   B   04/01/2015      600

I want result to be like below

Id   Fund    Date        SumOfAmount
1     A    02/28/2015      550
2     A    03/30/2015      600
3     B    04/01/2015     1100

Answer 1

If i change your incorrect sample data to ...

CREATE TABLE TableName
    ([Id] int, [Fund] varchar(1), [Date] datetime, [Amount] int)
;

INSERT INTO TableName
    ([Id], [Fund], [Date], [Amount])
VALUES
    (1, 'A', '2015-01-28 00:00:00', 250),
    (2, 'A', '2015-01-28 00:00:00', 300),
    (3, 'A', '2015-03-30 00:00:00', 400),
    (4, 'A', '2015-03-30 00:00:00', 200),
    (5, 'B', '2015-04-01 00:00:00', 500),
    (6, 'B', '2015-04-01 00:00:00', 600)
;

this query using GROUP BY works:

SELECT MIN(Id) AS Id,   
       MIN(Fund) AS Fund,
       [Date],        
       SUM(Amount) AS SumOfAmount
FROM dbo.TableName t
WHERE [Date] IN ('01/28/2015','03/30/2015','04/01/2015')
GROUP BY  [Date]

Demo

Answer 2

Based on your question, it seems that you want to select a set of dates, and then for each fund and selected date, get the sum of the fund amounts from the selected date to the previous selected date. Here is the result set I think you should be expecting:

Fund    Date        SumOfAmount
A       2015-02-28  550.00
A       2015-03-30  600.00
B       2015-04-01  1100.00

Here is the code to produce this output:

DECLARE @Dates TABLE
(
    SelectedDate DATE PRIMARY KEY 
)

INSERT INTO @Dates 
VALUES
     ('02/28/2015')
    ,('03/30/2015')
    ,('04/01/2015')

DECLARE @FundAmounts TABLE
(
    Id INT PRIMARY KEY
    ,Fund VARCHAR(5)
    ,Date DATE
    ,Amount MONEY
);

INSERT INTO @FundAmounts
VALUES
     (1, 'A', '01/20/2015', 250)
    ,(2, 'A', '02/28/2015', 300)
    ,(3, 'A', '03/20/2015', 400)
    ,(4, 'A', '03/30/2015', 200)
    ,(5, 'B', '04/01/2015', 500)
    ,(6, 'B', '04/01/2015', 600);

SELECT 
    F.Fund
    ,D.SelectedDate AS Date
    ,SUM(F.Amount) AS SumOfAmount
FROM
(
    SELECT
        SelectedDate
        ,LAG(SelectedDate,1,'1/1/1900') OVER (ORDER BY SelectedDate ASC) AS PreviousDate
    FROM @Dates
) D
JOIN
    @FundAmounts F
    ON
        F.Date BETWEEN DATEADD(DAY,1,D.PreviousDate) AND D.SelectedDate
GROUP BY
    D.SelectedDate
    ,F.Fund

EDIT: Here is alternative to the LAG function for this example:

FROM
(
    SELECT
        SelectedDate
        ,ISNULL((SELECT TOP 1 SelectedDate FROM @Dates WHERE SelectedDate < Dates.SelectedDate ORDER BY SelectedDate DESC),'1/1/1900') AS PreviousDate
    FROM @Dates Dates
) D

Answer 3

declare @TableName table([Id] int, [Fund] varchar(1), [Date] datetime, [Amount] int)
declare @Sample table([SampleDate] datetime)


INSERT INTO @TableName
    ([Id], [Fund], [Date], [Amount])
VALUES
    (1, 'A', '20150120 00:00:00', 250),
    (2, 'A', '20150128 00:00:00', 300),
    (3, 'A', '20150320 00:00:00', 400),
    (4, 'A', '20150330 00:00:00', 200),
    (5, 'B', '20150401 00:00:00', 500),
    (6, 'B', '20150401 00:00:00', 600)

INSERT INTO @Sample ([SampleDate])
values ('20150128 00:00:00'), ('20150330 00:00:00'), ('20150401 00:00:00')


-- select * from @TableName
-- select * from @Sample

;WITH groups AS ( 
    SELECT [Fund], [Date], [AMOUNT], MIN([SampleDate]) [SampleDate] FROM @TableName
    JOIN @Sample ON [Date] <= [SampleDate]
    GROUP BY [Fund], [Date], [AMOUNT])
SELECT [Fund], [SampleDate], SUM([AMOUNT]) FROM groups
GROUP BY [Fund], [SampleDate]

Explanation:

• The CTE groups finds the earliest SampleDate which is later than (or equals to) your data's date and enriches your data accordingly, thus giving them the group to be summed up in.
• After that, you can group on the derived date.

Answer 4

Initially i have used Row_number and month function to pick max date of every month and in 2nd cte i did sum of amounts and joined them..may be this result set matches your out put

declare @t table (Id int,Fund Varchar(1),Dated date,amount int)

insert into @t (id,Fund,dated,amount) values (1,'A','01/20/2015',250),
(2,'A','01/28/2015',300),
(3,'A','03/20/2015',400),
(4,'A','03/30/2015',200),
(5,'B','04/01/2015',600),
(6,'B','04/01/2015',500)

;with cte as (
select ID,Fund,Amount,Dated,ROW_NUMBER() OVER 
  (PARTITION BY DATEDIFF(MONTH, '20000101', dated)ORDER BY dated desc)AS RN from @t
  group by ID,Fund,DATED,Amount
  ),
  CTE2 AS 
  (select  SUM(amount)Amt from @t
    GROUP BY MONTH(dated))
  ,CTE3 AS 
  (Select Amt,ROW_NUMBER()OVER (ORDER BY amt)R from cte2)
 ,CTE4 AS 
 (
        Select DISTINCT C.ID As ID,
        C.Fund As Fund,
        C.Dated As Dated
        ,ROW_NUMBER()OVER (PARTITION BY RN ORDER BY (SELECT NULL))R
         from cte C INNER JOIN CTE3 CC ON c.RN = CC.R
        Where C.RN = 1
        GROUP BY C.ID,C.Fund,C.RN,C.Dated )

select C.R,C.Fund,C.Dated,cc.Amt  from CTE4 C INNER JOIN CTE3 CC
ON c.R = cc.R