How to query microsoft sql data and get the current hour and previous hour

Question

I have never worked with database queries inside of Excel before, but have been asked to come up with a spreadsheet that shows the current hour and previous hour's completed units. Pulling in the basic info from the database was easy. I have the spreadsheet showing just the columns that I need from the table. Right now it is pulling in all data, but as I said above, I need it to just show the current date, the previous hour and current hour in time format (Ex: 07:00), and the sum of the completed units for those hours. The 3 columns that I am working with are: Date: Shown as 2/22/2024; Data Type is Date Hour: The Data Type is Decimal Number and shows a number 0 thru 24. So, if it's 1pm the number in the column is 13. Complete: The Data Type is Whole Number. There are multiple rows per hour for completed units As stated above, I need to show just the previous and current hour for the current date and a total of units completed in that hour. The table then needs to update every 5 minutes to refresh the completed amounts. Below is an example of data in the table.

Any help is appreciated.

Answer 1

It sounds like you're using Excel to run a query in SQL to get the data you need. If you want to filter the data that comes back from SQL to just now plus previous hour (so 2 rows) then that becomes part of your WHERE clause in your SQL query.

You can set this up a couple of ways. SQL itself can do the calculation if you are sure this is the only range you want.

SELECT ... FROM table WHERE [Date] = GETDATE() AND [Hour] BETWEEN CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 114),1,2))-1 AND CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 114),1,2))

Based upon your example, the challenge here is that you are using 24 hour time but GETDATE will use whatever time format is the default which is most likely 12 hour time. So you have to convert to 24 hours. The above SUBSTRING/CONVERT code takes the current date, gets the 24-hour time out of it and converts it back to a number. The result is all rows that are on the current date and between last hour and now, inclusive.

Alternatively you can do this on the Excel side. This is probably easier and especially better if you need to be able to change the range later. To do that add 2 parameters to your query in Excel: the begin hour and the end hour. Then adjust the SQL query to use the parameters instead of the code above. This would eliminate the SUBSTRING/CONVERT logic.

Note that you could possibly optimize the SQL query by combining the date and hour columns into a single DATETIME value and then your query would become quite a bit simpler. as you could just use BETWEEN. Something like this perhaps.

SELECT [Date], Hour, CONVERT(DATETIME, DATEADD(hour, Hour, [Date]))
FROM @table
WHERE CONVERT(DATETIME, DATEADD(hour, Hour, [Date])) BETWEEN DATEADD(hour, -1, GETDATE()) AND GETDATE()

For this to work your Date column would have to be just a date with no time.

Answer 2

Michael Taylor's query is overly complex. This is simpler:

SELECT * 
FROM   tbl 
WHERE  datediff(HOUR, convert(char(8), date, 112) + ' ' + convert(varchar, hour),
                      getdate()) IN (0, 1)

By the way, getdate() has no format at all; it returns a binary value. And it cannot be compared to something which is date only, since getdate() returns both date and time.