How to write loop in Microsoft Cosmos Scope to process row by row?

Question

Updated: 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 Cosmos 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.

Appreciate everyone's help in ahead.

/////////////////////////

My thoughts:

I was thinking to loop over the 1st and pick one row (one Date) each time and use that date as the criteria to derive data from the 2nd table.

If written in sql, it may look something like below.

Declare @Counter int,@MaxRowLimit int; 
Set @Counter =1;
Set @MaxRowLimit = (SELECT COUNT(*) FROM DistinctAcctDay);
WHILE @Counter <= @MaxRowLimit
BEGIN
  PickAcctDay=
  SELECT AccountingDay
  FROM DistinctAcctDayId
  WHERE ID =@Counter;

  TransferOut =
     SELECT SUM(Amount) FROM TransferOut
     WHERE AccountingDate <= PickAcctDay;   
   SET @Counter =@Counter +1
END

However it needs to be written in USQL, I'm not familiar with USQL nor C#.I wrote sth like below (which is not correct) and I'm stucked...

int counter=1; int MaxRowLimit;
for (counter = 1; counter <= MaxRowLimit; counter++)
{
  DateTime PickAcctDay =
  SELECT AccountingDay
  FROM DistinctAcctDayId
  WHERE ID ==@Counter;

  TransferOut =
     SELECT SUM(Amount) FROM TransferOut
     WHERE AccountingDate <= PickAcctDay;
}

This is an prime example of an XY problem. http://xyproblem.info/ — Sean Lange, Dec 18 '17 at 19:32
Hi Sean, I have edited my original questions/scenario more clear above, feel free to view and advice. Thanks! — Sssva, Dec 18 '17 at 20:59
Why are you including "original question"? It just adds noise and makes your question difficult to understand. — Yuck, Dec 18 '17 at 21:01
Even your solution in SQL is a bad solution. SQL is a declarative language that transforms and combines rowsets. So you need to think in terms of the language algebra instead of trying to shoehorn it into a different paradigm. David gives you the right answer for your question. It would be nice if you marked it as such. — Michael Rys, Jan 14 '18 at 08:27

score 1 · Answer 1 · answered Dec 20 '17 at 18:33

@DistinctAcctDay = 
SELECT * FROM 
    ( VALUES
    (1, new DateTime(2017,05,01)),
    (2, new DateTime(2017,08,01)),
    (3, new DateTime(2017,09,01))
    ) AS T(Id, AccountingDate);


@TransferOut = 
SELECT * FROM 
    ( VALUES
    (new DateTime(2017,01,01), 10),
    (new DateTime(2017,02,01), 13),
    (new DateTime(2017,06,25), 15),
    (new DateTime(2017,08,03), 18),
    (new DateTime(2017,08,08), 30)
    ) AS T(AccountingDate, Amount);

@result = 
       SELECT d.Id, SUM(Amount) AS sumAmount
       FROM  @DistinctAcctDay AS d
       CROSS JOIN @TransferOut AS t
       WHERE d.AccountingDate >= t.AccountingDate 
      // WHERE d.AccountingDate BETWEEN t.AccountingDate AND d.AccountingDate
       GROUP BY d.Id;

How to write loop in Microsoft Cosmos Scope to process row by row?

1 Answers1