SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL

Author

Topic

sazid
Starting Member

2 Posts

Posted - 2006-11-27 : 02:42:02

I am executing the following query.

I want to make another group of all sell type into one, basically i want to display 2 rows one for Buy and another for sell , which gives me the sum of all sell type.

Please advice the best way to achieve it .. without creating a temp table.

-----
SELECT
case o.BS_TYPE
when '1'then 'BUY'
when '2'then 'SELL'
when '5'then 'SPLSELL'
When '6' then 'EXTSELL'
end as 'Buy/Sale',
o.BUY_SALE_CODE as 'ID',
sum(e.UNIT_BUY_SALE) as 'Quantity',
round((sum(e.UNIT_BUY_SALE*e.AVG_PRICE)/sum(e.UNIT_BUY_SALE)),4) as 'Price',
o.BUY_SELL_CURRENCY as 'Currency,
MAX(convert(char(8), e.CREATED, 112)) as 'BUY_SELL_DATE',
FROM
BSORDERS o noholdlock,
BSEXECUTIONS e noholdlock,
where
e.CREATED>='20061118 00:00:00'
and
e.CREATED < '20061127 22:22:22'
and
o.ORDER_ID = e.ORDER_ID
and
o.ORDER_ID = r.ORDER_ID
GROUP BY
o.BS_TYPE,o.BUY_SALE_CODE,o.BUY_SELL_CURRENCY
ORDER BY BS_TYPE DESC

---

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 03:00:48

You reference a table R. Where is that table?

SELECT		case o.BS_TYPE
			when '1'then 'BUY'
			when '2'then 'SELL'
			when '5'then 'SPLSELL'
			When '6' then 'EXTSELL'
		end as 'Buy/Sale',
		o.BUY_SALE_CODE as 'ID',
		sum(e.UNIT_BUY_SALE) as 'Quantity',
		round((sum(e.UNIT_BUY_SALE * e.AVG_PRICE) / sum(e.UNIT_BUY_SALE)), 4) as 'Price',
		o.BUY_SELL_CURRENCY as 'Currency', 
		MAX(convert(char(8), e.CREATED, 112)) as 'BUY_SELL_DATE',
FROM		BSORDERS o noholdlock
inner join	BSEXECUTIONS e noholdlock on o.ORDER_ID = e.ORDER_ID 
where		e.CREATED >= '20061118 00:00:00'
		and e.CREATED < '20061127 22:22:22'
GROUP BY	case o.BS_TYPE
			when '1'then 'BUY'
			when '2'then 'SELL'
			when '5'then 'SPLSELL'
			When '6' then 'EXTSELL'
		end,
		o.BUY_SELL_CURRENCY,
		o.BUY_SALE_CODE
ORDER BY	o.BS_TYPE DESC

Peter Larsson
Helsingborg, Sweden

sazid
Starting Member

2 Posts

Posted - 2006-11-27 : 03:17:08

To be precise the Query is --

SELECT
case o.SIDE
when '1' then 'B'
when '2' then 'S'
when '5' then 'SS'
when '6' then 'SX'
end as 'B/S',
o.RIC as 'Stock',
sum(e.LAST_SHARES) as 'Quantity',
round((sum(e.LAST_SHARES*e.LAST_PX)/sum(e.LAST_SHARES)),4) as 'Price',
o.TRADING_CURRENCY as 'Currency',
r.FUT_SETT_DATE as 'SettlementDate',
FROM
ORD o noholdlock,
EXEC e noholdlock,
RES r noholdlock
where
e.CREATED>='$startDate'
and
e.CREATED < '$endDate'
and
o.ORDER_ID = e.ORDER_ID
and
o.ORDER_ID = r.ORDER_ID
and
e.EXEC_ID = r.EXEC_ID
and
r.EXEC_TYPE in ('1','2')
and
o.CLIENT_CODE = '$clientCode'
GROUP BY
o.SIDE,o.RIC,o.TRADING_CURRENCY,o.BOOK,r.FUT_SETT_DATE
ORDER BY o.SIDE DESC

I will try out the query -- Thanks a lot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 07:21:02

>>EXEC e noholdlock,

Is this part of your select statement?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources