Between Statment only for current day

Question

Post reply

Between Statment only for current day

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

jon pill SSCommitted Points: 1599 More actions · Answer 1

jon pill

SSCommitted

Points: 1599

July 14, 2010 at 9:08 am

#1192888

DECLARE @TT INT



SET @TT =(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)where BarStamp >= '7/14/2010') 

SELECT

min(upvolume),

min(downvolume),

min(upvolume),

min(downvolume)

from DBVaskVbid

WHERE TotalTrades

BETWEEN

@TT -1000

AND

@TT

Thank you that is it sorted 🙂

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Jon,

The last query you posted looks very wrong (or crap, in Australian :-D).

For one or another reason everyone here is struggling to understand your filtering requirements.

I am, again, strongly suggesting you to read the link in my signature.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jon pill SSCommitted Points: 1599 More actions · Answer 3

jon pill

SSCommitted

Points: 1599

July 14, 2010 at 9:36 am

#1192915

you right that cant be it either

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

jon pill (7/14/2010)

you right that cant be it either

Describe it in words. Be nice!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jon pill SSCommitted Points: 1599 More actions · Answer 5

I have data that I want to find the Min and Max off, for 2 fields

Upvolume and DownVolume

Every day the Totaltrades field starts at zero and cumulates throughout the day, there will be more than 1 day.

I want to find the maximum totaltrades and the maximum totaltrades less 1000

to then find

Then find the min and max for the upvolume and downvolume of the above filter

That is all

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

jon pill (7/14/2010)

I have data that I want to find the Min and Max off, for 2 fields

Upvolume and DownVolume

Every day the Totaltrades field starts at zero and cumulates throughout the day, there will be more than 1 day.

I want to find the maximum totaltrades and the maximum totaltrades less 1000

to then find

Then find the min and max for the upvolume and downvolume of the above filter

That is all

How about "The last 1000 totaltrades numbers for today - there may be gaps and dupes in the totaltrades sequence" - how does this fit?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

Chris Morris-439714 (7/14/2010)

jon pill (7/14/2010)

I have data that I want to find the Min and Max off, for 2 fields

Upvolume and DownVolume

Every day the Totaltrades field starts at zero and cumulates throughout the day, there will be more than 1 day.

I want to find the maximum totaltrades and the maximum totaltrades less 1000

to then find

Then find the min and max for the upvolume and downvolume of the above filter

That is all

How about "The last 1000 totaltrades numbers for today - there may be gaps and dupes in the totaltrades sequence" - how does this fit?

Chris, until OP will provide case setup srcipt with good sample of data representing real situation and expected results it will be hard to help help him (3 pages of the thread is a good avidence of that...)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jon pill SSCommitted Points: 1599 More actions · Answer 8

jon pill

SSCommitted

Points: 1599

July 14, 2010 at 10:07 am

#1192949

There will be gaps but it matters little, i only need to get the data the same way every time

The same with duplicates, it wont affect the hi and low

Many thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

jon pill (7/14/2010)

There will be gaps but it matters little, i only need to get the data the same way every time

The same with duplicates, it wont affect the hi and low

Many thanks

Of course it will, which is why folks are hitting you hard to get a better definition of what you mean by totaltrades-1000. It's still ambiguous. My best guess right now is:

Find the highest figure of totaltrades for today

Subtract 1000 from that figure.

If your requirement is this simple then say so, because then your filter becomes "where the rows are from today and the totaltrades number is higher than x", where x = totaltrades-1000.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jon pill SSCommitted Points: 1599 More actions · Answer 10

jon pill

SSCommitted

Points: 1599

July 14, 2010 at 10:21 am

#1192958

Find the highest figure of totaltrades for today

Subtract 1000 from that figure.

yes it is that simple

very sorry i can see me error now

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11



SELECT upvolume,downvolume,TotalTrades

INTO #TodayRecords

FROM DBVaskVbid  WITH (NOLOCK)  -- are you fully awre of what this hint does?

WHERE BarStamp >= '7/14/2010'

-- do you have huge vlume of records per day, than the following index might help

CREATE INDEX idx_TodayRecords ON #TodayRecords(TotalTrades)

DECLARE @MinTtlTrades INT

SELECT @MinTtlTrades = MAX(TotalTrades) - 1000 FROM #TodayRecords

SELECT MAX(upvolume), MAX(downvolume), MIN(upvolume), MIN(downvolume)

FROM #TodayRecords

WHERE TotalTrades > @MinTtlTrades

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 12

Eugene Elutin (7/14/2010)

SELECT upvolume,downvolume,TotalTrades INTO #TodayRecords FROM DBVaskVbid WITH (NOLOCK) -- are you fully awre of what this hint does? WHERE BarStamp >= '7/14/2010' -- do you have huge vlume of records per day, than the following index might help CREATE INDEX idx_TodayRecords ON #TodayRecords(TotalTrades) DECLARE @MinTtlTrades INT SELECT @MinTtlTrades = MAX(TotalTrades) - 1000 FROM #TodayRecords SELECT MAX(upvolume), MAX(downvolume), MIN(upvolume), MIN(downvolume) FROM #TodayRecords WHERE TotalTrades > @MinTtlTrades

One thing that keeps jumping out at me is the phrase "for today" (paraphrased most likely).

Part of the where clause should probably look like this:



WHERE BarStamp >= dateadd(dd, datediff(dd, cast('1900-01-01' as datetime), getdate()), cast('1900-01-01' as datetime)

jon pill SSCommitted Points: 1599 More actions · Answer 13

jon pill

SSCommitted

Points: 1599

July 14, 2010 at 11:51 am

#1193018

Thank you for that Lynn

I was sending the SQL statement from another application, but I will add that to my list of statements in SQL

jon pill SSCommitted Points: 1599 More actions · Answer 14

jon pill

SSCommitted

Points: 1599

July 15, 2010 at 5:46 am

#1193387

Really sorry about this,

But I can only run the query once

Msg 2714, Level 16, State 6, Line 1

There is already an object named '#TodayRecords' in the database.

I would like to run this many times a day, how can I clear the object?

jon pill SSCommitted Points: 1599 More actions · Answer 15

jon pill

SSCommitted

Points: 1599

July 15, 2010 at 6:05 am

#1193407

drop table #TodayRecords

Is this the fastest way, i will have over a 100 lines and will be calling sql every 500ms?