Running totals and first match

• Max-196371

  Grasshopper

  Points: 13

  More actions

  February 1, 2006 at 10:01 pm

  #89404

  Hi all,

  I have a table with data like;

  InvNum,InvCrtDt, InvPstDt, Balance

  1,1-14-2005,1-14-2005,115

  1,1-14-2005,1-29-2005,-105

  1,1-14-2005,1-29-2005,0

  1,1-14-2005,2-11-2005,-10

  What I'd like to do is have a view that selects InvNum,InvCrtDt, InvPstDt, Balance and also has a 'runningBal' and a marker(ZeroDte) ie '1'.

  The rolling balance and marker looks like this in the in the above example;

  InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte

  1,1-14-2005,1-14-2005,115,115,Null

  1,1-14-2005,1-29-2005,-105,10,Null

  1,1-14-2005,1-29-2005,0,10,Null

  1,1-14-2005,2-11-2005,-10,0,1

  I use this statement -

  select*,

  case when runningBalance < 0 then 1 else null end as ZeroDte
  from
  (
  select *, (select sum (Balance) from balance x where x.InvNum = b.InvNum and x.InvPstDt <= b.InvPstDt) as runningBalance
  frombalance b
  ) as a

  And get a table result like...

  InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte,Days
  1,1-14-2005,1-14-2005,-15,-15,Null,Null,Null
  1,1-14-2005,1-29-2005,85,70,Null,Null,Null
  1,1-14-2005,1-29-2005,-66,4,1,15
  1,1-14-2005,2-11-2005,0,4,1,28
  1,1-14-2005,2-15-2005,-4,0,1,32

  What I'd like it to do is put the '1' in the ZeroDte the first time it finds the runningBal <5 as opposed to putting the marker on the 3 rows in my example like the following:

  InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte,Days
  1,1-14-2005,1-14-2005,-15,-15,Null,Null,Null
  1,1-14-2005,1-29-2005,85,70,Null,Null,Null
  1,1-14-2005,1-29-2005,-66,4,1,15
  1,1-14-2005,2-11-2005,0,4,Null,Null
  1,1-14-2005,2-15-2005,-4,0,Null,Null

  So, it would only put the marker the first time the <5 criteria is true.

  Any help is greatly appreciated.

  Job

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  February 2, 2006 at 9:49 am

  #618437

  Is this what you are looking for?

   

  declare @newtable table(InvNum int, InvCrtDt datetime, InvPstDt datetime, Balance int, RunningBalance int, ZeroDte bit null, Days int null)

  insert @newtable

  select b.InvNum, b.InvCrtDt, b.InvPstDt, b.Balance, sum(c.Balance), null, null

  from balance b cross join balance c

  where b.InvPstDt >= c.InvPstDt

  group by b.InvNum, b.InvCrtDt, b.InvPstDt, b.Balance

  update n1 set n1.ZeroDte = 1, n1.Days = datediff(dd, n1.InvCrtDt, n1.InvPstDt)

  from @newtable n1

  where n1.RunningBalance < 5 and n1.Balance < 0

  and not exists

  (select * from @newtable n2 where n2.RunningBalance < 5

  and n2.InvPstDt < n1.InvPstDt)

  select * from @newtable