January 17, 2011 at 3:36 am
Hi, I have the following query:
select
@drawdrownPct AS largestDrawDownPct
,avg(pctAccountChange) as avgPctAccountChange
,max(pctAccountChange) as maxPctAccountChange
,min(pctAccountChange) as minPctAccountChange
,AVG (case when b.pctAccountChange > 0.1 then pctAccountChange end) as avgPctAccountChangeWinners
,AVG (case when b.pctAccountChange < -0.1 then pctAccountChange end) as avgPctAccountChangeLosers
from
#BaseTradeStats b
This query returns one row. I would like a query that produces two columns: pctChangeDescription, pctAccountChange which is effectively an unpivot. How would I write this - should I use the UNPIVOT statement? I'm not sure how to write this.
January 18, 2011 at 12:15 am
Keymoo,
It'll be very difficult to show you usable code without having some sample DDL and data, as well as a sample of the desired result. Check the first link in my sig for this.
However, for the unpivot, I don't think that's what you want.
When you unpivot, you'd basically take four columns of repeating data and turn them into two columns. IE: ID, pct1, pct2, pct3, and pct4. This would turn into a 3 column set of ID, PctName, pctValue, with pct1/2/3/4 as the PctName, and the value originally in those columns in pctValue.
Definately need samples to help you out here, but from your sample code, you don't want unpivot.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 1:59 am
Sorry for the tardiness - here's some sample data and the actual query I am running:
set nocount on
CREATE TABLE #BaseTradeStats(
[tradeId] [int] NOT NULL,
[minOrderDateTime] datetime,
[symbol] [char](6) NOT NULL,
[pctAccountChange] [decimal](38, 6) NULL,
CONSTRAINT [PK_BaseTradeStats] PRIMARY KEY NONCLUSTERED
(
[tradeId] ASC
)
)
CREATE CLUSTERED INDEX [basetradestats_cidx] ON #BaseTradeStats
(
[symbol]
)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(116,'Jan 6 2010 3:00:00:000AM','AUDJPY',0.688740)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(99,'Jan 6 2010 7:00:00:000AM','CADJPY',0.509932)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(161,'Jan 21 2010 3:00:00:000PM','CHFJPY',0.885134)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(174,'Jan 6 2010 3:00:00:000PM','EURCHF',-0.179938)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(126,'Jan 14 2010 11:00:00:000AM','EURGBP',0.745437)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(127,'Jan 27 2010 7:00:00:000AM','EURGBP',0.321225)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(63,'Jan 6 2010 3:00:00:000PM','EURJPY',-0.283782)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(64,'Jan 14 2010 3:00:00:000AM','EURJPY',-0.356655)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(65,'Jan 19 2010 3:00:00:000AM','EURJPY',0.893750)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(1,'Jan 19 2010 11:00:00:000AM','EURUSD',1.375000)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(78,'Jan 4 2010 7:00:00:000PM','GBPCHF',1.152172)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(79,'Jan 18 2010 3:00:00:000AM','GBPCHF',1.159340)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(80,'Jan 27 2010 11:00:00:000AM','GBPCHF',0.048542)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(139,'Jan 8 2010 7:00:00:000AM','GBPJPY',-0.391604)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(140,'Jan 26 2010 7:00:00:000AM','GBPJPY',0.282684)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(17,'Jan 7 2010 11:00:00:000AM','GBPUSD',-1.910000)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(18,'Jan 18 2010 3:00:00:000AM','GBPUSD',0.275862)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(150,'Jan 13 2010 11:00:00:000AM','USDCHF',-0.404151)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(152,'Jan 26 2010 3:00:00:000AM','USDCHF',0.454544)
INSERT INTO [#BaseTradeStats] ([tradeId],[minOrderDateTime],[symbol],[pctAccountChange])VALUES(39,'Jan 27 2010 3:00:00:000AM','USDJPY',-0.987500)
-- work out the compound percentage account increase for the set
declare
@compoundChange decimal (38,6),
@accountGrowth decimal (10,1)
;
-- calculate the largest drawdown for the trade series
select row_number() over (order by minOrderDateTime) as Row_Order
, minOrderDateTime, sum(pctaccountchange) as net_pctChange
into #grouped_Performance
from #BaseTradeStats b
group by minOrderDateTime
create clustered index ix_row_order on #grouped_Performance(Row_Order)
alter table #grouped_Performance
add Running_Sum decimal(38,6)
DECLARE
@runningValue [DECIMAL](38, 6), -- to hold the intermediate values
@Sequence INT -- verify that the Sequence of processing is the same as the reqired Sequence
SET @Sequence=0
SET @runningValue=1000
;
WITH SafeTable AS
(
SELECT net_pctChange
, Row_Order AS Sequence -- define the sequence for data processing
, Running_Sum
FROM #grouped_Performance
)
UPDATE SafeTable
SET
@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 END, -- double check to be still in sync
@runningValue = (@runningValue*(1+net_pctChange/100)), -- calculate the new value
Running_Sum = @runningValue -- store the value in the requested format
FROM SafeTable
WITH (TABLOCKX) -- IMPORTANT: lock the table to avoid data changes in between
OPTION (MAXDOP 1); -- avoid parallel processing to ensure proper sequence
declare
@drawdrownPct decimal(5,2)
;with cte as (
select gpa.minOrderDateTime as drawdownPeakDateTime
, gpb.minOrderDateTime as drawdownTroughDateTime
, (gpa.Running_Sum - gpb.Running_Sum) / gpa.Running_Sum * 100 as pct_drop
from #grouped_Performance gpa
join #grouped_Performance gpb
on gpa.Row_Order < gpb.Row_Order
and gpa.Running_Sum > gpb.Running_Sum
)
select top 1
@drawdrownPct = pct_drop * -1
from cte
order by pct_drop desc
option (maxrecursion 10000);
-- I need help with this bit so that the results are returned as two columns unpivoted
select
@drawdrownPct AS largestDrawDownPct
,avg(pctAccountChange) as avgPctAccountChange
,max(pctAccountChange) as maxPctAccountChange
,min(pctAccountChange) as minPctAccountChange
,AVG (case when b.pctAccountChange > 0.1 then pctAccountChange end) as avgPctAccountChangeWinners
,AVG (case when b.pctAccountChange < -0.1 then pctAccountChange end) as avgPctAccountChangeLosers
from
#BaseTradeStats b
drop table #grouped_Performance
drop table #BaseTradeStats
January 18, 2011 at 9:38 am
Keymoo,
I scanned through your code twice, and nowhere can I find anything that seems like it would translate to your pctDescription column. Am I missing something here, or does it not exist yet in any capacity?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 12:12 pm
Hi Craig,
I basically need to transpose columns to rows. So instead of the results looking like this:
largestDrawDownPct avgPctAccountChange maxPctAccountChange minPctAccountChange avgPctAccountChangeWinners avgPctAccountChangeLosers
------------------ ------------------- ------------------- ------------------- -------------------------- -------------------------
-3.49 0.213936 1.375000 -1.910000 0.728651 -0.644804
I want the results to look like this:
description pctAccountChange
--------------------------- ----------------
largestDrawDownPct -3.49
avgPctAccountChange 0.213936
maxPctAccountChange 1.375000
minPctAccountChange -1.910000
avgPctAccountChangeWinners 0.728651
avgPctAccountChangeLosers -0.644804
January 18, 2011 at 1:01 pm
keymoo (1/18/2011)
I want the results to look like this:
description pctAccountChange--------------------------- ----------------
largestDrawDownPct -3.49
avgPctAccountChange 0.213936
maxPctAccountChange 1.375000
minPctAccountChange -1.910000
avgPctAccountChangeWinners 0.728651
avgPctAccountChangeLosers -0.644804
*facepalms* I'm sorry, I got lost in translation. You're absolutely right, you want unpivot.
WITH cte1 AS
(SELECT
CONVERT( DECIMAL(19,6), -3.49) AS largestDrawDownPct,
CONVERT( DECIMAL(19,6), 0.213936 ) AS avgPctAccountChange,
CONVERT( DECIMAL(19,6), 1.375000 ) AS maxPctAccountChange,
CONVERT( DECIMAL(19,6), -1.91000 ) AS minPctAccountChange,
CONVERT( DECIMAL(19,6), 0.728651 ) AS avgPctAccountChangeWinners,
CONVERT( DECIMAL(19,6), -0.644804 ) AS avgPctAccountChangeLosers
)
select
[Description],
pctAccountChange
from
cte1
UNPIVOT ( pctAccountChange FOR [Description]
IN (largestDrawDownPct, avgPctAccountChange, maxPctAccountChange,
minPctAccountChange, avgPctAccountChangeWinners, avgPctAccountChangeLosers)
) AS unpvt
Note, you'll want to make sure you do the convert decimal on the result sets to make sure they're the same type, or the UNPIVOT tends to get cranky.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply