Between Statment only for current day

Question

Between Statment only for current day

jon pill

SSCommitted

Points: 1659
More actions
July 13, 2010 at 8:44 am

#235104

SELECT min(upvolume),min(downvolume),MAX(upvolume),max(downvolume)
FROM DBVaskVbid WITH (NOLOCK)
WHERE TotalTrades
BETWEEN (SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))
AND
(SELECT MAX(TotalTrades)-1000 FROM DBVaskVbid WITH (NOLOCK))
I have a datestamp field registering 7/13/2010 3:38:00 PM and an cumulative ID field
What I am trying to do is select the maximum Totaltrades from the database on todays date only, as yesterdays totaltrades field will start from zero.
I also want the between statement to be Totaltrades(the last incoming record) minus 1000, but I am not getting that through on this statement
Thanks
Neil

Viewing 15 posts - 1 through 15 (of 46 total)

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

jon pill SSCommitted Points: 1659 More actions · Answer 1

SELECT min(upvolume),min(downvolume),MAX(upvolume),max(downvolume)
FROM DBVaskVbid WITH (NOLOCK)
WHERE TotalTrades
BETWEEN
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000
AND
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))

A better statement, but how can i link today's date only, or the last record - 1000?

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

jon pill (7/13/2010)
SELECT min(upvolume),min(downvolume),MAX(upvolume),max(downvolume)
FROM DBVaskVbid WITH (NOLOCK)
WHERE TotalTrades
BETWEEN (SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))
AND
(SELECT MAX(TotalTrades)-1000 FROM DBVaskVbid WITH (NOLOCK))
I have a datestamp field registering 7/13/2010 3:38:00 PM and an cumulative ID field
What I am trying to do is select the maximum Totaltrades from the database on todays date only, as yesterdays totaltrades field will start from zero.
I also want the between statement to be Totaltrades(the last incoming record) minus 1000, but I am not getting that through on this statement
Thanks
Neil

I can't see that your are trying to select the maximum Totaltrades from the database on todays date only. Where the check for today? Which column contains datestamp?

What you mean by Totaltrades(the last incoming record)? Is it Totaltrades value of the last record for the required day or what?

Actually, I would strongly advise you to click the link in my signature. You will find some information which will help you to represent your case/question in a proper and "forum polite" way.

_____________________________________________
"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: 1659 More actions · Answer 3

SELECT

min(upvolume),

min(downvolume),

max(upvolume),

max(downvolume)

FROM

DBVaskVbid WITH (NOLOCK)

WHERE TotalTrades

BETWEEN

(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000

AND

(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))

The above code is working, the field TotalTrades is reset to zero every day, so therefore selecting Max total trades wont be the maximum total trades for the current day, so I need to reference todays date to get the real time totaltrades as it cumulates from zero every new day

Thanks

jon pill SSCommitted Points: 1659 More actions · Answer 4

jon pill

SSCommitted

Points: 1659

July 13, 2010 at 9:12 am

#1192123

I dont know how to do the check for today only

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

...

click 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: 1659 More actions · Answer 6

jon pill

SSCommitted

Points: 1659

July 13, 2010 at 9:29 am

#1192155

What is wrong with the post, it is orderly?

jon pill SSCommitted Points: 1659 More actions · Answer 7

WHERE BarStamp > '7/13/2010'

ok this works to select the current day, now i need to join it

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

jon pill (7/13/2010)
What is wrong with the post, it is orderly?

Nothing is wrong if you don't mind either verbal-only answers, swags, or incorrect code. People like to test their code before they post it but don't want to have to guess about the datatypes in the table nor create any test data. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jon pill SSCommitted Points: 1659 More actions · Answer 9

SELECT min(upvolume),min(downvolume),Max(upvolume),Max(downvolume)
FROM
(SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/13/2010') as XDtable
WHERE TotalTrades
BETWEEN
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000
AND
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))

This worked for me

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

jon pill (7/14/2010)
SELECT min(upvolume),min(downvolume),Max(upvolume),Max(downvolume)
FROM
(SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/13/2010') as XDtable
WHERE TotalTrades
BETWEEN
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000
AND
(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))

This worked for me

Jon, what if a value of TotalTrades is higher from yesterday than any for today?

^{“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: 1659 More actions · Answer 11

SELECT min(upvolume),min(downvolume),min(upvolume),min(downvolume)

FROM

(SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010') as XDtable

WHERE TotalTrades

BETWEEN

(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010') -1000

AND

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

This is my solution, is there a better way to write this, I am sure I have to many "where BarStamp >= '7/14/2010') -1000" statements

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

I like noodles some times, but usually I have them cooked not coded...:-D

Read about using CTE in SQLServer. Also, introducing variable to hold MAX(TotalTrades) will make your code look better and perform faster

_____________________________________________
"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]

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

jon pill (7/14/2010)
SELECT min(upvolume),min(downvolume),min(upvolume),min(downvolume) FROM (SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010') as XDtable WHERE TotalTrades BETWEEN (SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010') -1000 AND (SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010')
This is my solution, is there a better way to write this, I am sure I have to many "where BarStamp >= '7/14/2010') -1000" statements

There's almost certainly a better way of writing this, but not without a better way of going about it. Write the query step by step, starting with the filter. You want to select a set of data from your source table to work with. Write the query to do that, then extend it to incorporate the aggregate afterwards. Most of us are still scratching our heads trying to figure out the filter - but that's probably because you are, too.

^{“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: 1659 More actions · Answer 14

SELECT upvolume as UPVol,

downvolume as DNVol,

TotalTrades as TTrades,

Barstamp as BStamp

from DBVaskVbid WITH (NOLOCK) where BarStamp >= '7/14/2010'

46411473972010-07-14 13:08:00.000

46411973982010-07-14 13:08:00.000

46511984002010-07-14 13:08:00.000

47611984022010-07-14 13:08:00.000

This is the filter, it works correctly

Now all I want is the Min and Max from the rows of that table.

But I am stepping back into the database to find the min max of upvolume and downvolume