Grouping Transactions Quarterly by Client

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 2005 Forums

Transact-SQL (2005)

Grouping Transactions Quarterly by Client

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-12-05 : 09:39:29

I have transaction table I am pulling transactions from and can't quite figure out how to get a sum based on a few items.

I am trying to figure out how to show the sum of the InvestorAssumption and group it based on the DealId, then DealTransType and show the sum of the InvestorAssumption.

So I want to see a total of the InvestorAssumption by quarter split up by DealTransType, then DealId.

Not sure how to do this and any help would be greatly appreciated.

Here is my current view.

SELECT     TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter, 
                      DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType, 
                      dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount, 
                      dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption, 
                      dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual
FROM         dbo.DealTransactions INNER JOIN
                      dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN
                      dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId
GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId, 
                      dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100, 
                      dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId, 
                      dbo.DealTransactions.DealTransDate
ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName, 
                      dbo.DealTransactions.DealTransAmount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-05 : 11:38:01

SELECT t.TransDateQuart,t.DealTransType,t.DealId,SUM(t.InvestorAssumption) AS 'InvestorAssumption' FROM
(SELECT TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter,
DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType,
dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount,
dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption,
dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual
FROM dbo.DealTransactions INNER JOIN
dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN
dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId
GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId,
dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100,
dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId,
dbo.DealTransactions.DealTransDate
ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName,
dbo.DealTransactions.DealTransAmount
) t
GROUP BY t.TransDateQuart,t.DealTransType,t.DealId

what is the purpose of putting TOP 100 percent in query? I dont think thats required

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources