complex sql server 2005 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 2005 Forums

Transact-SQL (2005)

complex sql server 2005 query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 07:35:52

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
BpriceSize decimal(12, 8),
Aprice decimal(12, 8),
ApriceSize decimal(12, 8)
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...

I believe this insert query produces the same data as above
select 1, 1, 'sourceA', 'SecA', 100.2, 2, 99.12, 1, '2007-11-07 16:24:31.297'
union all select 2, 2, 'sourceW', 'SecH', 95.7,null, 89.43,null, '2007-11-07 16:24:31.297'
union all select 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297'
union all select 4, 4, 'SourceQ', 'SecZ', null,null,87.98,null, '2007-11-07 16:24:31.297'
union all select 5, 5, 'SourceJ', 'SecH', 100.2,null, 99.12, 2, '2007-11-07 16:24:31.297'
union all select 6, 6, 'SourceK', 'SecU', null,null,null,null,'2007-11-07 16:24:31.297'
union all select 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11,null, '2007-11-07 16:24:31.297'
union all select 8, 1, 'sourceA', 'SecA', 100.2, 6, 99.12, 2, '2007-11-07 16:26:15.123'
union all select 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43,null, '2007-11-07 16:26:15.123'
union all select 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33,null, '2007-11-07 16:26:15.123'
union all select 11, 4, 'SourceQ', 'SecZ', 16.98,null, 87.98,null, '2007-11-07 16:26:15.123'
union all select 12, 5, 'SourceJ', 'SecH', 100.2, 1, 99.12, 2, '2007-11-07 16:26:15.123'
union all select 13, 6, 'SourceK', 'SecU', null,null,null,null,'2007-11-07 16:26:15.123'
union all select 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123'
union all select 15, 1, 'sourceA', 'SecA', 100.2, 1, 87.11, 1, '2007-11-07 16:26:15.123'
union all select 16, 2, 'sourceW', 'SecH', 99.66,null, 89.43, 2, '2007-11-07 16:26:15.123'
union all select 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33,null, '2007-11-07 16:26:15.123'
union all select 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123'
union all select 19, 5, 'SourceJ', 'SecH', 100.2, 3, 99.12, 3, '2007-11-07 16:26:15.123'
union all select 20, 6, 'SourceK', 'SecU', null,null,null,null,'2007-11-07 16:26:15.123'
union all select 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCode

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-08 : 08:06:26

quick query on your sample data...
in the table definition you got columns for Aprice and Bprice, but in the data you seem to have Apricesize and Bpricesize as well.
are they meant to be in this table or populated from elsewhere? are they relevant to the logic for the resultset you want?

also, if you could format your sample so people can just run in it will make it easier for someone to help...
i.e.
insert into tbldata
select 1, 'source A', ....

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-08 : 08:15:44

ok, i see you've edited it to show size now, but the sample data is still confusing. i've guessed so far that you mean nulls where data seems to be missing. can you confrim...?


insert into #tbldata

		select 1,1, 'sourceA', 'SecA', 100.2, 2, 99.12, 1, '2007-11-07 16:24:31.297'
union all	select 2,2, 'sourceW', 'SecH', 95.7, null, 89.43,null, '2007-11-07 16:24:31.297'
union all	select 3,3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297'
union all	select 4,4, 'SourceQ', 'SecZ', 87.98,null,null,null, '2007-11-07 16:24:31.297'
union all	select 5,5, 'SourceJ', 'SecH', 100.2, null,99.12, 2, '2007-11-07 16:24:31.297'
union all	select 6,6, 'SourceK', 'SecU', null,null,null,null, '2007-11-07 16:24:31.297'
union all	select 7,7, 'SourceT', 'SecA', 50.56, 3, 87.11,null, '2007-11-07 16:24:31.297'

or better yet, post it like this yourself, how you want it

Em

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 08:18:03

yes, empty is referring to null.
Ok, I will try to have the insert sysntax too.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 08:32:22

This is the best I can do with your piece of sample data.
Your business rules are not that clear either...

New code further below due to new rules and sample data

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 08:38:03

Hi Peso,
This is good but may be I should have mentioned that if a souorce has not given a price i.e. null then it does not mean that is the lowest price. So if there are other prices we need to get the lowest of those and ignore the empty one.
Hope you know what I mean.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 09:42:22

Ok, so you posted new sample data...

DECLARE	@Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

INSERT	@Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2, 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7, null, 89.43, null, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', null, null, 87.98, null, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2, null, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', null, null, null, null,'2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, null, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2, 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, null, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, null, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, null, 87.98, null, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2, 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', null, null, null, null,'2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2, 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, null , 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, null, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2, 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', null, null, null, null, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'

; WITH a (Security, Aprice, ApriceSize, SourceCode)
AS (
	SELECT		a1.Security,
			a1.Aprice,
			a2.ApriceSize,
			a2.SourceCode
	FROM		(
				SELECT		Security,
						MIN(Aprice) AS Aprice
				FROM		@Sample
				WHERE		Security IN ('SecA', 'SecH', 'SecS', 'SecZ')
						AND Aprice IS NOT NULL
						AND ApriceSize IS NOT NULL
				GROUP BY	Security
			) AS a1
	INNER JOIN	(
				SELECT		Security,
						Aprice,
						MAX(ApriceSize) AS ApriceSize,
						SourceCode
				FROM		@Sample
				WHERE		Security IN ('SecA', 'SecH', 'SecS', 'SecZ')
						AND Aprice IS NOT NULL
						AND ApriceSize IS NOT NULL
				GROUP BY	Security,
						Aprice,
						SourceCode
			) AS a2 ON a2.Security = a1.Security AND a2.Aprice = a1.Aprice
), b (Security, Bprice, BpriceSize, SourceCode)
AS (
	SELECT		b1.Security,
			b1.Bprice,
			b2.BpriceSize,
			b2.SourceCode
	FROM		(
				SELECT		Security,
						MAX(Bprice) AS Bprice
				FROM		@Sample
				WHERE		Security IN ('SecA', 'SecH', 'SecS', 'SecZ')
						AND Bprice IS NOT NULL
						AND BpriceSize IS NOT NULL
				GROUP BY	Security
			) AS b1
	INNER JOIN	(
				SELECT		Security,
						Bprice,
						MAX(BpriceSize) AS BpriceSize,
						SourceCode
				FROM		@Sample
				WHERE		Security IN ('SecA', 'SecH', 'SecS', 'SecZ')
						AND Bprice IS NOT NULL
						AND BpriceSize IS NOT NULL
				GROUP BY	Security,
						Bprice,
						SourceCode
			) AS b2 ON b2.Security = b1.Security AND b2.Bprice = b1.Bprice
), a2 (Security, Aprice, ApriceSize, Aprice_SourceCode)
AS (
	SELECT		a1.Security,
			a1.Aprice,
			a1.ApriceSize,
			STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + a2.SourceCode FROM a AS a2 WHERE a2.Security = a1.Security ORDER BY ', ' + a2.SourceCode FOR XML PATH('')), 1, 2, '')
	FROM		a AS a1
), b2 (Security, Bprice, BpriceSize, Bprice_SourceCode)
AS (
	SELECT		b1.Security,
			b1.Bprice,
			b1.BpriceSize,
			STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + b2.SourceCode FROM b AS b2 WHERE b2.Security = b1.Security ORDER BY ', ' + b2.SourceCode FOR XML PATH('')), 1, 2, '')
	FROM		b AS b1
)

SELECT		bx.Security,
		bx.Bprice,
		bx.BpriceSize,
		bx.Bprice_SourceCode,
		ax.Aprice,
		ax.ApriceSize,
		ax.Aprice_SourceCode
FROM		(
			SELECT		Security,
					Bprice,
					SUM(BpriceSize) AS BpriceSize,
					Bprice_SourceCode
			FROM		b2
			GROUP BY	Security,
					Bprice,
					Bprice_SourceCode
		) AS bx
INNER JOIN	(
			SELECT		Security,
					Aprice,
					SUM(ApriceSize) AS ApriceSize,
					Aprice_SourceCode
			FROM		a2
			GROUP BY	Security,
					Aprice,
					Aprice_SourceCode
		) AS ax ON ax.Security = bx.Security
ORDER BY	bx.Security

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 11:08:00

WHAT?

You wrote "I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice)." before.

Which is it?

If you can't read the suggestion and learn from it, you should consider a career change.

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 11:09:05

As you know, the data is imported every 2 minutes.
Please note that I am onoy interested in the final set of records i.e. the ones which have the max importDate.

Let's say this is the last set of the imported data...

SELECT 15, 1, 'sourceA', 'SecA', 100.2, 1, 87.11, 1, '2007-11-07 16:26:17.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, null , 89.43, 2, '2007-11-07 16:26:17.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, null, '2007-11-07 16:26:17.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:17.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2, 3, 99.12, 3, '2007-11-07 16:26:17.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', null, null, null, null, '2007-11-07 16:26:17.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:17.123'

If it is ok, please base the query on the maximum ImportDate.
I am not sure where to place the where Max(importDate)

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 11:11:53

Twice in CTE a and twice in cte b.

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 11:14:25

quote:
Originally posted by Peso

Twice in CTE a and twice in cte b.

E 12°55'05.25"
N 56°04'39.16"

Got it, thanks
Will let you know how it goes.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 11:23:10

quote:
Originally posted by Peso

Twice in CTE a and twice in cte b.

E 12°55'05.25"
N 56°04'39.16"

I have modified the query based on the actual table.
But the error is:
Error converting data type varchar to numeric.
I can not see where this error is.
here is the last sql code based on the actual table:
DECLARE @Sample TABLE (ID INT, SourceID smallint, SourceCode VARCHAR(255), Security VARCHAR(255),
Bprice decimal(12, 8), BpriceSize decimal(12, 8), Aprice decimal(12, 8), ApriceSize decimal(12, 8), ImportDate smalldatetime)

INSERT @Sample
select
Price_Source_Import_ID, Source_ID, Source_Code, Security_Name,
Bid_Price, Bid_Size, Ask_Price, replace(Ask_Size, 'E', ''), Import_Date
from
tblPricesSourcesImport
where
Import_Date in (select max(Import_Date) from tblPricesSourcesImport)
--SELECT 1, 1, 'sourceA', 'SecA', 100.2, 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 2, 2, 'sourceW', 'SecH', 95.7, null, 89.43, null, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 4, 4, 'SourceQ', 'SecZ', null, null, 87.98, null, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 5, 5, 'SourceJ', 'SecH', 100.2, null, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 6, 6, 'SourceK', 'SecU', null, null, null, null,'2007-11-07 16:24:31.297' UNION ALL
--SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, null, '2007-11-07 16:24:31.297' UNION ALL
--SELECT 8, 1, 'sourceA', 'SecA', 100.2, 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
--SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, null, '2007-11-07 16:26:15.123' UNION ALL
--SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, null, '2007-11-07 16:26:15.123' UNION ALL
--SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, null, 87.98, null, '2007-11-07 16:26:15.123' UNION ALL
--SELECT 12, 5, 'SourceJ', 'SecH', 100.2, 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
--SELECT 13, 6, 'SourceK', 'SecU', null, null, null, null,'2007-11-07 16:26:15.123' UNION ALL
--SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
--
--SELECT 15, 1, 'sourceA', 'SecA', 100.2, 1, 87.11, 1, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 16, 2, 'sourceW', 'SecH', 99.66, null , 89.43, 2, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, null, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 19, 5, 'SourceJ', 'SecH', 100.2, 3, 99.12, 3, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 20, 6, 'SourceK', 'SecU', null, null, null, null, '2007-11-07 16:26:17.123' UNION ALL
--SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:17.123'

; WITH a (Security, Aprice, ApriceSize, SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a2.ApriceSize,
a2.SourceCode
FROM (
SELECT Security,
MIN(Aprice) AS Aprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security
) AS a1
INNER JOIN (
SELECT Security,
Aprice,
MAX(ApriceSize) AS ApriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security,
Aprice,
SourceCode
) AS a2 ON a2.Security = a1.Security AND a2.Aprice = a1.Aprice
), b (Security, Bprice, BpriceSize, SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b2.BpriceSize,
b2.SourceCode
FROM (
SELECT Security,
MAX(Bprice) AS Bprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security
) AS b1
INNER JOIN (
SELECT Security,
Bprice,
MAX(BpriceSize) AS BpriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security,
Bprice,
SourceCode
) AS b2 ON b2.Security = b1.Security AND b2.Bprice = b1.Bprice
), a2 (Security, Aprice, ApriceSize, Aprice_SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a1.ApriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + a2.SourceCode FROM a AS a2 WHERE a2.Security = a1.Security ORDER BY ', ' + a2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM a AS a1
), b2 (Security, Bprice, BpriceSize, Bprice_SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b1.BpriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + b2.SourceCode FROM b AS b2 WHERE b2.Security = b1.Security ORDER BY ', ' + b2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM b AS b1
)

SELECT bx.Security,
bx.Bprice,
bx.BpriceSize,
bx.Bprice_SourceCode,
ax.Aprice,
ax.ApriceSize,
ax.Aprice_SourceCode
FROM (
SELECT Security,
Bprice,
SUM(BpriceSize) AS BpriceSize,
Bprice_SourceCode
FROM b2
GROUP BY Security,
Bprice,
Bprice_SourceCode
) AS bx
INNER JOIN (
SELECT Security,
Aprice,
SUM(ApriceSize) AS ApriceSize,
Aprice_SourceCode
FROM a2
GROUP BY Security,
Aprice,
Aprice_SourceCode
) AS ax ON ax.Security = bx.Security
ORDER BY bx.Security

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-08 : 12:13:24

Hi,
Edited the sql query again.
this is the latest and it seems to be correct, BUT it does not show the records which have null prices.
Please have a look and see where it can be edited to show even the prices that do not have values i.e. null

Many thanks

DECLARE @Sample TABLE (ID INT, SourceID smallint, SourceCode VARCHAR(255), Security VARCHAR(255),
Bprice decimal(12, 8), BpriceSize decimal(12, 8), Aprice varchar(255), ApriceSize decimal(12, 8), ImportDate smalldatetime)

INSERT @Sample
select
Price_Source_Import_ID, Source_ID, Source_Code, Security_Name,
Bid_Price =
case
when len(Bid_Price) > 1 then replace(Bid_Price, 'H', '')
when dbo.isreallynumeric(Bid_Price) = 1 then convert(decimal(12, 8), Bid_Price)
else null
end,
Bid_Size =
case
when dbo.isreallynumeric(Bid_Size) = 1 then convert(decimal(12, 8), Bid_Size)
else null
end,
Ask_Price =
case
when dbo.isreallynumeric(Ask_Price) = 1 then convert(decimal(12, 8), Ask_Price)
else null
end,
Ask_Size =
case
when len(Ask_Size) > 1 then replace(Ask_Size, 'E', '')
when dbo.isreallynumeric(Ask_Size) = 1 then convert(decimal(12, 8), Ask_Size)
else null
end,
Import_Date
from
tblPricesSourcesImport
where
Import_Date in (select max(Import_Date) from tblPricesSourcesImport)

; WITH a (Security, Aprice, ApriceSize, SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a2.ApriceSize,
a2.SourceCode
FROM (
SELECT Security,
MIN(Aprice) AS Aprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security
) AS a1
INNER JOIN (
SELECT Security,
Aprice,
MAX(ApriceSize) AS ApriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security,
Aprice,
SourceCode
) AS a2 ON a2.Security = a1.Security AND a2.Aprice = a1.Aprice
), b (Security, Bprice, BpriceSize, SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b2.BpriceSize,
b2.SourceCode
FROM (
SELECT Security,
MAX(Bprice) AS Bprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security
) AS b1
INNER JOIN (
SELECT Security,
Bprice,
MAX(BpriceSize) AS BpriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
AND Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
AND ImportDate in (select max(ImportDate) from @Sample)
GROUP BY Security,
Bprice,
SourceCode
) AS b2 ON b2.Security = b1.Security AND b2.Bprice = b1.Bprice
), a2 (Security, Aprice, ApriceSize, Aprice_SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a1.ApriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + a2.SourceCode FROM a AS a2 WHERE a2.Security = a1.Security ORDER BY ', ' + a2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM a AS a1
), b2 (Security, Bprice, BpriceSize, Bprice_SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b1.BpriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + b2.SourceCode FROM b AS b2 WHERE b2.Security = b1.Security ORDER BY ', ' + b2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM b AS b1
)

SELECT bx.Security,
bx.Bprice,
bx.BpriceSize,
bx.Bprice_SourceCode,
ax.Aprice,
ax.ApriceSize,
ax.Aprice_SourceCode
FROM (
SELECT Security,
Bprice,
SUM(BpriceSize) AS BpriceSize,
Bprice_SourceCode
FROM b2
GROUP BY Security,
Bprice,
Bprice_SourceCode
) AS bx
INNER JOIN (
SELECT Security,
Aprice,
SUM(ApriceSize) AS ApriceSize,
Aprice_SourceCode
FROM a2
GROUP BY Security,
Aprice,
Aprice_SourceCode
) AS ax ON ax.Security = bx.Security
ORDER BY bx.Security

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 13:36:59

Things would be SO MUCH EASIER if you could provide which line is giving yuo the error...
Do you want us to guess?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 13:38:49

And now when we are in the furtune telling business, change
AND ImportDate in (select max(ImportDate) from @Sample)

to
AND ImportDate = (select max(ImportDate) from @Sample)

E 12°55'05.25"
N 56°04'39.16"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-09 : 04:03:28

Hi,
Yes, you are right.
I think next time I will be more to the point.
This is the latest sql and it seems to be ok so far.
Still testing it with the results.
Thank you

DECLARE @Sample TABLE (ID INT, SourceID smallint, SourceCode VARCHAR(255), Security VARCHAR(255),
Bprice decimal(12, 8), BpriceSize decimal(12, 8), Aprice varchar(255), ApriceSize decimal(12, 8), ImportDate smalldatetime)

INSERT @Sample
select
Price_Source_Import_ID, Source_ID, Source_Code, Security_Name,
Bid_Price =
case
when len(Bid_Price) > 1 then replace(Bid_Price, 'H', '')
when dbo.isreallynumeric(Bid_Price) = 1 then convert(decimal(12, 8), Bid_Price)
else null
end,
Bid_Size =
case
when dbo.isreallynumeric(Bid_Size) = 1 then convert(decimal(12, 8), Bid_Size)
else null
end,
Ask_Price =
case
when dbo.isreallynumeric(Ask_Price) = 1 then convert(decimal(12, 8), Ask_Price)
else null
end,
Ask_Size =
case
when len(Ask_Size) > 1 then replace(Ask_Size, 'E', '')
when dbo.isreallynumeric(Ask_Size) = 1 then convert(decimal(12, 8), Ask_Size)
else null
end,
Import_Date
from
tblPricesSourcesImport
where
Import_Date = (select max(Import_Date) from tblPricesSourcesImport)

; WITH a (Security, Aprice, ApriceSize, SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a2.ApriceSize,
a2.SourceCode
FROM (
SELECT Security,
MIN(Aprice) AS Aprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
--AND Aprice IS NOT NULL
--AND ApriceSize IS NOT NULL
AND ImportDate = (select max(ImportDate) from @Sample)
GROUP BY Security
) AS a1
INNER JOIN (
SELECT Security,
Aprice,
MAX(ApriceSize) AS ApriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
--AND Aprice IS NOT NULL
--AND ApriceSize IS NOT NULL
AND ImportDate = (select max(ImportDate) from @Sample)
GROUP BY Security,
Aprice,
SourceCode
) AS a2 ON a2.Security = a1.Security AND a2.Aprice = a1.Aprice
), b (Security, Bprice, BpriceSize, SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b2.BpriceSize,
b2.SourceCode
FROM (
SELECT Security,
MAX(Bprice) AS Bprice
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
--AND Bprice IS NOT NULL
--AND BpriceSize IS NOT NULL
AND ImportDate = (select max(ImportDate) from @Sample)
GROUP BY Security
) AS b1
left JOIN (
SELECT Security,
Bprice,
MAX(BpriceSize) AS BpriceSize,
SourceCode
FROM @Sample
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)
--AND Bprice IS NOT NULL
--AND BpriceSize IS NOT NULL
AND ImportDate = (select max(ImportDate) from @Sample)
GROUP BY Security,
Bprice,
SourceCode
) AS b2 ON b2.Security = b1.Security AND b2.Bprice = b1.Bprice
), a2 (Security, Aprice, ApriceSize, Aprice_SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a1.ApriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + a2.SourceCode FROM a AS a2 WHERE a2.Security = a1.Security ORDER BY ', ' + a2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM a AS a1
), b2 (Security, Bprice, BpriceSize, Bprice_SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b1.BpriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + b2.SourceCode FROM b AS b2 WHERE b2.Security = b1.Security ORDER BY ', ' + b2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM b AS b1
)

SELECT bx.Security,
bx.Bprice,
bx.BpriceSize,
bx.Bprice_SourceCode,
ax.Aprice,
ax.ApriceSize,
ax.Aprice_SourceCode
FROM (
SELECT Security,
Bprice,
SUM(BpriceSize) AS BpriceSize,
Bprice_SourceCode
FROM b2
GROUP BY Security,
Bprice,
Bprice_SourceCode
) AS bx
left JOIN (
SELECT Security,
Aprice,
SUM(ApriceSize) AS ApriceSize,
Aprice_SourceCode
FROM a2
GROUP BY Security,
Aprice,
Aprice_SourceCode
) AS ax ON ax.Security = bx.Security
ORDER BY bx.Security

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 04:35:16

I see absolutely no reason for this line of code!
WHERE Security IN (select distinct Security_Name from tblPricesSourcesImport)

All records orginally come from this table anyway...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 04:36:05

Also this line of code is redundant since all records in @sample table already are the latest
AND ImportDate = (select max(ImportDate) from @Sample)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 04:41:39

[code]DECLARE @Stage TABLE
(
ID INT,
SourceID SMALLINT,
SourceCode VARCHAR(255),
[Security] VARCHAR(255),
Bprice DECIMAL(12, 8),
BpriceSize DECIMAL(12, 8),
Aprice VARCHAR(255),
ApriceSize DECIMAL(12, 8)
)

INSERT @Stage
SELECT Price_Source_Import_ID,
Source_ID,
Source_Code,
Security_Name,
CASE
WHEN LEN(Bid_Price) > 1 THEN REPLACE(Bid_Price, 'H', '')
WHEN dbo.IsReallyNumeric(Bid_Price) = 1 THEN Bid_Price
ELSE NULL
END AS Bid_Price,
CASE
WHEN dbo.IsReallyNumeric(Bid_Size) = 1 THEN Bid_Size
ELSE NULL
END AS Bid_Size,
CASE
WHEN dbo.IsReallyNumeric(Ask_Price) = 1 THEN Ask_Price
ELSE NULL
END AS Ask_Price,
CASE
WHEN LEN(Ask_Size) > 1 THEN REPLACE(Ask_Size, 'E', '')
WHEN dbo.IsReallyNumeric(Ask_Size) = 1 THEN Ask_Size
ELSE NULL
END AS Ask_Size
FROM tblPricesSourcesImport
WHERE Import_Date = (SELECT MAX(Import_Date) FROM tblPricesSourcesImport)

;WITH a ([Security], Aprice, ApriceSize, SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a2.ApriceSize,
a2.SourceCode
FROM (
SELECT [Security],
MIN(Aprice) AS Aprice
FROM @Stage
WHERE Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
GROUP BY [Security]
) AS a1
INNER JOIN (
SELECT [Security],
Aprice,
MAX(ApriceSize) AS ApriceSize,
SourceCode
FROM @Stage
WHERE Aprice IS NOT NULL
AND ApriceSize IS NOT NULL
GROUP BY [Security],
Aprice,
SourceCode
) AS a2 ON a2.Security = a1.Security AND a2.Aprice = a1.Aprice
), b ([Security], Bprice, BpriceSize, SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b2.BpriceSize,
b2.SourceCode
FROM (
SELECT [Security],
MAX(Bprice) AS Bprice
FROM @Stage
WHERE Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
GROUP BY [Security]
) AS b1
INNER JOIN (
SELECT [Security],
Bprice,
MAX(BpriceSize) AS BpriceSize,
SourceCode
FROM @Stage
WHERE Bprice IS NOT NULL
AND BpriceSize IS NOT NULL
GROUP BY [Security],
Bprice,
SourceCode
) AS b2 ON b2.Security = b1.Security AND b2.Bprice = b1.Bprice
), a2 ([Security], Aprice, ApriceSize, Aprice_SourceCode)
AS (
SELECT a1.Security,
a1.Aprice,
a1.ApriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + a2.SourceCode FROM a AS a2 WHERE a2.Security = a1.Security ORDER BY ', ' + a2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM a AS a1
), b2 ([Security], Bprice, BpriceSize, Bprice_SourceCode)
AS (
SELECT b1.Security,
b1.Bprice,
b1.BpriceSize,
STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + b2.SourceCode FROM b AS b2 WHERE b2.Security = b1.Security ORDER BY ', ' + b2.SourceCode FOR XML PATH('')), 1, 2, '')
FROM b AS b1
)

SELECT bx.Security,
bx.Bprice,
bx.BpriceSize,
bx.Bprice_SourceCode,
ax.Aprice,
ax.ApriceSize,
ax.Aprice_SourceCode
FROM (
SELECT [Security],
Bprice,
SUM(BpriceSize) AS BpriceSize,
Bprice_SourceCode
FROM b2
GROUP BY [Security],
Bprice,
Bprice_SourceCode
) AS bx
LEFT JOIN (
SELECT [Security],
Aprice,
SUM(ApriceSize) AS ApriceSize,
Aprice_SourceCode
FROM a2
GROUP BY [Security],
Aprice,
Aprice_SourceCode
) AS ax ON ax.Security = bx.Security
ORDER BY bx.Security[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources