Post reply

using a string statment inside sql "between" statement

  • 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?

  • September 25, 2007 at 10:17 am

    #736087

    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/

  • September 25, 2007 at 1:33 pm

    #736140

    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

  • September 25, 2007 at 1:35 pm

    #736141

    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

  • September 25, 2007 at 1:39 pm

    #736142

    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

  • September 25, 2007 at 3:11 pm

    #736171

    ok solved it

  • September 25, 2007 at 4:51 pm

    #736180

    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)
    Intro to Tally Tables and Functions

  • September 25, 2007 at 7:37 pm

    #736199

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

  • September 25, 2007 at 7:59 pm

    #736206

    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)
    Intro to Tally Tables and Functions

  • September 25, 2007 at 8:20 pm

    #736208

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

  • September 25, 2007 at 9:28 pm

    #736221

    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)
    Intro to Tally Tables and Functions

  • September 25, 2007 at 9:33 pm

    #736225

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

    Nice one!

  • February 11, 2008 at 1:47 pm

    #777066

    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

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

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