group By And Union Help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

group By And Union Help

Author

Topic

ghumph
Starting Member

2 Posts

Posted - 2004-07-29 : 06:12:54

Does anyone have any ideas that would help me Im trying to get all the information about a clients transactions, firast I need to start with an opening balance and then get all the rest of the transactions for the month and display them in a VB data report
Here is the statement the first part is the opening balance and the second part is the transactions, I want to be able to group them by funds
Here is the sp Ive done so far but this treats the transactions for each funds as though they are individual records

create proc CustomerTest5
@idshareholder as integer,
@startDate as datetime,
@endDate as datetime
as
Select tblTAClientDetails.IDShareholder, tblFundDetails.FundName, sum(FinalShares) as stock, 'OpeningBalance' As Description, '' as IDTransaction, '' as tradedate
from tblTATransactions
inner join tblFundDetails on tblFundDetails.IDFund = tblTATransactions.IDFund
inner join tblTAClientDetails on tblTAClientDetails.IDShareholder = tblTATransactions.IDShareholder
where tblTAClientDetails.IDShareholder = @idshareholder
group by tblFundDetails.FundName, tblTAClientDetails.IDShareholder, Description, tradedate
having tradeDate < @startDate
union
Select tblTAClientDetails.IDShareholder, tblFundDetails.FundName, sum(FinalShares) as stock, transType As Description, IDTransaction, tradedate
from tblTATransactions
inner join tblFundDetails on tblFundDetails.IDFund = tblTATransactions.IDFund
inner join tblTAClientDetails on tblTAClientDetails.IDShareholder = tblTATransactions.IDShareholder
where tblTAClientDetails.IDShareholder = 87
group by tblFundDetails.FundName, tblTAClientDetails.IDShareholder, transType, IDTransaction, tradedate
having sum(finalShares)> 0 and tradeDate between @startDate and @endDate

Any Ideas ?????

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 08:36:53

If you want to give some sample data and a desired result, I'm sure we would find it much easier to point you in the right direction...

Corey

ghumph
Starting Member

2 Posts

Posted - 2004-07-29 : 09:01:12

Here is some sample output it works in the way ou would expect but Im displaying it in a data dynamic active report and I want it to group the result set by the fundname and then as we are displaying the data we can display the transactions for each fund as one recordset for example the fund ADVANTAGE CONVERTIBLE SECURITES should be displayed together but instead the are treated as seperate.
Hope you get me

IDShareholder FundName stock Description IDTransaction tradedate
------------- -------------------------------------------------- ----------------------------------------------------- --------------- ------------- ---------------------------
87 ADVANTAGE CONVERTIBLE SECURITES 25.0 Redemption 13436 2004-07-25 00:00:00.000
87 EPSILON USD 250.0 SUBSCRIPTION 13435 2004-07-26 00:00:00.000
87 ADVANTAGE CONVERTIBLE SECURITES 50.0 OpeningBalance 0 1900-01-01 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources