|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Orders Per Hour CountHello,
I have a table with 3 columns ProductID int, Product varchar(30), OrderDate datetime I have orders in this table from January to July I want to get a query that will display how many orders I am getting per day and per hour. Do you mean an average per day, and an average per hour?
Or do you want a list of counts for every single day and hour since Jan. 1st? Show quote "Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message news:08BBBD1C-C288-4A63-828D-8C1F8CD9C3F5@microsoft.com... > Hello, > > I have a table with 3 columns > > ProductID int, Product varchar(30), OrderDate datetime > > I have orders in this table from January to July I want to get a query > that > will display how many orders I am getting per day and per hour. > An average per day and then another script for per hour
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Do you mean an average per day, and an average per hour? > > Or do you want a list of counts for every single day and hour since Jan. > 1st? > > > > "Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message > news:08BBBD1C-C288-4A63-828D-8C1F8CD9C3F5@microsoft.com... > > Hello, > > > > I have a table with 3 columns > > > > ProductID int, Product varchar(30), OrderDate datetime > > > > I have orders in this table from January to July I want to get a query > > that > > will display how many orders I am getting per day and per hour. > > > > > Maybe these queries will help? (untested, btw)
SELECT DailyAverage = AVG(c) FROM ( SELECT DATEDIFF(DAY, 0, OrderDate), c = COUNT(*) FROM Orders WHERE OrderDate >= '20060101' -- to leave out today's orders: -- AND OrderDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETUTCDATE())) GROUP BY DATEDIFF(DAY, 0, OrderDate) ) x SELECT [Hour], HourlyAverage = AVG(c) FROM (SELECT OrderDate, [Hour] = DATEPART(HOUR, OrderDate), c = COUNT(*) FROM Orders WHERE OrderDate >= '20060101' GROUP BY OrderDate, DATEPART(HOUR, OrderDate) ) x Show quote "Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message news:3A3916B9-659E-4BE4-B271-704E36685448@microsoft.com... > An average per day and then another script for per hour > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> Do you mean an average per day, and an average per hour? >> >> Or do you want a list of counts for every single day and hour since Jan. >> 1st? >> >> >> >> "Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message >> news:08BBBD1C-C288-4A63-828D-8C1F8CD9C3F5@microsoft.com... >> > Hello, >> > >> > I have a table with 3 columns >> > >> > ProductID int, Product varchar(30), OrderDate datetime >> > >> > I have orders in this table from January to July I want to get a query >> > that >> > will display how many orders I am getting per day and per hour. >> > >> >> >>
Show quote
> SELECT Careful: If the OrderDate column is not stored as CHAR(8), your WHERE > DailyAverage = AVG(c) > FROM > ( > SELECT > DATEDIFF(DAY, 0, OrderDate), > c = COUNT(*) > FROM > Orders > WHERE OrderDate >= '20060101' > -- to leave out today's orders: > -- AND OrderDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETUTCDATE())) > GROUP BY > DATEDIFF(DAY, 0, OrderDate) > ) x clause will fail. But big-time nerd-points on that DATEDIFF in the GROUP BY.... I would never have thought of that. Something to demo for my 2071 students in the morning. Dang. That was better than sex. -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "y = (-b ± (b^2 - 4 * a * c)^.5) / 2 * a" -- Dr. Houser >> WHERE OrderDate >= '20060101' I assumed it was a DATETIME or SMALLDATETIME. Why would the WHERE clause >> -- to leave out today's orders: >> -- AND OrderDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETUTCDATE())) >> GROUP BY >> DATEDIFF(DAY, 0, OrderDate) >> ) x > > Careful: If the OrderDate column is not stored as CHAR(8), your WHERE > clause will fail. fail??? "Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message Are you sure you're doing it right then?news:e0fj3UesGHA.4784@TK2MSFTNGP04.phx.gbl... > Dang. That was better than sex. >> Dang. That was better than sex. I don't understand. The programming? Or the sex?> > Are you sure you're doing it right then? -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "y = (-b ± (b^2 - 4 * a * c)^.5) / 2 * a" -- Dr. Houser "Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message The sex. Programming can be good, but if it's better than sex...news:%23dOrnnysGHA.5044@TK2MSFTNGP05.phx.gbl... >>> Dang. That was better than sex. >> >> Are you sure you're doing it right then? > > I don't understand. The programming? Or the sex? >>>> Dang. That was better than sex. Then it's really fantastic eloquently well phrased highly efficient well >>> >>> Are you sure you're doing it right then? >> >> I don't understand. The programming? Or the sex? > > The sex. Programming can be good, but if it's better than sex... architected tiny fast code that gives me goose-bumps just thinking about it. I think I need to goto the bathroom! -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "y = (-b ± (b^2 - 4 * a * c)^.5) / 2 * a" -- Dr. Houser Something like this should point you in the right direction:
USE Northwind GO SELECT OrderDate , [Hour] = datepart( hour, OrderDate ) , count(1) FROM Orders GROUP BY OrderDate , datepart( hour, OrderDate ) And of course, if you need to restrict the date range, add WHERE criteria between the FROM and GROUP BY clauses. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Lontae Jones" <LontaeJo***@discussions.microsoft.com> wrote in message news:08BBBD1C-C288-4A63-828D-8C1F8CD9C3F5@microsoft.com... > Hello, > > I have a table with 3 columns > > ProductID int, Product varchar(30), OrderDate datetime > > I have orders in this table from January to July I want to get a query that > will display how many orders I am getting per day and per hour. > Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. You talk about orders, then post your personal pseudo-code about products. Does that make sense to you? >> I have orders in this table from January to July I want to get a query that will display how many orders I am getting per day and per hour. << Build a table of hourly report ranges. Think about using a spreadsheetor a simple program to give you a year's worth of ranges -- 365 * 24 = 8760 rows CREATE TABLE ReportRanges (period_name CHAR(15) NOT NULL PRIMARY KEY, -- or use start_date start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, CHECK (start_date < end_date), etc.) ; Then the query is simply: SELECT R.period_name, COUNT(*) AS hourly_cnt FROM ReportRanges AS R, Orders AS O WHERE O.order_date BETWEEN R.start_date AND R.end_date GROUP BY R.period_name; Joe,
I have tried this approach several times on SQL Server, and you know what? It consistently performs much (at least 5 times) slower that what Hugo posted (datediff followed by a dateadd)... On Thu, 27 Jul 2006 15:56:02 -0700, Lontae Jones wrote:
>Hello, Hi Lontae,> >I have a table with 3 columns > >ProductID int, Product varchar(30), OrderDate datetime > >I have orders in this table from January to July I want to get a query that >will display how many orders I am getting per day and per hour. For "per day": SELECT DATEADD(day, DATEDIFF(day, '20000101', OrderDate), '20000101') AS OrderDay, COUNT(*) AS OrderCount FROM YourTable GROUP BY DATEDIFF(day, '20000101', OrderDate); For "per hour": SELECT DATEADD(hour, DATEDIFF(hour, '20000101', OrderDate), '20000101') AS OrderHour, COUNT(*) AS OrderCount FROM YourTable GROUP BY DATEDIFF(hour, '20000101', OrderDate); I guess you can work out other intervals for yourself :-) BTW, the '20000101' can be replaced by any other date - just make sure that you don't overflow the integer range (only really an issue if you use "second" or "ms" in your DATEDIFF). -- Hugo Kornelis, SQL Server MVP I love You Joe!!
Show quote "Hugo Kornelis" wrote: > On Thu, 27 Jul 2006 15:56:02 -0700, Lontae Jones wrote: > > >Hello, > > > >I have a table with 3 columns > > > >ProductID int, Product varchar(30), OrderDate datetime > > > >I have orders in this table from January to July I want to get a query that > >will display how many orders I am getting per day and per hour. > > Hi Lontae, > > For "per day": > > SELECT DATEADD(day, DATEDIFF(day, '20000101', OrderDate), '20000101') > AS OrderDay, COUNT(*) AS OrderCount > FROM YourTable > GROUP BY DATEDIFF(day, '20000101', OrderDate); > > For "per hour": > > SELECT DATEADD(hour, DATEDIFF(hour, '20000101', OrderDate), > '20000101') AS OrderHour, COUNT(*) AS OrderCount > FROM YourTable > GROUP BY DATEDIFF(hour, '20000101', OrderDate); > > I guess you can work out other intervals for yourself :-) > > BTW, the '20000101' can be replaced by any other date - just make sure > that you don't overflow the integer range (only really an issue if you > use "second" or "ms" in your DATEDIFF). > > -- > Hugo Kornelis, SQL Server MVP > >I love You Joe!! So you're telling Hugo that you love Joe? Won't Hugo be jealous?-- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "y = (-b ± (b^2 - 4 * a * c)^.5) / 2 * a" -- Dr. Houser > I have orders in this table from January to July I want to get a query Ugly.> that > will display how many orders I am getting per day and per hour. Off the top of my head, that sounds like a SELECT that has a COUNT(stuff) FROM xyz GROUP BY otherstuff WITH ROLLUP Or WITH CUBE Whatever suits you. But the ROLLUP or CUBE expresion will have to be based on the DATEDIFF(.....) of your OrderDate column (or worse). As a "Dev guy" (I'm not a DBA), I will warn you in advance: The WITH CUBE and WITH ROLLUP operators that can accompany a GROUP BY clause will do bizarre things to the front-side application that's trying to use it. Such as returning multiple ADODB.Recordset objects or some other heirarchichal nonsense that is dang near impossible for us dev-people to work with. Although I think the fastest answer to your question lies in the direction of the CUBE or ROLLUP keywords, I can promise you that if you use either of them in a stored procedure, your front-side developes will hate you. Just my two [irrelevant] cents? -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "y = (-b ± (b^2 - 4 * a * c)^.5) / 2 * a" -- Dr. Houser  |
|||||||||||||||||||||||
Other interesting topics