Group By Running Total

[HulaGirl]

Hi all,
I am trying to find out the highest positive creditdebit balance and the date it occured for each customer in the account table. I can determine the amount of the highest positive creditdebit balance for each customer using the following codes, but not the date it occured. I can 't group by transdate since it is unique for each transaction. Please HELP. Thanks...

Code...

SELECT t.customerid, MAX(t.runningtotal)
FROM (SELECT d2.customerid
	, d2.transdate
	,runningtotal = (SELECT SUM(d1.creditdebit) 
			FROM account D1 
			WHERE D1.transdateDate <= D2.transdate
				AND D1.customerid = D2.customerid) 
	FROM account D2
	) t
GROUP BY t.customerid
ORDER BY t.customerid

 

[ClaireHCM]

give this a try..................

select x.id,x.run,y.transdateDate from
(SELECT t.customerid as id, MAX(t.runningtotal as run)
FROM (SELECT d2.customerid
, d2.transdate
,runningtotal = (SELECT SUM(d1.creditdebit)
FROM account D1
WHERE D1.transdateDate <= D2.transdate
AND D1.customerid = D2.customerid)
FROM account D2
) t
GROUP BY t.customerid) as X , account y
where x.id = y.customerid and x.run = y.runningtotal
order by
x.id,x.run,y.transdateDate

 

[HulaGirl]

Thanks ClaireHsu,
But runningtotal is an alias not a field in table &quot;account&quot;.