different ways, but it's just not happening. It's an ordering problem.
I want this: 20 2Yr, 45 3Yr, 3 5Yr, 42 10Yr, 2 30Yr
I'm getting this: 28 5Yr, 9 2Yr, 8 10Yr, 2 30Yr, 45 3Yr
at one time I had them ordered properly, but now for some reason they're not
ordered sequentially. It's pretty much always 5Yr, then 2Yr, 3Yr and 10Yr.
this project is 2-fold. first i do a ton of calculations and insert the
results of which into a stats table. then i report from that stats table.
the relevant portion of the insert is this:
CASE
WHEN LEFT(symbol,3)='USM' THEN '2Yr,' WHEN LEFT(symbol,3)='USL' THEN '3Yr,'
WHEN LEFT(symbol,3)='USN' THEN '5Yr,' WHEN LEFT(symbol,3)='USO' THEN '10Yr'
WHEN LEFT(symbol,3)='USP' THEN '30Yr' END,
i make a couple declarations in the reporting proc:
DECLARE @.termsBBBB varchar(150), @.delimiter char,@.termsEEEE varchar(150)
SET @.delimiter = ','
SELECT @.termsBBBB = CAST(countOftrades as varchar)+ ' ' + term +
COALESCE(@.termsBBBB , '')
FROM (SELECT Sum(countOftrades) as countOfTrades, term from dbo.stats2
WHERE destination = 'BBBB' group by term) a
SELECT @.termsEEEE = CAST(countOftrades as varchar)+ ' ' + term +
COALESCE(@.termsEEEE , '')
FROM (SELECT Sum(countOftrades) as countOfTrades, term from dbo.stats2
WHERE destination = 'EEEE' group by term) a
and i pull it out in the report proc like this:
SELECT Destination as
EndPoint,RTRIM(LEFT(CONVERT(char(16),CAS
T(SUM(countOfTrades) AS
MONEY),1),13)) AS ' #Trades',
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Vol
ume) AS MONEY),1),13)) AS 'Volume
',
RIGHT(' '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS
'Total $ ',+LTRIM(RTRIM(@.termsBBBB)) AS Terms
from dbo.stats2 WHERE destination in ('BBBB')
group by destination
UNION
SELECT Destination as
EndPoint,RTRIM(LEFT(CONVERT(char(16),CAS
T(SUM(countOfTrades) AS
MONEY),1),13)) AS ' #Trades',+' '+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Vol
ume) AS MONEY),1),13)) AS 'Volume
',+' '+
RIGHT(' '+ISNULL (CONVERT(varchar(20),SUM(TotalUSD),1),0.00),20) AS
'Total $ ',+' '+LTRIM(RTRIM(@.termsEEEE)) AS Terms
from dbo.stats2 WHERE destination in ('EEEE')
group by destination
is anybody able to direct me as to why I'm not ordering the values
sequentially?
--LynnI don't see an order by clause in your select. Have you cut-n-pasted
the whole query?|||no, i'm sorry, ak, i didn't cut/paste it all.
but it's just an ORDER BY Terms at the bottom of that UNION.
These are my results:
EndPoint #Trades Volume Total $ Terms
EEEE 53 143 143,015,859.38 30 5Yr,12 2Yr,11
10Yr BBBB 143 465 464,878,125.00 51
5Yr,54 2Yr,38 10Yr
--Lynn
"AK" wrote:
> I don't see an order by clause in your select. Have you cut-n-pasted
> the whole query?
>|||I meant another order by:
create table #term(termname varchar(10), desired_order int)
insert into #term values('5Yr', 2)
insert into #term values('2Yr', 1)
insert into #term values('10Yr',3)
go
create table #totals(termname varchar(10), qty int)
insert into #totals values('5Yr', 200)
insert into #totals values('2Yr', 1)
insert into #totals values('10Yr',123)
go
dECLARE @.termsBBBB varchar(150)
set @.termsBBBB = null
SELECT @.termsBBBB =
case when @.termsBBBB is null then ''
else @.termsBBBB + ',' end + cast(qty as varchar)
+ ' ' + #term.termname
from #term, #totals
where #term.termname = #totals.termname
-- don't forget ORDER BY!
ORDER BY #term.desired_order
select @.termsBBBB
----
----
1 2Yr,200 5Yr,123 10Yr
(1 row(s) affected)
drop table #totals
drop table #term|||well, i'm a bit puzzled now. this is the relevant section of the procedure
-
it is unioned with other similar sections, just each for a different
destination. there is no order by with the insert, just with the report. i
s
that where i'm wrong? is it that simple, just an ORDER BY at the end of the
union in the insertion proc?
SELECT tr.Destination as EndPoint,CASE
WHEN liquidityflag IN ('A','17','18','25','26','27','28','43',
'44')
THEN 'Add'
WHEN liquidityflag IN ('R','02','21','22','24') THEN 'Remove'
WHEN liquidityflag IN ('X','19','30','35','40','51','53','55')
THEN
'Route'
WHEN liquidityflag IN ('O','49','50') THEN 'Opening'
WHEN liquidityflag = '0' THEN 'Unknown'
WHEN liquidityflag = '?' THEN '-'
ELSE ltrim(rtrim(liquidityflag)) END as Liquidity,CASE
WHEN LEFT(symbol,3)='USM' THEN '2Yr,' WHEN LEFT(symbol,3)='USL' THEN '3Yr,'
WHEN LEFT(symbol,3)='USN' THEN '5Yr,' WHEN LEFT(symbol,3)='USO' THEN '10Yr'
WHEN LEFT(symbol,3)='USP' THEN '30Yr' END,
Symbol,CAST(count(*) AS MONEY) as countOfTrades,SUM(tr.lastFillQuantity) AS
'Volume ',
SUM(tr.LastFillQuantity*CAST(tr.LastFillPrice * tik.multiplier AS Money)) AS
'Total $ '
FROM Trading.dbo.Trade tr WITH (NOLOCK) JOIN dba_stat.dbo.TickData tik
ON tr.Destination = tik.Destination
AND LEFT(tr.Symbol,3) IN ('USM','USN','USO')
AND tr.Destination IN ('BTEC','ESPD')
AND tik.Destination IN ('BTEC','ESPD')
AND LEFT(tr.Symbol,3) = tik.Product
--AND timeofexecution >= left(getdate()-0, 11)
AND timeofexecution BETWEEN dateadd(day,-1,
cast(convert(char(10),getdate(),120) + ' 16:10:00' as datetime) )
AND dateadd(day,-0, cast(convert(char(10),getdate(),120) + ' 16:10:00' as
datetime))
GROUP BY tr.Destination,CASE
WHEN liquidityflag IN ('A','17','18','25','26','27','28','43',
'44') THEN
'Add'
WHEN liquidityflag IN ('R','02','21','22','24') THEN 'Remove'
WHEN liquidityflag IN ('X','19','30','35','40','51','53','55')
THEN
'Route'
WHEN liquidityflag IN ('O','49','50') THEN 'Opening'
WHEN liquidityflag = '0' THEN 'Unknown'
WHEN liquidityflag = '?' THEN '-'
ELSE ltrim(rtrim(liquidityflag)) END,tr.Symbol,CASE
WHEN LEFT(symbol,3)='USM' THEN '2Yr,' WHEN LEFT(symbol,3)='USL' THEN '3Yr,'
WHEN LEFT(symbol,3)='USN' THEN '5Yr,' WHEN LEFT(symbol,3)='USO' THEN
'10Yr' WHEN LEFT(symbol,3)='USP' THEN '30Yr' END
--
Lynn
"AK" wrote:
> I meant another order by:
> create table #term(termname varchar(10), desired_order int)
> insert into #term values('5Yr', 2)
> insert into #term values('2Yr', 1)
> insert into #term values('10Yr',3)
> go
> create table #totals(termname varchar(10), qty int)
> insert into #totals values('5Yr', 200)
> insert into #totals values('2Yr', 1)
> insert into #totals values('10Yr',123)
> go
> dECLARE @.termsBBBB varchar(150)
> set @.termsBBBB = null
> SELECT @.termsBBBB =
> case when @.termsBBBB is null then ''
> else @.termsBBBB + ',' end + cast(qty as varchar)
> + ' ' + #term.termname
> from #term, #totals
> where #term.termname = #totals.termname
> -- don't forget ORDER BY!
> ORDER BY #term.desired_order
> select @.termsBBBB
>
>
> ----
----
> 1 2Yr,200 5Yr,123 10Yr
> (1 row(s) affected)
> drop table #totals
> drop table #term
>|||I threw the order by down there, AK, it didn't happen. But I wasn't
expecting it. See, the insert fills a stats table with values like this:
'2Yr,' '3Yr,' '5Yr,' etc. The comma is part of the value inserted. ORDE
R
BY in the insert proc changed nothing. My resultset is still this:
54 5Yr,67 2Yr,41 10Yr
32 5Yr,16 2Yr,15 10Yr
-- Lynn
"AK" wrote:
> I meant another order by:
> create table #term(termname varchar(10), desired_order int)
> insert into #term values('5Yr', 2)
> insert into #term values('2Yr', 1)
> insert into #term values('10Yr',3)
> go
> create table #totals(termname varchar(10), qty int)
> insert into #totals values('5Yr', 200)
> insert into #totals values('2Yr', 1)
> insert into #totals values('10Yr',123)
> go
> dECLARE @.termsBBBB varchar(150)
> set @.termsBBBB = null
> SELECT @.termsBBBB =
> case when @.termsBBBB is null then ''
> else @.termsBBBB + ',' end + cast(qty as varchar)
> + ' ' + #term.termname
> from #term, #totals
> where #term.termname = #totals.termname
> -- don't forget ORDER BY!
> ORDER BY #term.desired_order
> select @.termsBBBB
>
>
> ----
----
> 1 2Yr,200 5Yr,123 10Yr
> (1 row(s) affected)
> drop table #totals
> drop table #term
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
Why are you violating 1NF in the Terms (non-)column? Why are you
converting numerics into strings and playing with them?
The MOST BASIC PRINCIPLE of a tiered archtiecture is that display is
done on the client side, NEVER in the database.
Why are you using MONEY as a data type, knowing about its funny math
problems?
Why are you mixing CAST() and CONVERT()? Do you want code that is hard
to read and maintain?
Like a scratch file? You are doing a very bad job of writing COBOL
using SQL, complete with formatting output images. Reports are done on
the client side, not in the database. Yuo need to start over from the
foundations.|||Joe,
Are you still living back in the world before client/server? Reports are
done in the database, not the client. Only the formatting should be done in
the client, unless it facilitates grouping or ordering.
I don't understand what you have against using temp tables (or scratch files
as you like to call them). I see nothing wrong with caching intermediate
results of a long-running query in a temporary table. In addition judicious
use of temporary tables can reduce the need for true row-based logic
(cursors) or can at least minimize its performance impact.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124488452.417090.259690@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> Why are you violating 1NF in the Terms (non-)column? Why are you
> converting numerics into strings and playing with them?
> The MOST BASIC PRINCIPLE of a tiered archtiecture is that display is
> done on the client side, NEVER in the database.
> Why are you using MONEY as a data type, knowing about its funny math
> problems?
> Why are you mixing CAST() and CONVERT()? Do you want code that is hard
> to read and maintain?
>
a stats table. then I report from that stats table.<<
> Like a scratch file? You are doing a very bad job of writing COBOL
> using SQL, complete with formatting output images. Reports are done on
> the client side, not in the database. Yuo need to start over from the
> foundations.
>|||did you try to add ORDER BY here:
DECLARE @.termsBBBB varchar(150), @.delimiter char,@.termsEEEE
varchar(150)
SET @.delimiter = ','
SELECT @.termsBBBB = CAST(countOftrades as varchar)+ ' ' + term +
COALESCE(@.termsBBBB , '')
FROM (SELECT Sum(countOftrades) as countOfTrades, term from dbo.stats2
WHERE destination = 'BBBB' group by term
--ORDER BY HERE
ORDER BY ...
) a|||>>Why are you violating 1NF in the Terms (non-)column?
The concept that *result sets* must conform to normalization rules is
kinda new.
So if I display
select customer.first_name, customer.last_name, order.order_date,
order.amount
from ...
no PKs, no nothing, the result set is not in an NF, so what?
I think some of your result sets in your own books aren't in NF either
That's what her customers pay her for. Frequently it is more efficient
to have just one person do everything
Why should it be profitable for our business? How do you know how many
tiers the OP's applications have?
>Like a scratch file? You are doing a very bad job of writing COBOL
>using SQL, complete with formatting output images. Reports are done on
>the client side, not in the database. Yuo need to start over from the
>foundations.
I think you are giving a very poor advice. So far MS SQL Server may
have problems dealing with too complex queries, involving too many
tables and or too many calculations. So using a temporary table to
store intermediate results, so that each step is not too complex, is an
easy and simple way to get around that problem
Whatever, I have copies of SQL for Smarties and SQL Puzzles, I bought
them in our local Barnes and Noble like 6 or 7 years ago, just picked
them up from a shelf. At that time they used to carry several copies of
each of Joe Celko's books. The last time I stopped by in the same
bookstore, maybe a month ago, they still have a lot of books on
databases, including lots of books by Tom Kyte, Ken Henderson, Craig
Mullins, but this time there was not a single book by Mr.Celko ... I
wanted to p
into the new one on programming style, but the assistanttold me the book is available only online
No comments:
Post a Comment