Experts,
I've got a table called "Trade" that includes the following fields:
CompanyID (int)
Order_Type (nvarchar)
Price (float)
QTY (int)
Investor_Number (nvarchar)
ValueDate (smalldatetime)
I need to create a query of each investor that consolidates his trade for a range of dates (valueDate BETWEEN @startingDate AND @endingDate).
The query has to split the result into the two Order_Type values (Purchase/Sale).
The query has to output the following:
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--
Purchases | Sales |
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--
CompanyID Average of Price Sum of QTY (Price * QTY) AS Amount | CompanyID Average of Price Sum of QTY (Price * QTY) AS Amount |
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--
Note: The best thing I managed to do was to create two different views having the aggregates that I want. One is filtered by Order_Type='Sale' and the other one is filtered by 'Purchase'. It worked fine with a single date. But, the problem I faced was with a range of dates; it grouped by dates and did not consolidate them.
The query has to consolidate all company IDs together and group by only Company IDs, but not dates. I was obliged to choose to show the valueDate field in the view to be able to inner join both views so that I read relevant information. But again, that resulted in not consolidating all dates chosen for the same companyID.
Start Free Trial
