Find minimum value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find minimum value

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-09-06 : 07:27:04

Hi thanks for reading and all the help in the past.

I have a table structured;

TransID-----AccountID-----Transtype-----Cash-----Date
1-----------1--------------Deposit-------50------20110809
2-----------1--------------Deposit-------100-----20110810
3-----------1--------------Withdrawal----20------20110911
4-----------2--------------Deposit-------20------20111010

For each account I would like to find the first deposit and add them together grouped by each day and I would also like to sum together all subsequent deposits.

I need to select the minimum trans id for each account where the transtype is a deposit and call this Firstdeposit.

To give;

Date--------FirstDeposit-------AllOtherDeposits
20110809----50-----------------0
20110810----0------------------100
20111010----20-----------------0

Many thanks for continued help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 07:38:54

something like


select Date,
SUM(Case WHEN Seq=1 THEN Cash ELSE 0 END) AS FirstDeposit,
SUM(Case WHEN Seq<>1 THEN Cash ELSE 0 END) AS AllOtherDeposits
from
(
select row_number() over (partition by AccountID order by TransID) as seq,AccountID , Cash,Date
from Table
where trans_type='Deposit'
)t
group by AccountID,Date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources