0

An example of what I want to do:

Existing tables:

1st Table name: DistinctAcctDay;
Column name: ID int, AccountingDate datetime;
Values: (1, 2017/05/01);
 (2, 2017/08/01);
 (3, 2017/09/01);


2nd Table name: TransferOut;
Column name: AccountingDate datetime; Amount decimal;
Values: (2017/01/01, 10);
(2017/02/01, 13);
(2017/06/25, 15);
(2017/08/03, 18);
(2017/08/08, 30);

I want to have Scope script to return below 3 (or n, depends on how many rows exists in 1st table) outputs of sum amounts of ALL historical data prior to that day:

1) Sum amount in 2nd table of dates before 2017/05/01 in 1st table: number should be 23;

2)sum amount in 2nd table of dates before 2017/08/01 in 1st table: number should be 10+13+15 =38;

3)sum amount in 2nd table of dates before 2017/09/01 in 1st table: number should be 10+13+15+18+30=86;

Scripts should be written in Cosmos Scope script. ** I once asked a similar questions but didn't describe in a correct way by saying it used USQL. It's Scope script rather than USQL. Hence I'm posting again with right description.*

// I was thinking it may need to use C# to write loops in user-defined Function/operator.

Thanks for help.

Sssva
  • 19
  • 1
  • 6

1 Answers1

0
@result = 
    SELECT d.Id, SUM(Amount) AS sumAmount
    FROM  @DistinctAcctDay AS d
    CROSS JOIN @TransferOut AS t
    WHERE d.AccountingDate >= t.AccountingDate 
    GROUP BY d.Id;
David Paul Giroux
  • 657
  • 4
  • 10