using a string statment inside sql "between" statement

Question

using a string statment inside sql "between" statement

jon pill

SSCommitted

Points: 1659
More actions
September 24, 2007 at 1:40 pm

#181359

I have found a fast way to do my query, first I find the max timestamp then include that in the sql statement
So I find it here:
rsRecordset7.Open "select TIMESTAMP from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)",
gcnConnect
DSMAXTS = rsRecordset7.Fields(0).Value
rsRecordset7.Close
and add it into the statement:
rsRecordset.Open "select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR from DEMANDSPREAD WHERE TIMESTAMP=" & "'" & DSMAXTS & "'", gcnConnect
now I am foxed when i want to use it here in a "between" statement, if i have the max timestamp i can the use sql to find 1 min back?
and trigger this statement
rsRecordset3.Open "SELECT AVG(s.ESDSTotal) AS ESDSTotal FROM dbo.DemandSpread s INNER JOIN (SELECT MAX(s1.[TimeStamp]) AS MaxTimeStamp FROM dbo.DemandSpread s1) d ON s.[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp", gcnConnect
Sheet1.Range("A29") = rsRecordset3.Fields(0).Value
rsRecordset3.Close
for some reason I cant put it in to this statement
what should i be doing?

Viewing 13 posts - 1 through 12 (of 12 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

assuming your timestamp is a timestamp data type then it doesn't record time but a binary value which is sequential(ish ) if you want to do datetime calcs the column must contain a datetime

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

jon pill SSCommitted Points: 1659 More actions · Answer 2

I record it as a string so its works on the first statement as I send thru the where timestamp statement as a string, which matches the timestamp in sql

the timestamp in sql is date time, so it matches up

Thanks for replying

jon pill SSCommitted Points: 1659 More actions · Answer 3

These 2 statements work its the between statement thats not

So I find it here:

rsRecordset7.Open "select TIMESTAMP from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)",

gcnConnect

DSMAXTS = rsRecordset7.Fields(0).Value

rsRecordset7.Close

and add it into the statement:

rsRecordset.Open "select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR from DEMANDSPREAD WHERE TIMESTAMP=" & "'" & DSMAXTS & "'", gcnConnect

jon pill SSCommitted Points: 1659 More actions · Answer 4

so i dont think i need inner join just then between

rsRecordset3.Open "SELECT AVG(s.ESDSTotal) AS ESDSTotal FROM dbo.DemandSpread s BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND DSMAXTS", gcnConnect

Sheet1.Range("A29") = rsRecordset3.Fields(0).Value

rsRecordset3.Close

something like that

jon pill SSCommitted Points: 1659 More actions · Answer 5

jon pill

SSCommitted

Points: 1659

September 25, 2007 at 3:11 pm

#736171

ok solved it

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

Pass it forward, Jon... post the code you solved it with.

And, by the way, saving a timestamp (datetime) in a table as any character or integer based data is death by SQL. 😉

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

have you noticed how this works out? L death by sql (dbs) = database bullshit by a DBA.

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

Sheeeooottt... what I'm really amazed at is how many people ask for help, get it, use an answer to write a fix, and then don't share. :exclamation: I know that's the way some folks are on forums but I've seen about a dozen of these in the last day or so.

The other thing that I'm truly amazed at is that folks that think storing some formatted date is going to work out for them in the long run. And, when you tell them about it, they just clam up. :pinch: They have no idea of the future hell they've made for themselves. 😀

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Take note of where they work, then wait a few months and send in your resume with more than generous rates :).

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 10

Heh... they won't be able to keep the appointment for the interview because their dates will be all haywire...

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

September 25, 2007 at 9:33 pm

#736225

:D:P;):w00t::hehe::):Whistling:

Nice one!

jon pill SSCommitted Points: 1659 More actions · Answer 12

this worked for me and never any errors

rsRecordset7.Open "select TIMESTAMP from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD WITH (NOLOCK))", gcnConnect

DSMAXTS = rsRecordset7.Fields(0).Value

rsRecordset7.Close

Sheet1.Range("E10") = DSMAXTS

then reference DSMAXTS

rsRecordset3.Open "SELECT AVG(ESDSTotal) AS ESDSTotal FROM dbo.DemandSpread WHERE TIMESTAMP BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND " & "'" & DSMAXTS & "'", gcnConnect

Sheet1.Range("A29") = rsRecordset3.Fields(0).Value

rsRecordset3.Close