Difficult Query - 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)

Difficult Query

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2004-01-23 : 04:31:35

Hi All

I have been working on this for some time now and cannot figure out how to get the result set I need. any help would be appreciated.



CREATE TABLE [dbo].[CurrentPrices] (
	[CurrentPriceID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[ISIN] [varchar] NULL,
	[BidPrice] [numeric](18, 0) NULL ,
	[OfferPrice] [numeric](18, 0) NULL ,
	[MarketMaker] [varchar] (50) 
	
) 


insert into currentprices values ('GB0033163287',10,13,1)
insert into currentprices values ('GB0033163287',12,14,2)
insert into currentprices values ('GB0033163287',12,15,3)
insert into currentprices values ('GB0031884421',25,26,1)
insert into currentprices values ('GB0031884421',10,13,2)

So the table will look like ( I have abbreviated the column names so I can fit them in the page)

CPriceID    ISIN            BPrice    OPrice  MM                  
1           GB0033163287    10         13     1
2           GB0033163287    12         14     2
3           GB0033163287    12         15     3
4           GB0031884421    25         26     1
5           GB0031884421    23         24     2

I basically need to return the max(bidprice),Min(offer) and MM for each ISIN.

the result set should look like this



ISIN            BidPrice    OfferPrice  BidMM   OfferMM
GB0033163287    12          13          2,3     1
GB0031884421    25          24          1       2

Where the bidMM /offerMM is the MM making that bid/offer.  If there is more than one MM making that bid than I need to store both of them, the same is the case for OfferMM.

I have got as far as finding the max and min values but cannot find a way to show all the records on one line for each ISIN, i.e bid,offer bidMM, offerMM.

Am i chewing off too much here trying to do it all in SQL or this there a way this can be done.

Your help, as always is appreciated.

OMB

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-23 : 06:26:49

Sorry OMB, couldn't find a 'neat' solution but this SQL2000 T-SQL does work:

DECLARE @Results TABLE (ISIN VARCHAR(20), BidPrice NUMERIC(18,0), OfferPrice NUMERIC(18, 0), BidMM VARCHAR(50), OfferMM VARCHAR(50))

INSERT INTO @Results
SELECT z.ISIN, z.BidPrice, z.OfferPrice, BidMM = x.MarketMaker, OfferMM = y.MarketMaker
FROM
(
SELECT ISIN, BidPrIce = MAX(BidPrice), OfferPrice = MIN(OfferPrice)
FROM CurrentPrices
GROUP BY ISIN
) z
INNER JOIN CurrentPrices x ON z.ISIN = x.ISIN AND z.BidPrice = x.BidPrice
INNER JOIN CurrentPrices y ON z.ISIN = y.ISIN AND z.OfferPrice = y.OfferPrice

-- Update @Results with COALESCE of MarketMakers for each ISIN
DECLARE @ISIN TABLE (id INT IDENTITY, ISIN VARCHAR(20))
DECLARE @nLoop INT, @nCount INT, @ISINValue VARCHAR(20), @BidMM VARCHAR(2000), @OfferMM VARCHAR(2000)
SET @nLoop = 1

INSERT INTO @ISIN SELECT DISTINCT ISIN FROM @Results
SET @nCount = @@ROWCOUNT

WHILE @nLoop <= @nCount
BEGIN
SELECT @ISINValue = ISIN, @BidMM = NULL, @OfferMM = NULL FROM @ISIN WHERE id = @nLoop

SELECT @BidMM = COALESCE(@BidMM + ', ', '') + BidMM FROM @Results WHERE ISIN = @ISINValue GROUP BY BidMM
SELECT @OfferMM = COALESCE(@OfferMM + ', ', '') + OfferMM FROM @Results WHERE ISIN = @ISINValue GROUP BY OfferMM

UPDATE @Results SET BidMM = @BidMM, OfferMM = @OfferMM WHERE ISIN = @ISINValue

SET @nLoop = @nLoop + 1
END

-- Return Result set
SELECT DISTINCT *
FROM @Results

Raymond

OMB
Yak Posting Veteran

88 Posts

Posted - 2004-01-23 : 09:33:53

Cheers Raymond

Did'nt think of doing it this way!

Thanxs

OMB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources