May 12, 2010 at 8:21 am
I usually write the following, but I am now inputting data from 2 .net scripts,
I use the identity specification(TickID) as well so the rows will look like the following table
So I end up with a null field, I cant make the barstamp anylower in timeframe as I wont
be able to call back properly later
I need two scripts
1) to be able to select the last not null field
2) to select a range of time and then the last not null field within that.
I know how to make the between statement, but not the select last not null field
many thanks
Neil
BarStamp TickID AskSideRWsize BidSideRWsize
5/12/2010 3:16:00 PM23112NULL155
5/12/2010 3:16:00 PM23113223NULL
5/12/2010 3:16:00 PM23114NULL166
5/12/2010 3:16:00 PM23115220NULL
5/12/2010 3:16:00 PM23116NULL165
5/12/2010 3:16:00 PM23117221NULL
5/12/2010 3:16:00 PM23118NULL163
5/12/2010 3:16:00 PM23119238NULL
5/12/2010 3:16:00 PM23120NULL162
5/12/2010 3:17:00 PM23121249NULL
5/12/2010 3:17:00 PM23122NULL161
5/12/2010 3:17:00 PM23123253NULL
5/12/2010 3:17:00 PM23124NULL161
5/12/2010 3:17:00 PM23125253NULL
5/12/2010 3:17:00 PM23126NULL162
5/12/2010 3:17:00 PM23127253NULL
5/12/2010 3:17:00 PM23128NULL162
5/12/2010 3:17:00 PM23129255NULL
SELECT Max(AskSideRWsize),Max(BidSideRWsize)
FROM
FullOrderBookES
WITH (NOLOCK)
WHERE BarStamp =(SELECT MAX(BarStamp)FROM FullOrderBookES)";
May 12, 2010 at 8:43 am
What you mean by "the last not null field"?
1. Do you want to select AskSideRWsize if the BidSideRWsize is null?
use ISNULL(BidSideRWsize, AskSideRWsize)
2. If both values (AskSideRWsize & BidSideRWsize) aere null, do you want TickID returned?
BidSideRWsize
BarStamp TickID AskSideRWsize BidSideRWsize
Do you want to make you DateStamp column shorter (as a string)?
It is possible to make it just 11 characters long without loosing any date/time part...
May 12, 2010 at 8:55 am
What you mean by "the last not null field"?
1. Do you want to select AskSideRWsize if the BidSideRWsize is null?
use ISNULL(BidSideRWsize, AskSideRWsize)
> I want to select only when there is data in the field
2. If both values (AskSideRWsize & BidSideRWsize) aere null, do you want TickID returned?
BidSideRWsize
BarStamp TickID AskSideRWsize BidSideRWsize
> one of the values will always be null, one script is for real-time access, therefore i only need the last record that contains data in it.
the other query is historical so I can select a range of time, then find the last field with data it in
Do you want to make you DateStamp column shorter (as a string)?
It is possible to make it just 11 characters long without loosing any date/time part...
> for now I am ok with the datestamp
I hope this explains it better
May 12, 2010 at 9:13 am
Friend, here is something of gift for you! From what i understood, this would help you
First the tables and sample data: (Please look how i post data so that it will be so helpful for people who try to help you)
IF OBJECT_ID('TEMPDB..#TABLE') IS NOT NULL
DROP TABLE #TABLE
CREATE TABLE #TABLE
(
BarStamp DATETIME,
TickID INT ,
AskSideRWsize INT,
BidSideRWsize INT
)
INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23112, NULL ,155
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23113 ,223 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23114 ,NULL, 166
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23115 ,220, NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23116 ,NULL, 165
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23117 ,221 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23118 ,NULL, 163
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23119 ,238 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:16:00 PM', 23120 ,NULL, 162
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23121 ,249 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23122 ,NULL, 161
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23123 ,253 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23124 ,NULL, 161
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23125 ,253 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23126 ,NULL ,162
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23127 ,253 ,NULL
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23128 ,NULL ,162
) INSERT INTO #TABLE(BarStamp ,TickID ,AskSideRWsize, BidSideRWsize) VALUES (
'5/12/2010 3:17:00 PM', 23129 ,255 ,NULL)
now, select the data and view the data:
SELECT
BarStamp ,TickID ,AskSideRWsize, BidSideRWsize ,
ROW_NUMBER() OVER(ORDER BY TickID) RN
FROM #TABLE
Now the real code; this will fetch the TickID of last occurrence of AskSideRWsize and BidSideRWsize where they are null:
;WITH CTE AS
(
SELECT
BarStamp ,TickID ,AskSideRWsize, BidSideRWsize ,
ROW_NUMBER() OVER(ORDER BY TickID) RN
FROM #TABLE
--WHERE BarStamp BETWEEN @STARTDATE AND @ENDATE
),
LAST_NLL_AskSideRWsize AS
(
SELECT MAX(TickID) TickID , 1 NUM
FROM CTE WHERE AskSideRWsize IS NULL
),
LAST_NLL_BidSideRWsize AS
(
SELECT MAX(TickID) TickID , 1 NUM
FROM CTE WHERE BidSideRWsize IS NULL
)
SELECT
A.TickID LAST_NLL_AskSideRWsize,
B.TickID LAST_NLL_BidSideRWsize
FROM LAST_NLL_AskSideRWsize A CROSS JOIN LAST_NLL_BidSideRWsize B
If you want to view the last occurrence of data,meaning the row value before the last NULL in both the columns, use this following script:
;WITH CTE AS
(
SELECT
BarStamp ,TickID ,AskSideRWsize, BidSideRWsize
FROM #TABLE
--WHERE BarStamp BETWEEN @STARTDATE AND @ENDATE
),
Last_NonNULL_AskSideRWsize AS
(
SELECT MAX(TickID) TickID , 1 NUM
FROM CTE WHERE AskSideRWsize IS NOT NULL
),
Last_NonNULL_BidSideRWsize AS
(
SELECT MAX(TickID) TickID , 1 NUM
FROM CTE WHERE BidSideRWsize IS NOT NULL
)
SELECT
A.TickID Last_NonNULL_AskSideRWsize,
B.TickID Last_NonNULL_BidSideRWsize
FROM Last_NonNULL_AskSideRWsize A CROSS JOIN Last_NonNULL_BidSideRWsize B
Now if you want to filter the data using some date range, please uncomment the commented line in the above code and supply the start and end date!
Please tell us back here if this worked for you!
Hope this helps you, Cheers!
~Edit: Added further more details and code!
May 12, 2010 at 9:17 am
Sorry, but your anwers made it even more obscure:
What you mean by "the last not null field"?
1. Do you want to select AskSideRWsize if the BidSideRWsize is null?
use ISNULL(BidSideRWsize, AskSideRWsize)
> I want to select only when there is data in the field
>> Select what? Select records from table where the value in one of the field is not null?
Or select the records from table where value in the specified field is not null?
Or select some thing else.
2. If both values (AskSideRWsize & BidSideRWsize) aere null, do you want TickID returned?
BidSideRWsize
BarStamp TickID AskSideRWsize BidSideRWsize
> one of the values will always be null, one script is for real-time access, therefore i only need the last record that contains data in it.
>> Look, if there is a record then, I can assure, you there is always some kind of data in it (nulls are data as well), otherwise there is no record at all!
Do you whant to select the last record (eg. the record with the latest BarStamp) where one of the AskSideRWsize or BidSideRWsize is not null?
Or do you want to slelect exactly two rows: one with the latest BarStamp where AskSideRWsize is not null and one with the latest BarStamp where BidSideRWsize is not null?
If you provide the expected results It might help to understand your question.
Regards,
Me
May 12, 2010 at 9:32 am
The table i posted at the start is what the table looks like in SQL.
Sorry, but your anwers made it even more obscure:
What you mean by "the last not null field"?
1. Do you want to select AskSideRWsize if the BidSideRWsize is null?
use ISNULL(BidSideRWsize, AskSideRWsize)
> I want to select only when there is data in the field
>> Select what? Select records from table where the value in one of the field is not null?
Or select the records from table where value in the specified field is not null?
Or select some thing else.
>>>select the records from table where value in the specified field is not null
2. If both values (AskSideRWsize & BidSideRWsize) aere null, do you want TickID returned?
BidSideRWsize
BarStamp TickID AskSideRWsize BidSideRWsize
> one of the values will always be null, one script is for real-time access, therefore i only need the last record that contains data in it.
>> Look, if there is a record then, I can assure, you there is always some kind of data in it (nulls are data as well), otherwise there is no record at all!
Do you whant to select the last record (eg. the record with the latest BarStamp) where one of the AskSideRWsize or BidSideRWsize is not null?
Or do you want to slelect exactly two rows: one with the latest BarStamp where AskSideRWsize is not null and one with the latest BarStamp where BidSideRWsize is not null?
>>this one
If you provide the expected results It might help to understand your question.
SELECT AskSideRWsize,BidSideRWsize,TickID FROM FullOrderBookES WITH (NOLOCK) WHERE BarStamp =(SELECT MAX(BarStamp)FROM FullOrderBookES)
returns a table
AskSideRWsize BidSideRWsize TickID
194 NULL 33757
NULL 154 33758
194 NULL 33759
NULL 154 33760
194 NULL 33761
NULL 155 33762
196 NULL 33763
NULL 152 33764
201 NULL 33765
NULL 151 33766
I am trying to return the last record with number in it
AskSideRWsize=194
BidSideRWsize=154
If I was using a between statement by using barstamp to select a part of the data base,
I would still want the same process as above
to select the last tick ID for AskSideRWsize and BidSideRWsize this has a number in it
May 12, 2010 at 10:01 am
Hi,, this is short but still performs your request
;WITH CTE as
(
SELECT MAX(TickID) AskSideRWsizeTickID
FROM #TABLE WHERE AskSideRWsize IS NOT NULL
UNION ALL
SELECT MAX(TickID) BidSideRWsizeTickID
FROM #TABLE WHERE BidSideRWsize IS NOT NULL
)
SELECT A.*
FROM
#TABLE A
INNER JOIN
CTE B
ON
B.AskSideRWsizeTickID = A.TickID
Tell me if this is what you want!
May 12, 2010 at 10:19 am
very close
BarStamp TickID AskSideRWsize BidSideRWsize
2010-05-12 17:14:00.00039972182NULL
2010-05-12 17:14:00.00039973NULL215
However my reader is very limited
if (!reader.IsDBNull(0))Asksidevalue=((int)reader.GetDecimal(0));
if (!reader.IsDBNull(1))Bidsidevalue=((int)reader.GetDecimal(1));
I can only pull the first row on an object
thats why i have to avoid nulls
my select expression has to be like
BarStamp = new DateTime(Time[0].Year, Time[0].Month, Time[0].Day, Time[0].Hour, Time[0].Minute, Time[0].Second);
sSQL = "SELECT Max(AskSideRWsize),Max(BidSideRWsize) FROM FullOrderBookES WITH (NOLOCK) WHERE BarStamp =(SELECT MAX(BarStamp)FROM FullOrderBookES)";
using (SqlCommand myCommand = new SqlCommand(sSQL,dbconnection))
{
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
//BarStamp=(DateTime)reader.GetValue(0);
if (!reader.IsDBNull(0))Asksidevalue=((int)reader.GetDecimal(0));
if (!reader.IsDBNull(1))Bidsidevalue=((int)reader.GetDecimal(1));
}
reader.Close();
}
May 12, 2010 at 12:24 pm
(
SELECT AskSideRWsize AskSideRWsizeTickID, BidSideRWsize BidSideRWsizeTickID
FROM FullOrderBookES
WHERE TickID=(SELECT MAX(TickID) FROM FullOrderBookES WHERE AskSideRWsize IS NOT NULL)
OR
TickID=(SELECT MAX(TickID) FROM FullOrderBookES WHERE BidSideRWsize IS NOT NULL)
)
Closer,
162NULL
NULL190
I just want to read
162 192
May 12, 2010 at 12:44 pm
using (SqlCommand myCommand = new SqlCommand(sSQL,dbconnection))
{
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
//BarStamp=(DateTime)reader.GetValue(0);
if (!reader.IsDBNull(0))Asksidevalue=((int)reader.GetDecimal(0));
reader.NextResult();
if (!reader.IsDBNull(1))Bidsidevalue=((int)reader.GetDecimal(1));
}
reader.Close();
}
This will access the next record in the object?
reader.NextResult();
Thanks
nearly there
May 12, 2010 at 1:33 pm
Small modification to the given query will do what you want:
;WITH CTE as
(
SELECT MAX(TickID) AskSideRWsizeTickID
FROM #TABLE WHERE AskSideRWsize IS NOT NULL
UNION ALL
SELECT MAX(TickID) BidSideRWsizeTickID
FROM #TABLE WHERE BidSideRWsize IS NOT NULL
)
SELECT MAX(A.AskSideRWsize) AS AskSideRWsize, MAX(BidSideRWsize) AS BidSideRWsize
FROM #TABLE A
INNER JOIN CTE B
ON B.AskSideRWsizeTickID = A.TickID
May 12, 2010 at 1:54 pm
Thankyou thats it
Just 1 more question
using (SqlCommand myCommand = new SqlCommand(sSQL,dbconnection))
{
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
//BarStamp=(DateTime)reader.GetValue(0);
if (!reader.IsDBNull(0))Asksidevalue=((int)reader.GetDecimal(0));
reader.NextResult();
if (!reader.IsDBNull(1))Bidsidevalue=((int)reader.GetDecimal(1));
}
reader.Close();
}
When I call SQL using OleDB, I bring back an object, how do I move through the records if the object is a table?
Can I make the object an array?
and is reader.NextResult();
going to move me a row or too another select statement in a batch?
May 12, 2010 at 3:08 pm
Hi Jon,
no, NextResult() would fetch the next resultset if you would execute 2 querys in a batch, e.g.
SELECT 1 AS test;
SELECT 2 AS test;
To move to the next record in one resultset, you should use reader.Read().
But in my opinion you should use a Stored Procedure for that and return these 2 values as OUT parameters. (google + MSDN is your friend)
To return a array like thing, you can use ExecuteDataset which returns a DataSet consisting of the resultsets as DataTables. The DataRows and DataColumns of these DataTables can be accessed via foreach and index.
Steffen.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply