How do I implement this algorithm in SQL?

Answer 1

At the risk of being told I'm not answering the question, why is it necessary to be done in SQL? It seems like business logic that might fit better in an application layer and even have potential to be re-used elsewhere.

Answer 2

Hello,

Im not sure about Sybases T-SQL, but MS SQL's dialect you could use a trick like the following.

Note though that although it works, Im not sure it's documented behaviour. To be real sure you should probably use a cursor like psasik suggests.

SET NOCOUNT ON

CREATE TABLE MoneyFlow
(
    fiscalEventID INT NOT NULL, 
    value MONEY,
    transactionDate DATETIME
)
go

INSERT INTO MoneyFlow VALUES(1, 1000, '2009-08-25')
INSERT INTO MoneyFlow VALUES(1, -500, '2009-08-30')
INSERT INTO MoneyFlow VALUES(1, -50, '2009-09-01')

-- Today

INSERT INTO MoneyFlow VALUES(1, -150, '2009-09-17') -- -150
INSERT INTO MoneyFlow VALUES(1, +250, '2009-09-19') -- +100 
INSERT INTO MoneyFlow VALUES(1, -125, '2009-09-22') --  -25
INSERT INTO MoneyFlow VALUES(1, 1000, '2009-09-25') -- +975
--INSERT INTO MoneyFlow VALUES(1, -2000, '2009-09-25') -- -1025



GO

DECLARE @curr   MONEY
,   @min MONEY

SELECT  @curr = 0
,   @min = 0

SELECT  @curr = @curr + value
,   @min = CASE 
      WHEN @curr < @min THEN @curr
      ELSE @min
     END
FROM    MoneyFlow f (NOLOCK)
WHERE   f.transactionDate > '2009-09-15'

SELECT  @min

GO
DROP TABLE MoneyFlow

Answer 3

It looks to me like you're trying to create a running total, then select the smallest running value from the running total.

The following is not pretty, but it avoids cursors.

Start with the following to populate your table:

CREATE TABLE #temp
    (someDate datetime
    ,amount decimal)

INSERT INTO #temp (someDate, amount)
SELECT '2009-01-01', 1000 UNION ALL
SELECT '2009-01-02', -500 UNION ALL
SELECT '2009-01-03', -50 UNION ALL
SELECT '2009-01-04', -150 UNION ALL
SELECT '2009-01-05', 250 UNION ALL
SELECT '2009-01-06', -125 UNION ALL
SELECT '2009-01-07', 1000

Here's a simple query to get the minimum running totals:

SELECT
    TOP 1
    base.someDate
    ,runningTotal =
        (SELECT sum(derived.amount)
        FROM #temp derived
        WHERE derived.someDate <= base.someDate)
FROM #temp base
ORDER BY runningTotal ASC

Answer 4

This is MS TSQL but I imagine it will be similar in sybase

SELECT MIN(lmv.value)
FROM @moneyFlow mv
JOIN (
    SELECT SUM(mv.value) as [VALUE], lmv.fiscalEventID
    FROM @moneyFlow mv
    JOIN @moneyFlow lmv ON mv.transactionDate <= lmv.transactionDate
    WHERE mv.transactionDate >= @Start AND mv.transactionDate <= @End
      AND lmv.transactionDate >= @Start AND lmv.transactionDate <= @End
    GROUP BY lmv.fiscalEventID
) lmv ON mv.fiscalEventID = lmv.fiscalEventID
WHERE lmv.value < 0

---

DECLARE @Start DATETIME
SET @Start = '1/2/09'
DECLARE @End DATETIME
SET @End = '1/6/09'
DECLARE @moneyFlow TABLE (
    fiscalEventID int not null,   
    value money,   
    transactionDate DATETIME
)

INSERT @moneyFlow VALUES (1, 1000, '1/1/09')
INSERT @moneyFlow VALUES (2, -500, '1/2/09')
INSERT @moneyFlow VALUES (3,  -50, '1/3/09')
INSERT @moneyFlow VALUES (4, -150, '1/4/09')
INSERT @moneyFlow VALUES (5,  250, '1/5/09')
INSERT @moneyFlow VALUES (6, -125, '1/6/09')
INSERT @moneyFlow VALUES (7, 1000, '1/7/09')

Answer 5

I'm not familiar with Sybase, so I don't know whether you could do this or not, but I would try something like the following:

select a.transactionDate as balanceDate
     , (select sum(value)
          from MoneyFlow b
         where b.transactionDate <= a.transactionDate
       ) as balance
  from MoneyFlow a
 order by 2

That should show you the day where the balance bottoms out. You may need to adjust this of there is a starting date and starting balance. Likewise, if you only want the one day returned, you will need to restrict the output just to the first row.

Answer 6

This query gives running totals:

 Select M1.TransactionDate, Sum(M2.Money)
 From MoneyFlow M1
     Join MoneyFlow M2
          On M2.TransactionDate <= M1.TransactionDate
 Group By M1.TransactionDate

You want the smallest of these, So this SQL should do that..

Select Min(RunBalance) From
 (Select M1.TransactionDate, Sum(M2.Money) RunBalance
  From MoneyFlow M1
     Join MoneyFlow M2
          On M2.TransactionDate <= M1.TransactionDate
  Group By M1.TransactionDate) Z

To restrict the ouput to negative values add a predivate.
(But SQL should only generate one row, so if there are no negatives this will cause null output...)

 Select Min(RunBalance) From
 (Select M1.TransactionDate, Sum(M2.Money) RunBalance
  From MoneyFlow M1
     Join MoneyFlow M2
          On M2.TransactionDate <= M1.TransactionDate
  Group By M1.TransactionDate) Z
 Where RunBalance > 0

Answer 7

select min(Value)
from (
    select sum(value) as Value
    from MoneyFlow
    group by transactionDate 
    where transactionDate between @startdate and @enddate
) a
where min(Value) < 0