Counting Items Per Hour
Nov 2, 2005
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!
the table I have looks like this:
IDBadgeOrdNumberTimeDate
6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40
View 4 Replies
ADVERTISEMENT
Nov 14, 2001
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.
Thanks,
Eron
View 2 Replies
View Related
Jun 22, 2004
Hi,
I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:
SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1
This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?
Cheers.
View 6 Replies
View Related
Jun 18, 2015
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.
View 9 Replies
View Related
Oct 28, 2007
Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with
Like
Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)....
Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName
Which Produces
Real Estate12401 12
Extreme Sports3 4
I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports3 4
Cycling 5 0
View 2 Replies
View Related
Jul 20, 2005
I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?
View 1 Replies
View Related
Mar 30, 2008
I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.
Categories========ID intParentID intCatName varchar(30)Items====IDCategoryIDItemID
There is a third table that links to items through ItemID, but this is not important for this problem!
What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.
An end result with two top-level categories and three subcategories in each might look like:
- Category 1 (20)---- Subcategory 1.1 (10)---- Subcategory 1.2 (5)---- Subcategory 1.3 (5)- Category 2 (14)---- Subcategory 2.1 (3)---- Subcategory 2.2 (4)---- Subcategory 2.3 (7)Hence the difficult bit is getting the total number in brackets for each category with subcategories.
What I have at the moment is:
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotalFROM Categories
What I would like is something along the lines of (pseudocode):
SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotalFROM Categories
I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.
Thanks!
View 2 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 Replies
View Related
May 4, 2006
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!
View 1 Replies
View Related
Sep 29, 1999
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.
Thanks in advance for your opinions/help.
LN
View 3 Replies
View Related
Jul 23, 2004
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?
View 3 Replies
View Related
Oct 28, 2006
How to get hour from a date
Msg_Time = 10/28/2006 12:20:00 AM
here how to get hour, minuts
see for month we are using month(Msg_Time),
for year we are using year(Msg_Time) same way how to fetch the hour and minutes
all are appreciate
View 1 Replies
View Related
Jul 31, 2007
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
View 1 Replies
View Related
May 22, 2007
I have a pretty straight forward fact table that contains order information like this:
DateTimeStamp Price OrderID
I have a measure that does a simple row count for orders placed. How can I get the Max Orders Placed grouped by Hour? Something similar to this:
0:00 15
1:00 25
2:00 50
.....
23:00 75
I tried to do a max function like max([time].[hour], [measures].[Order Row Count]) but ended up with only one value. I know I'm doing it wrong.
I appreciate any help on this.
View 1 Replies
View Related
Apr 27, 2007
Hey guys,
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.
Jul
View 1 Replies
View Related
Dec 29, 2007
Hi
I'm trying to create a query, where I add one hour to a value (I marked it below with "+ 1 HOUR"). I can't find any informations, how I can do this.
Code Block
"SELECT field1, enddate, (enddate < GetDate() FROM table WHERE (enddate + 1 HOUR) < GetDate()"
Does anybody can help me with this problem?
Kind regards
Roland
View 3 Replies
View Related
Apr 7, 2007
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.
View 5 Replies
View Related
Aug 30, 2001
How do I get the hour in HH24 format from a datetime value?
Many Thanks.
View 3 Replies
View Related
Jan 22, 2007
Hi all:
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
Thanks.:confused:
View 1 Replies
View Related
Aug 5, 2007
I want to round times down to the previous hour,
For example a call time of 7/31/07 11:51:13.532 would become 7/31/07 11:00:00.000.
View 14 Replies
View Related
Dec 3, 2013
I have the following Case statement:
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.
View 2 Replies
View Related
Jul 8, 2014
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)/¯ ~~~
View 1 Replies
View Related
Sep 11, 2014
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
View 13 Replies
View Related
Nov 24, 2005
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.
Thanks.
View 1 Replies
View Related
Feb 15, 2007
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.
Any thoughts would be greatly appreciated.
Dave
View 7 Replies
View Related
Nov 28, 2007
Hi ,
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.
Abhi
View 3 Replies
View Related
Jan 26, 2008
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?
Shimi
View 2 Replies
View Related
Jul 17, 2007
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
View 1 Replies
View Related
Mar 27, 2008
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])
View 4 Replies
View Related
Apr 1, 2008
Hi, I'm hoping you guys can help me with a query.
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.
Can anybody guide me as to an approach for this?
Many thanks
Matt
View 13 Replies
View Related
