Close

Results 1 to 8 of 8

Thread: SQL Help Needed

  1. #1
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default SQL Help Needed

    Hi, I currently have a spreadsheet that pulls data from an SQL Database. The spreadsheet displays the top (Most recent) 24 records from the DB whenever it is refreshed. As this DB is an hourly data log, the spreadsheet is simply a 24hr display of historical data - with some charts that populate based on the data returned.

    I'd like to improve this - but don't know how to... The SQL query is in a cell located in a different sheet of the workbook, so I can alter the query and see the results appear. What I would l like to do is be able to specify the year, month, date and hour to get the 24hr report starting at the hour specified. There is already a column for each of these parameters in the DB Table I just have not been able to put together a query that will pick the row I want plus the 23 that come after it. If I just wanted the 24 hrs from midnight day 1 to midnight day 2 it would be straightforward, but because it (could) span across the midnight rollover point I am struggling.

    I should also mention that there is an index column that increments each time a row is added to the DB as I've a feeling this could be the key...

  2. #2
    DF VIP Member flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,696
    Thanks
    11
    Thanked:        131
    Karma Level
    473

    Default Re: SQL Help Needed

    Post the SQL you have so far.

  3. #3
    DF VIP Member SiE's Avatar
    Join Date
    Jan 2001
    Location
    My comfy sofa
    Posts
    7,211
    Thanks
    196
    Thanked:        407
    Karma Level
    785

    Default Re: SQL Help Needed

    Would you not be better doing it in Access? MS charts can do your charting and you can pull your data from anywhere.


    Sent from my iPhone using Tapatalk

  4. #4
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    368

    Default Re: SQL Help Needed

    Pop the excel sheet up and should be easy fix mate ;-) but basically you want a BETWEEN statement
    if you always what 24 hours then it would be
    WHERE [date] BETWEEN {DateTimeStirng} AND DATEADD(HOUR,24,{DateTimeString})

    But you could be cocky and do WHERE [date] BETWEEN {DateTimeStirng} AND DATEADD(Day,{ExcellCell},{DateTimeString})
    Have the ExcelCell as a number and you could then vary the period i.e. 24 for 24 hours but could be 48 hours if wanted 2 days for instance

  5. #5
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default Re: SQL Help Needed

    The existing query is very simple:

    SELECT TOP(24)* FROM (DB) ORDER BY PointIndex desc

    This returns the most recent 24 rows. excel then has a couple of charts that show the data in a more pleasing way. As I mentioned there are columns for "Year", "Month", "Date" and "Hour" all as Integer that could be queried against as well as an "Index".

    I'd rather keep it to excel at the minute as I'm doing this to learn a little about VBA etc..

  6. #6
    DF VIP Member inspectercoley's Avatar
    Join Date
    May 2007
    Location
    TaxiMike
    Posts
    3,733
    Thanks
    234
    Thanked:        64
    Karma Level
    405

    Default Re: SQL Help Needed

    SELECT TOP(24)* FROM (DB) WHERE year = 2016 and month = 1 ORDER BY PointIndex desc

    That should do it.

    You can then just reference the cells where you want this to work:

    ="SELECT TOP(24)* FROM (DB) WHERE year = "&A1&" and month = "&a2&" ORDER BY PointIndex desc"

    You may want to setup rules that define things a bit better i.e.

    ="SELECT TOP(24)* FROM (DB)"&IF(A1="","","where year = ")&A1&" ORDER BY PointIndex desc"

    This means if it's blank the where condition won't exist.

    Depending on how you want this to work where you put the " and " can create problems.

    ="SELECT TOP(24)* FROM (DB) WHERE "&IF(A1="","","year = ")&A1&IF(A2="",""," and month = ")&A2&" ORDER BY PointIndex desc"

    This will only work if you specify the year and the month, or just the year.

    So the final line would look more like this (I have used 1=1 to make it simpler if that's easier):

    ="SELECT TOP(24)* FROM (DB) where "&IF(A1="","1=1","year = "&A1)&" and "&IF(A2="","1=1","month = "&A2)&" ORDER BY PointIndex desc"
    Last edited by inspectercoley; 12th February 2016 at 10:54 AM.

  7. #7
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default Re: SQL Help Needed

    The first example seems straightforward - except it will return the most recent 24 records for the month (As opposed to a 24hr report)

    Presumably the second example is just going to insert whatever I input into cells A1 and A2 directly into the query? (I didn't realize you could do that but makes sense)

    Can't quite make sense of the third example (&IF??) and after that I'm completely lost. I'll have to have a try at it and maybe when I see the results for different things I insert it may make more sense.

  8. #8
    DF VIP Member inspectercoley's Avatar
    Join Date
    May 2007
    Location
    TaxiMike
    Posts
    3,733
    Thanks
    234
    Thanked:        64
    Karma Level
    405

    Default Re: SQL Help Needed

    Select * instead of top 24 will give you all the records.

    The other examples allow you to specify the values without editing the sql, so just specify what you want in the cells.

    The last example allows some fields to be included or not.

    If(a, then do something, otherwise do something else)

    So the formula means is you haven't got anything in a1 then the where statement will be 1=1. This is always true so doesn't do anything. If it's filled in then create the where condition. E.g. Year = 2016
    [live=TaxiMike]TaxiMike[/live]

Similar Threads

  1. needed opinions
    By krazi30173 in forum Site Critique
    Replies: 1
    Last Post: 20th September 2002, 03:34 PM
  2. Saturn info needed
    By Shiver in forum Old Skool Gaming & Retro
    Replies: 8
    Last Post: 16th September 2002, 12:01 PM
  3. Advice needed! Xbox or Gamecube
    By Grooster in forum Microsoft Consoles
    Replies: 6
    Last Post: 5th September 2002, 01:05 PM
  4. 2 Dire Straights tracks needed
    By Fett in forum Music Factory
    Replies: 2
    Last Post: 5th September 2002, 12:29 PM
  5. Opinions needed for Originality and Concept
    By Scat in forum Site Critique
    Replies: 0
    Last Post: 28th August 2002, 10:33 AM

Social Networking Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •