I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.
The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement 2015-06-26 14:00:00.000 6 to 4 2015-06-26 14:00:00.000 11 to 7 2015-06-26 15:30:00.000 9 to 6 2015-06-26 21:00:00.000 7 to 3 2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
Hello people, I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this: UPDATE S_ACC_MONEYSET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108) Does anyone knows a better way to do that? Any other comments? Thanks!
I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Hi all, We have many messages in SQL server logs that say 'Log backed up:Database:COM,creation date(time):2003/03/26(19:41:58),first' why is this message appeared every one hour?
Hi All, I have several servers each with an instance of SQL Server with a number of databases in each instance. We are a law firm dealing mostly with documents (Modifying and creating new). Is there an easy way to determin how many transactions the transaction log handles per hour? Not all tables have a create date or modify date. Is there some way to monitor the transaction logs them selves? Thanks, Roy
I am doing a project that has start time and end time. i was given only the start time of cases, but not the end time.
for example:
start time
01:30:00
10:00:00
09:24:00
11:25:00
.
.
.
.
It was like, thousands of start time. I need to create an end time (smalldatetime) which is one hour passed the start time. can anyone tell me how to do it in sql query? thanks.
Hi All, I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries 1. Get number of orders per year 2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row) 3. Get number of orders per hour Note: If there are no orders in a year etc, it should return a row with 0.
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
CASE WHEN CAST(wo.start_date AS TIME) BETWEEN '00:00:00' AND '00:59:59' THEN 0 WHEN CAST(wo.start_date AS TIME) BETWEEN '01:00:00' AND '01:59:59' THEN 1 WHEN CAST(wo.start_date AS TIME) BETWEEN '02:00:00' AND '02:59:59' THEN 2 WHEN CAST(wo.start_date AS TIME) BETWEEN '03:00:00' AND '03:59:59' THEN 3 WHEN CAST(wo.start_date AS TIME) BETWEEN '04:00:00' AND '04:59:59' THEN 4
[code]....
The purpose is to take a row and set it to the hour of the day that it occurred in. This works fine, however I would like to force it to display every hour 0-23 regardless of whether or not it has a corresponding row.
So, if no row exists for 0, display 0 with null values for the rest of the columns.
from getdate(), how can i get today's date with specific time :
For example : (i just need specific 11oclock today) 2014-07-08 11:00:00.000
This do the trick : select DATEADD( HH, 11, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
But at second half of the day it changed from 2014-07-08 11:00:00.000 to the next day, i dont know why who controls the past controls the future, who controls the present controls the past. ¯(º_o)/¯ ~~~
I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.
AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230
Does anyone know where to purchase a good (or great) SQL hour scheduler program, and pulls up information from a SQL server and puts this information into a schedule (hourly schedule). I'm looking for something that will show the schedule that people work, the information off the SQL server needed is the "agent" name. If anyone could help me, that would be greatly appreciated.
One of our programmers has a stored procedure that joins two views into a new table.
One of the views that it is grabbing is fairly complicated pulling from several tables, and can take quite awhile to run on its own. This stored procedure has been running on several databases for several months just fine.
After switching to sql server 2005 it started taking almost 6 hours in one of the databases.
As of now, it runs fine in one of the dbs in a few minutes and produces 50,000 records. The db that it takes almost 6 hours to complete produces only 17,000 records. The programmer has restricted the fields and gets it to run in under a minute - but it doesn't seem to matter which fields, just how many.
After 3 or 4 of the records from the complex view, it goes from running in 1 min, to almost 6 hours again - no in between. It is the same code as the database where it runs fine and produces more records.
I want to backup the database per hour but not fully only the change that has been made from last one hour.Every hour I want the update data base is it possible.
is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?
Hello,I have been having a tough time writing the follow requirement for aquery.On a table that the primary key is a tagId and an hourly timestamp, Iwould like to find out for every hour which tags did not get enteredinto the database. Essentially I am looking for patterns of entriesthat are not making it into tableB.Examples of the tables:TableA TableBTagID and TagName TagId TimestampPK PK1 PK2approx 6000 rows approx 6000 rows per hourI am thinking that I will need to do something like:Select tableB1.time, count(*) from tableB1 group by tableB1.timehaving tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagIdnot in (select tagId from tableA where not exists (selecttableA.tagId, distinct.tableB2.time from tableB2)I have been trying to create an effecient query handle this but havenot had any luck. Any assistance would be more then appreciated.Thanks,Andy
I have a table with a [Timestamp] field which is a datetime data type. What I need to do is add one hour to the timestamp. Each entry in the [Timestamp] field looks like this: 2008-03-09 16:44:06.313
What is the best way to do this?
(Should I be using UPDATE [Tablename] or Alter Table [Tablename])
I am trying to write a SELECT statement to return the number of calls received by a Call Centre, grouped by the hour that they are received (i.e to reflect peaks and troughs throughout the 24 hr day). So, my SELECT statement is as follows:
SELECT DATEPART(HOUR, RecvdTime) AS [Hour of Day], COUNT(CallID) AS [Total Calls]
FROM Table1
GROUP BY DATEPART(HOUR, RecvdTime)
ORDER BY DATEPART(HOUR, RecvdTime)
However, the problem of course with this statement is that for Hours where there are no Calls (i.e a COUNT of zero) there is no result returned at all. What I am trying to acheive is for the query to return a result for all hours of the day, and to display a 0 for a particular hour if appropriate.
I am new in SQL Server and creating some SQL script that should give total transaction count per hour for a day. I need simply hour and count result. My table contains DateCreated column and unique ID column.
Can any one tell me how to subtracts and add hours to the current date and time? In my case I have to store the Vancouver date and time in Toronto. Thanks, T.Lingam
I need to query data from a table which has order_date which is datetime field. How to pull orders by hour by day per month for the last 4 months. Any idea how to write the query?
Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!
INSERT INTO Temp VALUES('Org Name', '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000') - AND many more like these with different START_TIME and END_TIME.
The Task:
- I need to calculate the duration of the activities PER HOUR.
i.e. in the example above, if I want to see the productivity for 2PM (i.e. activity duration from 2-3PM), I should only get 35 mins (as the activity started at 14:25). In the same way, if I see the productivity for 3PM (i.e activity duration from 3-4 PM), I should only see 25 minutes (as activity ended at 15:25).
There would technically be many activities with overlapping times - for example, there might be 5 activities starting at 14:30 and ending at 15:10. In this case, if I were to see the productivity for 2PM, I'd see 150 minutes (as each activity starts at 14:30, so 30 min per activity = 150 min). In the same way, if I saw the productivity for 3PM for those 5 activities, I'd see 50 minutes.
I am looking to write a query that returns all records Inserted in the last hour.
The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?
I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.
