sql 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)

sql query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-16 : 11:23:19

Below you see a query I run.

The result of this query is shown below...
The result shows fields such as Bid_Price_Best_Latest and Ask_Price_Best_Latest may or may not have null values.
At present, I use a cursor to get the values of these two fields if any of them is null from the table tblPricesSourcesImportHistory for the latest Import_Date on each record.
These two fields are referred to as Bid_Price, Ask_Price in tblPricesSourcesImportHistory.

Now I would like to find out how to do this without the use of a cursor, so that I can speed up this query.

Thanks

select
fp.Security_ID,
fp.Security_Name,
fp.Bid_Price_Best_Latest,
fp.Bid_Size_Best_Latest,
fp.Bid_Source,
fp.Ask_Price_Best_Latest,
fp.Ask_Size_Best_Latest,
fp.Ask_Source
from
tblTEST fp
left join tblSources s on fp.Security_ID = s.Security_ID
inner join tblPricesSourcesImportHistory h on
h.Source_ID = s.Source_ID
where
(fp.Bid_Price_Best_Latest is null) or (fp.Ask_Price_Best_Latest is null)
group by
fp.Security_ID,
fp.Security_Name,
fp.Bid_Price_Best_Latest,
fp.Bid_Size_Best_Latest,
fp.Bid_Source,
fp.Ask_Price_Best_Latest,
fp.Ask_Size_Best_Latest,
fp.Ask_Source
order by
fp.Security_ID

fp.Security_ID Security_Name Bid_Price_Best_Latest Bid_Size_Best_Lates Bid_Source Ask_Price_Best_Latest Ask_Size_Best_Latest Ask_Source
1 Alli 84.0000 0.50 G NULL NULL NULL
2 bow 82.5000 0.50 G NULL NULL NULL
4 xte NULL NULL NULL 90.0000 0.50 G
6 Wqu 84.5000 0.50 I NULL NULL NULL
...
...
...

Not sure if it helps but here are the structures of the tables you may need...

CREATE TABLE [dbo].[tblSources](
[Source_ID] [int] IDENTITY(1,1) NOT NULL,
[Security_ID] [smallint] NOT NULL,
[Source_Parent] [varchar](50) NOT NULL,
[Source_Code] [varchar](20) NOT NULL,
[Source_Security_Name] [varchar](50) NOT NULL)

CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-16 : 11:28:20

Please provide sample data and expected outout.
This is the 15th time you post a question related to this system of yours. Every time we ask you to provide sample data and expected output.

Or learn the NEW ROW_NUMBER function available in SQL Server 2005.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-16 : 11:32:42

SELECT Col1, Col2
FROM (
SELECT Col1, Col2, ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY Col4 DESC) AS RecID
FROM Table1 WHERE Col5 IS NULL
) AS d
WHERE RecID = 1

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-16 : 11:40:20

Ok, thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-16 : 11:44:58

quote:
Originally posted by Peso

Please provide sample data and expected outout.
This is the 15th time you post a question related to this system of yours. Every time we ask you to provide sample data and expected output.

Or learn the NEW ROW_NUMBER function available in SQL Server 2005.

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

Hi,
I believe I did provide information. See below:

This is what I get at present using the query.

fp.Security_ID Security_Name Bid_Price_Best_Latest Bid_Size_Best_Lates Bid_Source Ask_Price_Best_Latest Ask_Size_Best_Latest Ask_Source
1 Alli 84.0000 0.50 G NULL NULL NULL
2 bow 82.5000 0.50 G NULL NULL NULL
4 xte NULL NULL NULL 90.0000 0.50 G
6 Wqu 84.5000 0.50 I NULL NULL NULL
...
...
...

the null fields should be retrieved from table tblPricesSourcesImportHistory

Please let me know if you would like more information.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-17 : 09:04:11

quote:
Originally posted by Peso

SELECT Col1, Col2
FROM (
SELECT Col1, Col2, ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY Col4 DESC) AS RecID
FROM Table1 WHERE Col5 IS NULL
) AS d
WHERE RecID = 1

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

Hi,
Does this look ok?
It gives an error:
An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'.
Thanks

SELECT
Security_ID,
MAX(Bid_Price) AS Bid_Price,
MAX(Bid_Date) AS Bid_Date,
MAX(Ask_Price) AS Ask_Price,
MAX(Ask_Date) AS Price_Quote_Date
FROM
(
SELECT
sec.Security_ID,
h.Bid_Price,
h.Import_Date AS Bid_Date,
NULL AS Ask_Price,
NULL AS Ask_Date,
ROW_NUMBER() OVER (PARTITION BY sec.Security_ID ORDER BY h.Import_Date DESC) AS RecID
FROM
tblPricesSourcesImportHistory h
left join tblSources s on h.Source_ID = s.Source_ID
right join tblSecurities sec on s.Security_ID
WHERE
len(rtrim(ltrim(h.Bid_Price))) > 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 09:35:56

No, it does not look ok.

select		d.Security_ID,
		d.Security_Name, 
		d.Bid_Price_Best_Latest,
		d.Bid_Size_Best_Latest,
		d.Bid_Source,
		d.Ask_Price_Best_Latest,
		d.Ask_Size_Best_Latest,
		d.Ask_Source
from		(
			select		fp.Security_ID,
					fp.Security_Name, 
					fp.Bid_Price_Best_Latest,
					fp.Bid_Size_Best_Latest,
					fp.Bid_Source,
					fp.Ask_Price_Best_Latest,
					fp.Ask_Size_Best_Latest,
					fp.Ask_Source,
					ROW_NUMBER() OVER (PARTITION BY fp.Security_ID ORDER BY h.Import_Date DESC) AS RecID
			from		tblPricesSourcesImportHistory as h
			inner join	tblSources as s on s.Source_ID = h.Source_ID
			right join	tblTEST as fp on fp.Security_ID = s.Security_ID
			where		fp.Bid_Price_Best_Latest is null
					or fp.Ask_Price_Best_Latest is null
		) AS d
where		d.recid = 1
order by	d.Security_ID

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-17 : 09:45:53

These two queries return the same records

select * from tblTEST
--------------

select
d.VTB_Security_ID,
d.Security_Name,
d.Bid_Price_Best_Latest,
d.Bid_Size_Best_Latest,
d.Bid_Source,
d.Ask_Price_Best_Latest,
d.Ask_Size_Best_Latest,
d.Ask_Source
from (
select
fp.VTB_Security_ID,
fp.Security_Name,
fp.Bid_Price_Best_Latest,
fp.Bid_Size_Best_Latest,
fp.Bid_Source,
fp.Ask_Price_Best_Latest,
fp.Ask_Size_Best_Latest,
fp.Ask_Source,
ROW_NUMBER() OVER (PARTITION BY fp.VTB_Security_ID ORDER BY h.Import_Date DESC) AS RecID
from
tblPricesSourcesImportHistory as h
inner join tblSources as s on s.Source_ID = h.Source_ID
right join tblTEST as fp on fp.VTB_Security_ID = s.VTB_Security_ID
where fp.Bid_Price_Best_Latest is null
or fp.Ask_Price_Best_Latest is null
) AS d
where d.recid = 1
order by d.VTB_Security_ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 10:08:47

Arkiboys, if you REALLY want some further help PLEASE post sample data TOGETHER with expected result.
How to post sample data?

Please READ and FOLLOW the steps here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-17 : 10:59:40

Ok, I hope the following information is ok.

craete table tblTEST
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Source varchar(1000),
Ask_Price_Best_Latest decimal(12, 4),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Source varchar(1000)
)

Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source

34 ALFARU 7 7/8 10/09 98.5000 0.50 T NULL NULL NULL
62 GAZPRU 5.44 11/17 NULL NULL NULL 88.0000 1.00 T
...
...
...

insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source
)
select 34,'ALFARU 7 7/8 10/09',98.5000,0.50,T,NULL,NULL,NULL union all
select 62,'GAZPRU 5.44 11/17',NULL,NULL,NULL,88.0000,1.00 T
...
...
...

CREATE TABLE [dbo].[tblSources](
[Source_ID] [int] IDENTITY(1,1) NOT NULL,
[Security_ID] [smallint] NOT NULL,
[Source_Parent] [varchar](50) NOT NULL,
[Source_Code] [varchar](20) NOT NULL,
[Source_Security_Name] [varchar](50) NOT NULL)

insert into tblSources(Source_ID, Security_ID, Source_Parent, Source_Code, Source_Security_Name)
select 1,62,'4GCW','GAZ 5.44 17E' union all
select 33,62,EUROVCSC11,GAZPRM 17EN' union all
select 49,62,GFI01,GAZ 5.44 N17' union all
select 72,34,NFBE,ALFA09U' union all
select 94,34,EUROVCSC11,ALFA 09 $' union all
select 150,62,NFBE,GAZ17EN' union all
select 254,62,TR73,GAZ 5.44 17E' union all
select 322,34,TR74,ALFA 7.875 09' union all
select 355,62,TR74,GAZ 5.44 17E' union all
select 760,34,TTFN01,ALFA 09$' union all
select 768,62,TTFN01,GAZPRM17N E'

CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)

insert into tblPricesSourcesImportHistory(Price_Source_Import_History_ID,Source_ID,Source_Parent,Source_Code,Security_Name,Source_Security_Name,
Bid_Price,Bid_Size,Ask_Price,Ask_Size,Import_Date)
590729 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:43:00
590749 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:44:00
590771 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:45:00
590793 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:46:00
590816 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:47:00
590839 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:48:00
...
...
...

As you can see, some of the fields in tblTEST have null values.
My cursor loops through each Security_ID and finds the records :

from tblTEST
where
Bid_Price_Best_Latest is null or Ask_Price_Best_Latest is null

Then
these fields along with other null fields for that security_ID is retrieved from tblPricesSourcesImportHistory where Price_Quote_Date is max for that security_ID

Sample data in tblPricesSourcesImportHistory (This is a history table which gets added to every minute) :

1237948 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:29:00
1237970 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:29:00
1238086 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:30:00
1241254 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:46:00
1241359 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:46:00
1241382 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:46:00
1241499 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:48:00
1241604 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:48:00
1241627 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:48:00
1241744 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:49:00
1241849 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:49:00
1241872 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:49:00
1241989 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:50:00
...
...
...
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 11:27:54

quote:
Originally posted by arkiboys

Ok, I hope the following information is ok.

craete table tblTEST
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Source varchar(1000),
Ask_Price_Best_Latest decimal(12, 4),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Source varchar(1000)
)

Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source

34 ALFARU 7 7/8 10/09 98.5000 0.50 T NULL NULL NULL
62 GAZPRU 5.44 11/17 NULL NULL NULL 88.0000 1.00 T
...
...
...

insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source
)
select 34,'ALFARU 7 7/8 10/09',98.5000,0.50,T,NULL,NULL,NULL union all
select 62,'GAZPRU 5.44 11/17',NULL,NULL,NULL,88.0000,1.00 T
...
...
...

CREATE TABLE [dbo].[tblSources](
[Source_ID] [int] IDENTITY(1,1) NOT NULL,
[Security_ID] [smallint] NOT NULL,
[Source_Parent] [varchar](50) NOT NULL,
[Source_Code] [varchar](20) NOT NULL,
[Source_Security_Name] [varchar](50) NOT NULL)

insert into tblSources(Source_ID, Security_ID, Source_Parent, Source_Code, Source_Security_Name)
select 1,62,'4GCW','GAZ 5.44 17E' union all
select 33,62,EUROVCSC11,GAZPRM 17EN' union all
select 49,62,GFI01,GAZ 5.44 N17' union all
select 72,34,NFBE,ALFA09U' union all
select 94,34,EUROVCSC11,ALFA 09 $' union all
select 150,62,NFBE,GAZ17EN' union all
select 254,62,TR73,GAZ 5.44 17E' union all
select 322,34,TR74,ALFA 7.875 09' union all
select 355,62,TR74,GAZ 5.44 17E' union all
select 760,34,TTFN01,ALFA 09$' union all
select 768,62,TTFN01,GAZPRM17N E'

CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)

insert into tblPricesSourcesImportHistory(Price_Source_Import_History_ID,Source_ID,Source_Parent,Source_Code,Security_Name,Source_Security_Name,
Bid_Price,Bid_Size,Ask_Price,Ask_Size,Import_Date)
590729 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:43:00
590749 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:44:00
590771 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:45:00
590793 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:46:00
590816 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:47:00
590839 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:48:00
...
...
...

As you can see, some of the fields in tblTEST have null values.
My cursor loops through each Security_ID and finds the records :

from tblTEST
where
Bid_Price_Best_Latest is null or Ask_Price_Best_Latest is null

Then
these fields along with other null fields for that security_ID is retrieved from tblPricesSourcesImportHistory where Price_Quote_Date is max for that security_ID

Sample data in tblPricesSourcesImportHistory (This is a history table which gets added to every minute) :

1237948 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:29:00
1237970 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:29:00
1238086 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:30:00
1241254 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:46:00
1241359 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:46:00
1241382 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:46:00
1241499 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:48:00
1241604 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:48:00
1241627 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:48:00
1241744 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:49:00
1241849 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:49:00
1241872 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:49:00
1241989 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:50:00
...
...
...
...
...

this will do the job for u

UPDATE t
SET 
t.Bid_Price_Best_Latest=ISNULL(t.Bid_Price_Best_Latest,psih.Bid_Price), t.Bid_Size_Best_Lates=ISNULL(t.Bid_Size_Best_Lates,psih.Bid_Size),
t.Bid_Source=ISNULL(t.Bid_Source,xxxx), 
t.Ask_Price_Best_Latest=ISNULL(t.Ask_Price_Best_Latest,psih.Ask_Price), 
t.Ask_Size_Best_Latest=ISNULL(t.Ask_Size_Best_Latest,psih.Ask_Size), 
t.Ask_Source=ISNULL(t.Ask_Source,xxxx)
FROM tblTEST t
INNER JOIN tblSources src
ON src.Security_ID=t.Security_ID
INNER JOIN tblPricesSourcesImportHistory psih
ON psih.Source_ID=src.Source_ID
INNER JOIN (SELECT Source_ID,MAX(Import_Date) AS MaxDate 
FROM tblPricesSourcesImportHistory
GROUP BY Source_ID)tmp
ON tmp.Source_ID=psih.Source_ID
AND tmp.MaxDate =psih.Import_Date
WHERE t.Bid_Price_Best_Latest is null 
or t.Ask_Price_Best_Latest is null

this stub will do the job. Plzz make sure you substitute xxx placeholders with corresponding fields( i cant find out suitable fields to get the values for them)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 11:39:05

[code]-- Prepare sample data
DECLARE @tblTest TABLE
(
Security_ID SMALLINT,
Security_Name VARCHAR(50),
Bid_Price_Best_Latest DECIMAL(12, 4),
Bid_Size_Best_Latest DECIMAL(12, 2),
Bid_Source VARCHAR(1000),
Ask_Price_Best_Latest DECIMAL(12, 4),
Ask_Size_Best_Latest DECIMAL(12, 2),
Ask_Source VARCHAR(1000)
)

INSERT @tblTest
SELECT 34, 'ALFARU 7 7/8 10/09', 98.5, 0.5, 'T', NULL, NULL, NULL UNION ALL
SELECT 62, 'GAZPRU 5.44 11/17', NULL, NULL, NULL, 88.0, 1.0, 'T'

DECLARE @tblSources TABLE
(
Source_ID INT,
Security_ID SMALLINT,
Source_Parent VARCHAR(50),
Source_Code VARCHAR(20),
Source_Security_Name VARCHAR(50)
)

INSERT @tblSources
SELECT 1, 62, '4GCW', 'GAZ 5.44', '17E' UNION ALL
SELECT 33, 62, 'EUROVCSC11', 'GAZPRM', '17EN' UNION ALL
SELECT 49, 62, 'GFI01', 'GAZ 5.44', 'N17' UNION ALL
SELECT 72, 34, 'NFBE', 'ALFA09U', NULL UNION ALL
SELECT 94, 34, 'EUROVCSC11', 'ALFA 09 $', NULL UNION ALL
SELECT 150, 62, 'NFBE', 'GAZ17EN', NULL UNION ALL
SELECT 254, 62, 'TR73', 'GAZ 5.44', '17E' UNION ALL
SELECT 322, 34, 'TR74', 'ALFA 7.875', '09' UNION ALL
SELECT 355, 62, 'TR74', 'GAZ 5.44', '17E' UNION ALL
SELECT 760, 34, 'TTFN01', 'ALFA 09$', NULL UNION ALL
SELECT 768, 62, 'TTFN01', 'GAZPRM17N', 'E'

DECLARE @tblPricesSourcesImportHistory TABLE
(
Price_Source_Import_History_ID INT IDENTITY(1,1),
Source_ID SMALLINT,
Source_Parent VARCHAR(255),
Source_Code VARCHAR(255),
Security_Name VARCHAR(255),
Source_Security_Name VARCHAR(255),
Bid_Price VARCHAR(255),
Bid_Size VARCHAR(255),
Ask_Price VARCHAR(255),
Ask_Size VARCHAR(255),
Import_Date SMALLDATETIME
)

INSERT @tblPricesSourcesImportHistory
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:43:00' UNION ALL
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:44:00' UNION ALL
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:45:00' UNION ALL
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:46:00' UNION ALL
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:47:00' UNION ALL
SELECT 49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:48:00'

-- Is this the expected output?
SELECT t.Security_ID,
t.Security_Name,
MAX(COALESCE(t.Bid_Price_Best_Latest, psih.Bid_Price)) AS Bid_Price_Best_Latest,
MAX(COALESCE(t.Bid_Size_Best_Latest, psih.Bid_Size)) AS Bid_Size_Best_Latest,
MAX(CASE
WHEN t.Bid_Source IS NULL THEN 'PSIH'
ELSE 'T'
END) AS Bid_Source,
MAX(COALESCE(t.Ask_Price_Best_Latest, psih.Ask_Price)) AS Ask_Price_Best_Latest,
MAX(COALESCE(t.Ask_Size_Best_Latest, psih.ASk_Size)) AS Ask_Size_Best_Latest,
MAX(CASE
WHEN t.Ask_Source IS NULL THEN 'PSIH'
ELSE 'T'
END) AS Ask_Source
FROM @tblTest AS t
INNER JOIN @tblSources AS s ON s.Security_ID = t.Security_ID
LEFT JOIN (
SELECT Source_ID,
CONVERT(DECIMAL(12, 4), Bid_Price) AS Bid_Price,
CONVERT(DECIMAL(12, 2), Bid_Size) AS Bid_Size,
CONVERT(DECIMAL(12, 4), Ask_Price) AS Ask_Price,
CONVERT(DECIMAL(12, 2), Ask_Size) AS Ask_Size,
ROW_NUMBER() OVER (PARTITION BY Source_ID ORDER BY Import_Date DESC) AS RecID
FROM @tblPricesSourcesImportHistory
) AS psih ON psih.Source_ID = s.Source_ID
AND psih.RecID = 1
GROUP BY t.Security_ID,
t.Security_Name
ORDER BY t.Security_ID,
t.Security_Name[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-17 : 12:15:55

I will run some tests.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-17 : 12:46:00

quote:
Originally posted by visakh16

quote:
Originally posted by arkiboys

Ok, I hope the following information is ok.

craete table tblTEST
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Source varchar(1000),
Ask_Price_Best_Latest decimal(12, 4),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Source varchar(1000)
)

Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source

34 ALFARU 7 7/8 10/09 98.5000 0.50 T NULL NULL NULL
62 GAZPRU 5.44 11/17 NULL NULL NULL 88.0000 1.00 T
...
...
...

insert into tblTEST(Security_ID, Security_Name, Bid_Price_Best_Latest, Bid_Size_Best_Lates, Bid_Source, Ask_Price_Best_Latest, Ask_Size_Best_Latest, Ask_Source
)
select 34,'ALFARU 7 7/8 10/09',98.5000,0.50,T,NULL,NULL,NULL union all
select 62,'GAZPRU 5.44 11/17',NULL,NULL,NULL,88.0000,1.00 T
...
...
...

CREATE TABLE [dbo].[tblSources](
[Source_ID] [int] IDENTITY(1,1) NOT NULL,
[Security_ID] [smallint] NOT NULL,
[Source_Parent] [varchar](50) NOT NULL,
[Source_Code] [varchar](20) NOT NULL,
[Source_Security_Name] [varchar](50) NOT NULL)

insert into tblSources(Source_ID, Security_ID, Source_Parent, Source_Code, Source_Security_Name)
select 1,62,'4GCW','GAZ 5.44 17E' union all
select 33,62,EUROVCSC11,GAZPRM 17EN' union all
select 49,62,GFI01,GAZ 5.44 N17' union all
select 72,34,NFBE,ALFA09U' union all
select 94,34,EUROVCSC11,ALFA 09 $' union all
select 150,62,NFBE,GAZ17EN' union all
select 254,62,TR73,GAZ 5.44 17E' union all
select 322,34,TR74,ALFA 7.875 09' union all
select 355,62,TR74,GAZ 5.44 17E' union all
select 760,34,TTFN01,ALFA 09$' union all
select 768,62,TTFN01,GAZPRM17N E'

CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)

