I've been on this one for more than a month.  I've tried it countless
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),CAST(SUM(countOfTrades) AS
MONEY),1),13)) AS '   #Trades',
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) 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),CAST(SUM(countOfTrades) AS
MONEY),1),13)) AS '   #Trades',+'   '+
RTRIM(LEFT(CONVERT(char(16),CAST(SUM(Volume) 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?

--Lynn

I 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


Show quoteHide quote

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.  is
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
Show quoteHide quote

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.  ORDER
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


Show quoteHide quote

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.


Show quoteHide quotea stats table.  then I report from that stats table.<<
Show quoteHide quote

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?

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 peek into the new one on programming style, but the assistant
told me the book is available only online

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

if you mean here in the report proc:
.....WHERE destination  = 'BBBB' group by term, order by term) a

then yes, I tried that, but it won't get beyond the syntax.  and, of course,
i've got it down beneath in the union in the report proc like this:

--BBBB/EEEE
SELECT .......................+'   '+LTRIM(RTRIM(@termsBBBB)) AS Term
from dbo.stats2 WHERE destination in ('BBBB')
group by destination

UNION

SELECT ...................+'   '+LTRIM(RTRIM(@termsEEEE)) AS Term
from dbo.stats2 WHERE destination in ('EEEE')
group by destination
order by Term

-- Lynn


Show quoteHide quote

Calculated fields with Days/Dates
Dangers of update statement
Automating database changes
Track Modification Time
Simple Query Question