Prev: This query is typed incorrectly or is too complex to be evalua
Next: Time Calculation
From: carl on 26 Mar 2010 15:29
I am using these 2 queries:

CrossTab1
SELECT UnderlyingSymbol, Date, Sum(TradeVolume) AS [Total Volume]
FROM BTA_Trade_20100326143051
GROUP BY UnderlyingSymbol, Date;


TRANSFORM Sum([TradeVolume]/[Total Volume]) AS Expr1
SELECT BTA_Trade_20100326143051.FirmId,
BTA_Trade_20100326143051.UnderlyingSymbol
FROM BTA_Trade_20100326143051 INNER JOIN CrossTab1 ON
BTA_Trade_20100326143051.UnderlyingSymbol = CrossTab1.UnderlyingSymbol
GROUP BY BTA_Trade_20100326143051.FirmId,
BTA_Trade_20100326143051.UnderlyingSymbol
PIVOT BTA_Trade_20100326143051.Date;

I am trying to get the result of the 2nd query to produce a table that will
show me the % of total TradeVolume for each FirmId for each Date, categorized
by UnderlyingSymbol.

FirmId UnderlyingSymbol 20100316
BOX005 XLNX
BOX017 XLNX 0.04
BOX050 XLNX
BOX051 XLNX
BOX411 XLNX
BOX501 XLNX 0.41
BOX520 XLNX
BOX549 XLNX 0.39
BOX792 XLNX
BOX910 XLNX 0.43
BOX916 XLNX
BOX917 XLNX
BOX918 XLNX 1.82
BOX919 XLNX 0.90
BOX937 XLNX 0.13
BOX979 XLNX
BOX980 XLNX 1.12
BOX982 XLNX 0.04
BOX983 XLNX


Which is not the correct. Sum of the numbers should be 100%.

Can someone help me trouble shoot ?

Thank you in advance.
From: KARL DEWEY on 28 Mar 2010 18:41
Did you check the data comming out of first query?

Your second query is showing '20100316' where it should be a date. Is your
date field actuall a datetime field?

--
Build a little, test a little.


"carl" wrote:

> I am using these 2 queries:
>
> CrossTab1
> SELECT UnderlyingSymbol, Date, Sum(TradeVolume) AS [Total Volume]
> FROM BTA_Trade_20100326143051
> GROUP BY UnderlyingSymbol, Date;
>
>
> TRANSFORM Sum([TradeVolume]/[Total Volume]) AS Expr1
> SELECT BTA_Trade_20100326143051.FirmId,
> BTA_Trade_20100326143051.UnderlyingSymbol
> FROM BTA_Trade_20100326143051 INNER JOIN CrossTab1 ON
> BTA_Trade_20100326143051.UnderlyingSymbol = CrossTab1.UnderlyingSymbol
> GROUP BY BTA_Trade_20100326143051.FirmId,
> BTA_Trade_20100326143051.UnderlyingSymbol
> PIVOT BTA_Trade_20100326143051.Date;
>
> I am trying to get the result of the 2nd query to produce a table that will
> show me the % of total TradeVolume for each FirmId for each Date, categorized
> by UnderlyingSymbol.
>
> FirmId UnderlyingSymbol 20100316
> BOX005 XLNX
> BOX017 XLNX 0.04
> BOX050 XLNX
> BOX051 XLNX
> BOX411 XLNX
> BOX501 XLNX 0.41
> BOX520 XLNX
> BOX549 XLNX 0.39
> BOX792 XLNX
> BOX910 XLNX 0.43
> BOX916 XLNX
> BOX917 XLNX
> BOX918 XLNX 1.82
> BOX919 XLNX 0.90
> BOX937 XLNX 0.13
> BOX979 XLNX
> BOX980 XLNX 1.12
> BOX982 XLNX 0.04
> BOX983 XLNX
>
>
> Which is not the correct. Sum of the numbers should be 100%.
>
> Can someone help me trouble shoot ?
>
> Thank you in advance.
From: carl on 30 Mar 2010 10:52
Hi Karl.

I ran the 1st qry - CrossTab1 and the output is like this:

UnderlyingSymbol Date Total Volume
A 20100316 20
A 20100318 282
A 20100319 92
A 20100322 422
A 20100323 12
A 20100324 324


The 20100316 is a date - March 16, 2010.

I still can't figure out how to get the result I need - a table that will
show me the % of total TradeVolume for each FirmId for each Date, categorized
by UnderlyingSymbol

"KARL DEWEY" wrote:

> Did you check the data comming out of first query?
>
> Your second query is showing '20100316' where it should be a date. Is your
> date field actuall a datetime field?
>
> --
> Build a little, test a little.
>
>
> "carl" wrote:
>
> > I am using these 2 queries:
> >
> > CrossTab1
> > SELECT UnderlyingSymbol, Date, Sum(TradeVolume) AS [Total Volume]
> > FROM BTA_Trade_20100326143051
> > GROUP BY UnderlyingSymbol, Date;
> >
> >
> > TRANSFORM Sum([TradeVolume]/[Total Volume]) AS Expr1
> > SELECT BTA_Trade_20100326143051.FirmId,
> > BTA_Trade_20100326143051.UnderlyingSymbol
> > FROM BTA_Trade_20100326143051 INNER JOIN CrossTab1 ON
> > BTA_Trade_20100326143051.UnderlyingSymbol = CrossTab1.UnderlyingSymbol
> > GROUP BY BTA_Trade_20100326143051.FirmId,
> > BTA_Trade_20100326143051.UnderlyingSymbol
> > PIVOT BTA_Trade_20100326143051.Date;
> >
> > I am trying to get the result of the 2nd query to produce a table that will
> > show me the % of total TradeVolume for each FirmId for each Date, categorized
> > by UnderlyingSymbol.
> >
> > FirmId UnderlyingSymbol 20100316
> > BOX005 XLNX
> > BOX017 XLNX 0.04
> > BOX050 XLNX
> > BOX051 XLNX
> > BOX411 XLNX
> > BOX501 XLNX 0.41
> > BOX520 XLNX
> > BOX549 XLNX 0.39
> > BOX792 XLNX
> > BOX910 XLNX 0.43
> > BOX916 XLNX
> > BOX917 XLNX
> > BOX918 XLNX 1.82
> > BOX919 XLNX 0.90
> > BOX937 XLNX 0.13
> > BOX979 XLNX
> > BOX980 XLNX 1.12
> > BOX982 XLNX 0.04
> > BOX983 XLNX
> >
> >
> > Which is not the correct. Sum of the numbers should be 100%.
> >
> > Can someone help me trouble shoot ?
> >
> > Thank you in advance.
 | 
Pages: 1
Prev: This query is typed incorrectly or is too complex to be evalua
Next: Time Calculation