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

Post the SQL you have so far.

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

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

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

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"

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.

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