insert into tblPricesSourcesImportHistory(Price_Source_Import_History_ID,Source_ID,Source_Parent,Source_Code,Security_Name,Source_Security_Name,
Bid_Price,Bid_Size,Ask_Price,Ask_Size,Import_Date)
590729 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:43:00
590749 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:44:00
590771 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:45:00
590793 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:46:00
590816 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:47:00
590839 49 GFI01 GAZ 5.44 N17 GAZ_5.44_N17 87.875 2 88.875 2 2008-01-07 08:48:00
...
...
...

As you can see, some of the fields in tblTEST have null values.
My cursor loops through each Security_ID and finds the records :

from tblTEST
where
Bid_Price_Best_Latest is null or Ask_Price_Best_Latest is null

Then
these fields along with other null fields for that security_ID is retrieved from tblPricesSourcesImportHistory where Price_Quote_Date is max for that security_ID

Sample data in tblPricesSourcesImportHistory (This is a history table which gets added to every minute) :

1237948 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:29:00
1237970 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:29:00
1238086 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:30:00
1241254 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:46:00
1241359 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:46:00
1241382 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:46:00
1241499 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:48:00
1241604 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:48:00
1241627 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:48:00
1241744 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:49:00
1241849 254 TR73 GAZ 5.44 17E GAZ___5.44_17E 86.75 1 87.75 1 2008-01-16 14:49:00
1241872 322 TR74 ALFA 7.875 09 ALFA_7.875_09 98.50 0.5 2008-01-16 14:49:00
1241989 94 EUROVCSC11 ALFA 09 $ ALFA_09_$ 98.25 0.5 99.00 0.5 2008-01-16 14:50:00
...
...
...
...
...

this will do the job for u
UPDATE t
SET
t.Bid_Price_Best_Latest=ISNULL(t.Bid_Price_Best_Latest,psih.Bid_Price), t.Bid_Size_Best_Lates=ISNULL(t.Bid_Size_Best_Lates,psih.Bid_Size),
t.Bid_Source=ISNULL(t.Bid_Source,xxxx),
t.Ask_Price_Best_Latest=ISNULL(t.Ask_Price_Best_Latest,psih.Ask_Price),
t.Ask_Size_Best_Latest=ISNULL(t.Ask_Size_Best_Latest,psih.Ask_Size),
t.Ask_Source=ISNULL(t.Ask_Source,xxxx)
FROM tblTEST t
INNER JOIN tblSources src
ON src.Security_ID=t.Security_ID
INNER JOIN tblPricesSourcesImportHistory psih
ON psih.Source_ID=src.Source_ID
INNER JOIN (SELECT Source_ID,MAX(Import_Date) AS MaxDate
FROM tblPricesSourcesImportHistory
GROUP BY Source_ID)tmp
ON tmp.Source_ID=psih.Source_ID
AND tmp.MaxDate =psih.Import_Date
WHERE t.Bid_Price_Best_Latest is null
or t.Ask_Price_Best_Latest is null

this stub will do the job. Plzz make sure you substitute xxx placeholders with corresponding fields( i cant find out suitable fields to get the values for them)

Hi,
I am in the process of carrying out alot of tests and alot of learning at the same time from the threads I have received.

I am having difficulties adding the following case statement into your update query.
Here is the case statements

Bid_Price_Best_Latest =
case
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Price)))
end,

Bid_Size_Best_Lates =
case
when charindex('A', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Size)))
end,

Ask_Price_Best_Latest =
case
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Price)))
end,

Ask_Size_Best_Latest =
case
when charindex('A', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Size)))
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 12:53:09

place case like this:-

UPDATE t
SET
t.Bid_Price_Best_Latest=ISNULL(t.Bid_Price_Best_Latest,case
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Price)))
end), ...

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-18 : 05:28:12

Hi,
I ran several tests.
It seems not all Bid_Price_Best_Latest fields get populated. Some do not and it seems the ones that do get populated are correct but the their Source field is empty.
Thanks
This is the sql:

UPDATE t
SET
t.Bid_Price_Best_Latest=ISNULL(t.Bid_Price_Best_Latest,case
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Price)))
end),
t.Bid_Size_Best_Latest=ISNULL(t.Bid_Size_Best_Latest,case
when charindex('A', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Size)))
end),
t.Bid_Source=ISNULL(t.Bid_Source,''),
t.Ask_Price_Best_Latest=ISNULL(t.Ask_Price_Best_Latest,case
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Price)))
end),
t.Ask_Size_Best_Latest=ISNULL(t.Ask_Size_Best_Latest,case
when charindex('A', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Size)))
end),
t.Ask_Source=ISNULL(t.Ask_Source,'')
FROM tblTEST t
INNER JOIN tblSources src
ON src.Security_ID=t.Security_ID
INNER JOIN tblPricesSourcesImportHistory psih
ON psih.Source_ID=src.Source_ID
INNER JOIN (SELECT Source_ID,MAX(Import_Date) AS MaxDate
FROM tblPricesSourcesImportHistory
GROUP BY Source_ID)tmp
ON tmp.Source_ID=psih.Source_ID
AND tmp.MaxDate =psih.Import_Date
WHERE t.Bid_Price_Best_Latest is null
or t.Ask_Price_Best_Latest is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 05:43:42

quote:
Originally posted by arkiboys

Hi,
I ran several tests.
It seems not all Bid_Price_Best_Latest fields get populated. Some do not and it seems the ones that do get populated are correct but the their Source field is empty.
Thanks
This is the sql:

UPDATE t
SET
t.Bid_Price_Best_Latest=ISNULL(t.Bid_Price_Best_Latest,case
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Price)))
end),
t.Bid_Size_Best_Latest=ISNULL(t.Bid_Size_Best_Latest,case
when charindex('A', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Bid_Size))) > 0 then replace(psih.Bid_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Bid_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Bid_Size)))
end),
t.Bid_Source=ISNULL(t.Bid_Source,''),
t.Ask_Price_Best_Latest=ISNULL(t.Ask_Price_Best_Latest,case
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Price)))
end),
t.Ask_Size_Best_Latest=ISNULL(t.Ask_Size_Best_Latest,case
when charindex('A', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'A', '')
when charindex('E', ltrim(rtrim(psih.Ask_Size))) > 0 then replace(psih.Ask_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(psih.Ask_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(psih.Ask_Size)))
end),
t.Ask_Source=ISNULL(t.Ask_Source,'')
FROM tblTEST t
INNER JOIN tblSources src
ON src.Security_ID=t.Security_ID
INNER JOIN tblPricesSourcesImportHistory psih
ON psih.Source_ID=src.Source_ID
INNER JOIN (SELECT Source_ID,MAX(Import_Date) AS MaxDate
FROM tblPricesSourcesImportHistory
GROUP BY Source_ID)tmp
ON tmp.Source_ID=psih.Source_ID
AND tmp.MaxDate =psih.Import_Date
WHERE t.Bid_Price_Best_Latest is null
or t.Ask_Price_Best_Latest is null

why r u putting blanks for source fields? i had asked earlier to put name fields which you think must provide you with source values their. i had put xxxx as i couldnt trace out respective fields.
re. Bid_Price_Best_latest one of reasons could be problems with case statement inside. can you make sure case construct is as per your reqmnt?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-18 : 05:47:46

I would like to show just empty source if the value is null. I guess what I have done for the source is ok. Now Do you think so?
I have been through the case statements but this is the same as the one in my cursor that I am trying to replace with this query.
So not sure what to change to get the missing field values.
It seems it is getting there...
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 05:50:45

Can there be records with null values for import dates in tblPricesSourcesImportHistory table? Check using query
SELECT COUNT(Customer_ID) FROM tblPricesSourcesImportHistory WHERE Import_Date IS NULL

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-18 : 07:27:31

quote:
Originally posted by visakh16

Can there be records with null values for import dates in tblPricesSourcesImportHistory table? Check using query
SELECT COUNT(Customer_ID) FROM tblPricesSourcesImportHistory WHERE Import_Date IS NULL

This query returns 0
select count(*) from tblPricesSourcesImportHistory where Import_Date is null

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-18 : 08:48:13

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE	@tblTest TABLE
	(
		Security_ID SMALLINT,
		Security_Name VARCHAR(50),
		Bid_Price_Best_Latest DECIMAL(12, 4),
		Bid_Size_Best_Latest DECIMAL(12, 2),
		Bid_Source VARCHAR(1000),
		Ask_Price_Best_Latest DECIMAL(12, 4),
		Ask_Size_Best_Latest DECIMAL(12, 2),
		Ask_Source VARCHAR(1000)
	)

INSERT	@tblTest
SELECT	34, 'ALFARU 7 7/8 10/09', 98.5, 0.5, 'T', NULL, NULL, NULL UNION ALL
SELECT	62, 'GAZPRU 5.44 11/17', NULL, NULL, NULL, 88.0, 1.0, 'T'

DECLARE	@tblSources TABLE
	(
		Source_ID INT,
		Security_ID SMALLINT,
		Source_Parent VARCHAR(50),
		Source_Code VARCHAR(20),
		Source_Security_Name VARCHAR(50)
	)

INSERT	@tblSources
SELECT	1, 62, '4GCW', 'GAZ 5.44', '17E' UNION ALL
SELECT	33, 62, 'EUROVCSC11', 'GAZPRM', '17EN' UNION ALL
SELECT	49, 62, 'GFI01', 'GAZ 5.44', 'N17' UNION ALL
SELECT	72, 34, 'NFBE', 'ALFA09U', NULL UNION ALL
SELECT	94, 34, 'EUROVCSC11', 'ALFA 09 $', NULL UNION ALL
SELECT	150, 62, 'NFBE', 'GAZ17EN', NULL UNION ALL
SELECT	254, 62, 'TR73', 'GAZ 5.44', '17E' UNION ALL
SELECT	322, 34, 'TR74', 'ALFA 7.875', '09' UNION ALL
SELECT	355, 62, 'TR74', 'GAZ 5.44', '17E' UNION ALL
SELECT	760, 34, 'TTFN01', 'ALFA 09$', NULL UNION ALL
SELECT	768, 62, 'TTFN01', 'GAZPRM17N', 'E'

DECLARE	@tblPricesSourcesImportHistory TABLE
	(
		Price_Source_Import_History_ID INT IDENTITY(1,1),
		Source_ID SMALLINT,
		Source_Parent VARCHAR(255),
		Source_Code VARCHAR(255),
		Security_Name VARCHAR(255),
		Source_Security_Name VARCHAR(255),
		Bid_Price VARCHAR(255),
		Bid_Size VARCHAR(255),
		Ask_Price VARCHAR(255),
		Ask_Size VARCHAR(255),
		Import_Date SMALLDATETIME
	)

INSERT	@tblPricesSourcesImportHistory
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:43:00' UNION ALL
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:44:00' UNION ALL
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:45:00' UNION ALL
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:46:00' UNION ALL
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:47:00' UNION ALL
SELECT	49, 'GFI01', 'GAZ 5.44', 'N17', 'GAZ_5.44_N17', 87.875, 2, 88.875, 2, '2008-01-07 08:48:00'

-- Is this the expected output?
SELECT		t.Security_ID,
		t.Security_Name,
		MAX(COALESCE(t.Bid_Price_Best_Latest, psih.Bid_Price)) AS Bid_Price_Best_Latest,
		MAX(COALESCE(t.Bid_Size_Best_Latest, psih.Bid_Size)) AS Bid_Size_Best_Latest,
		MAX(CASE
			WHEN t.Bid_Source IS NULL THEN 'PSIH'
			ELSE 'T'
		END) AS Bid_Source,
		MAX(COALESCE(t.Ask_Price_Best_Latest, psih.Ask_Price)) AS Ask_Price_Best_Latest,
		MAX(COALESCE(t.Ask_Size_Best_Latest, psih.ASk_Size)) AS Ask_Size_Best_Latest,
		MAX(CASE
			WHEN t.Ask_Source IS NULL THEN 'PSIH'
			ELSE 'T'
		END) AS Ask_Source
FROM		@tblTest AS t
INNER JOIN	@tblSources AS s ON s.Security_ID = t.Security_ID
LEFT JOIN	(
			SELECT	Source_ID,
				CONVERT(DECIMAL(12, 4), Bid_Price) AS Bid_Price,
				CONVERT(DECIMAL(12, 2), Bid_Size) AS Bid_Size,
				CONVERT(DECIMAL(12, 4), Ask_Price) AS Ask_Price,
				CONVERT(DECIMAL(12, 2), Ask_Size) AS Ask_Size,
				ROW_NUMBER() OVER (PARTITION BY Source_ID ORDER BY Import_Date DESC) AS RecID
			FROM	@tblPricesSourcesImportHistory
		) AS psih ON psih.Source_ID = s.Source_ID
			AND psih.RecID = 1
GROUP BY	t.Security_ID,
		t.Security_Name
ORDER BY	t.Security_ID,
		t.Security_Name

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

Hi,
I see what is wrong here.
Lets say today is the 18th of Jan.
The results are wrong for the records which only have values before today.
i.e if a Security_ID has a Bid_Price for today but and Ask_Price for a day before today, then the query returns the correct Bid_Price but returns null or incorrect value for Ask_Price

thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources