Cross tab or CASE ?
-
I really need some assistance w/this. it is quite urgent. I'm not sure whether to do this as crosstab/pivot, or whether I can just CASE through it. See, I need a procedure that will provide me the results of each of the following -- I then will just mail the results as a report -- just one report. Is someone able to help me with this?
--Number of trades
select count(*) AS 'Total Trades' FROM Trade
go
--Number of trades per symbol
select count(*) AS 'Total Trades', Symbol FROM Trade
group by symbol
order by Symbol
go
--Volume of shares traded
select SUM(LastFillQuantity) AS 'Total Share Volume' FROM Trade
go
--Volume of futures contracts traded
select SUM(LastFillQuantity) AS 'Total Futures Volume' FROM Trade
WHERE destination in ('XEUR','LIFFE','CBOT','CME')
go
--Number of trades by endpoint (ECN/exchange)
select count(*) AS 'Total Trades', Destination as EndPoint FROM TRADE
where Destination IS NOT NULL
group by destination
order by Destination
go
--Volume of shares/contracts by endpoint.
select SUM(LastFillQuantity) as 'Total Share Volume', Destination as EndPoint FROM TRADE
where Destination IS NOT NULL
group by destination
order by destination
go
-
Not sure how you want this displayed
This is the easiest:
Column 1 Column 2 Total Trades Total Value Trades for Symbol1 Value That can be achieved by unioning the selects together
select '-Total-' AS Title, count(*) AS 'Total Trades' FROM Trade
union
select Symbol AS Title, count(*) AS 'Total Trades' FROM Trade
group by symbol
order by 1
and similar for the others
If you want the titles across the top, that's a lot more work, and is maybe better suited for doing in a reporting tool.
Do you have access to either MS Access or Excel? They might be better for formatting.
Is this a once off, or will you be running this report regularly?
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is difficult to tell exactly what you want. I think you should be able to get what you want in one SQL statement.
My suggestion is to build your statement one result column at a time, until you have all the result columns you need.
As an example of how powerful a single SQL query can be, see the code below from our system. It is similar to what you want to do, in that extracting a number of totals from a set of trades.
SELECT
RunType
,TradesetRunId
,BIC
,CurrCode
,Sum(TotalBuyInstruction) AS TotalBuyInstruction
,Sum(TotalSellInstruction) AS TotalSellInstruction
,Sum(CountTotal) AS CountTotal
FROM (SELECT
st.RunType
,dt.TradeSetRunId
,CASE ct.Category
WHEN 'MB' THEN t.MemberBic
WHEN 'MS' THEN t.MemberBic
WHEN 'CB' THEN t.CPMemberBic
WHEN 'CS' THEN t.CPMemberBic
END AS BIC
,CASE ct.Category
WHEN 'MB' THEN t.BuyCode
WHEN 'MS' THEN t.SellCode
WHEN 'CB' THEN t.BuyCode
WHEN 'CS' THEN t.SellCode
END AS CurrCode
,Cast(CASE
WHEN ct.Category = 'MB' AND t.Source IN (dt.IOSwapSetID, 'D') THEN t.BuyAmount
WHEN ct.Category = 'CS' AND t.Source IN (dt.IOSwapSetID, 'D') THEN t.SellAmount
ELSE 0
END AS Numeric(18,2)) AS TotalBuyInstruction
,Cast(CASE
WHEN ct.Category = 'MS' AND t.Source IN (dt.IOSwapSetID, 'D') THEN t.SellAmount
WHEN ct.Category = 'CB' AND t.Source IN (dt.IOSwapSetID, 'D') THEN t.BuyAmount
ELSE 0
END AS Numeric(18,2)) AS TotalSellInstruction
,Cast(CASE
WHEN t.Source IN (dt.IOSwapSetID, 'D') THEN 1
ELSE 0
END AS Int) AS CountTotal
FROM tb_newBATCH_TradeSets dt
INNER JOIN tb_newBATCH_Control st
ON dt.ScenarioNumber = st.ScenarioNumber
AND dt.IOSwapSetID = st.IOSwapSetID
AND dt.FailOriginalMember <> st.FailOriginalMember
INNER JOIN tb_Trade t
ON t.TradeSetID = dt.TradeSetID
CROSS JOIN
(SELECT 'MB' AS Category -- Member Buy
UNION ALL SELECT 'MS' -- Member Sell
UNION ALL SELECT 'CB' -- Counterparty Buy
UNION ALL SELECT 'CS' -- Counterparty Sell
) AS ct
) AS et
GROUP BY RunType,TradesetRunID,BIC,CurrCode
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I think you wolud use a temp table, like #Temp1 (Caption varchar(50), value decimal) ... make data type of value field match your requirements... an sql_variant would help...
CREATE TABLE #Temp1 (Caption varchar(50), value decimal)
INSERT INTO #Temp1 VALUES ('Number of trades',null)
select 'Total Trades', count(*) INTO #Temp1 FROM Trade
INSERT INTO #Temp1 VALUES ('Number of trades per symbol', null')
select Symbol, count(*) INTO #Temp1 FROM Trade
group by symbol
order by Symbol
INSERT INTO #Temp1 VALUES ('Volume of shares traded',null)
// continue with other values...
//finally
SELECT * FROM #Temp1
DROP TABLE #Temp1
I don't know your exact needs but i think that would be a practical, but maybe not performant, idea... just try it.
Nicolas DonadioSW Dev.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply