How to get orders from database foreach hour

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Hi,
I don't know how to get orders foreach hour how many orders are placed in a perticualar date.I mean for example

Date=2012-08-07,
6AM=20,7AM=30,8AM=40,9AM=0(if no orders are placed at 9AM),.......up to 20PM like.

please give me one sample query.

Thanks&Regards,
Raghu.

Posted 7-Aug-12 19:10pm

Madhugundi

Add a Solution

Comments

Kenneth Haugland 8-Aug-12 1:11am

Use Date time < and > and you should have it...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2012-08-07T19:53:00

Solution 1

suppose Order_tbl have some data as given below

CSS

Name   orderdt                   ordertm   
-----------------------------------------------------
aaaa   2012-08-08 00:00:00.000   2012-08-08 10:54:15.053
bbbb   2012-08-08 00:00:00.000   2012-08-08 10:54:17.270
cccc   2012-08-08 00:00:00.000   2012-08-08 10:54:18.727
dddd   2012-08-08 00:00:00.000   2012-08-08 10:54:19.117
eeee   2012-08-08 00:00:00.000   2012-08-08 11:54:21.000
ffff   2012-08-08 00:00:00.000   2012-08-08 11:55:21.000
gggg   2012-08-08 00:00:00.000   2012-08-08 10:55:21.663
hhhh   2012-08-08 00:00:00.000   2012-08-08 10:55:21.957

now, check below query for this table

SQL

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end  as hours,
count(*) as Total_orders from Order_tbl
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

result for date '2012-08-08'

CSS

Hours   Total_Orders
-------------------------
10 AM	6
11 AM	2

Happy Coding!
:)

Posted 7-Aug-12 19:53pm

Aarti Meswania

Updated 7-Aug-12 20:36pm

v2

Comments

Madhugundi 8-Aug-12 2:55am

hi,
i executed my query based on your query but i am getting zero rows.
my query look like is
DECLARE @Sql NVARCHAR(300);
SET @Sql ='select case when cast(SUBSTRING(ShipDate,11, 3)as varchar)>12 then cast(SUBSTRING(ShipDate,11, 3)as varchar)-12 else cast(SUBSTRING(ShipDate,11, 3)as varchar) end as hours,
count(Id) as Total_orders from ShipmentDetail where (ShipDate)=''2012-07-26''group by cast(SUBSTRING(ShipDate,11, 3)as varchar)' ;

EXEC(@Sql);
print @Sql;

Aarti Meswania 8-Aug-12 3:09am

shipdate & shiptime should be in different columns.
you can not compare date-time field to date only.
try this,

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end as hours,
count(*) as Total_orders from
(select id,convert(Datetime, CONVERT(VARCHAR(10),ordertm,105),105) as shipdate) as Orderdt, shipdt as orderTm from ShipmentDetail ) as a
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

Madhugundi 8-Aug-12 3:45am

HI,
If u don't mind give me more clarity on this
in my table shipdate is in nvarchar type does here datepart() will work if it is how to convert nvarchart to date to get hour and also your query will written for each hour or only the orders existing hours.i am trying to get orders for every hour if orders are not there on a paticular hour it will return 0 on result.

please help me,

Thanks&Regards,
Raghu.

Aarti Meswania 8-Aug-12 3:47am

please copy paste any value of shipdate field.

Madhugundi 8-Aug-12 3:51am

ShipDate Column Value is like: 2012-07-25 15:54:32 PDT

Aarti Meswania 8-Aug-12 3:58am

you should take shipdate in datetime format
and if you are taking it as nvarchar then it's format should be yyyy-MM-dd HH:mm:ss
eg. '2012-25-07 15:54:32' not '2012-07-25 15:54:32'
It will create difficulty when ever you will perform comparision with this column

Madhugundi 8-Aug-12 4:01am

without changing the shipdate type nvarchar to datetime is not possible?

Aarti Meswania 8-Aug-12 4:07am

yes but you have to perform to much task
change this '2012-07-25 15:54:32' to
'2012-25-07 15:54:32' using substring
and then use convert(Datetime,'2012-25-07 15:54:32',105)

Madhugundi 8-Aug-12 4:23am

Plese help me how to replace '2012-07-25 15:54:32' to
'2012-25-07 15:54:32' using substring ?
Thanks for you spent your valuable time.

Aarti Meswania 8-Aug-12 4:29am

try to do your self if any problem then tell me. I have given you the right suggestion just use date/time field

Madhugundi 8-Aug-12 5:23am

Thank you

Aarti Meswania 8-Aug-12 5:24am

you got solution?

Madhugundi 8-Aug-12 5:32am

No

Madhugundi 8-Aug-12 5:32am

Hi,

Is it possible by using DatePart() method to get hours from datetime where date is in nvarchar.
I am trying to get that but getting an error like:
Msg 241, Level 16, State 1, Line 14
Conversion failed when converting date and/or time from character string.

the sample query is:

Declare @Hours Table (Hour int);

Declare @i as int;
Set @i = 0
While @i < 24
Begin
Insert into @Hours
Values(@i);

Set @i = @i + 1;
End;

Select
[Hour of Day],
Count([Total Calls]) as total
From
(
SELECT
DATEPART(HOUR, convert(Datetime,ShipDate,13)) AS [Hour of Day]
, Id AS [Total Calls]
FROM ShipmentDetail
Union ALL
SELECT Hour, NULL
From @Hours
) as Data

GROUP BY [Hour of Day]
ORDER BY [Hour of Day]

please modify the query.

Thanks&Regards,
Raghu.

Aarti Meswania 8-Aug-12 5:46am

if you have not perform any changes in table
means you have datatype nvarchar and format yyyy-dd-MM hh:mm:ss then,
run query given below, you will get output

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end as hours,
count(*) as Total_orders from
(
select id,convert(Datetime, CONVERT(VARCHAR(10),Shipdate,105),105) as shipdate) as Orderdt, shipdate as orderTm from
(select id,convert(Datetime,(substring(shipdate,1,5) + substring(shipdate,6,2) + substring(shipdate,8,3) + substring(shipdate,11,9)),105) as shipdate from ShipmentDetail) as a
) as a
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

Madhugundi 8-Aug-12 5:35am

if i use substring it am getting result but i am getting problem where 0,1,2,3,4,5,6,7,8,9 because i am extracting hours like,01,02,03,04,05,06,07,08,09

Madhugundi 8-Aug-12 6:03am

while executing
select convert(datetime, (substring(ShipDate,1,5)+ substring(ShipDate,6,2) + substring(ShipDate,8,3) + substring(ShipDate,11,9)),105) as shipdate from ShipmentDetail this i am getting an error is like:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Aarti Meswania 8-Aug-12 6:07am

put a shipdate value and test eg.
shipdate='2012-25-07 15:54:32'

select convert(Datetime,(substring('2012-25-07 15:54:32',1,5) + substring('2012-25-07 15:54:32',6,2) + substring('2012-25-07 15:54:32',8,3) + substring('2012-25-07 15:54:32',11,9)),105)

Aarti Meswania 8-Aug-12 9:29am

run this Query

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end as hours,
count(*) as Total_orders from
(
select id,convert(Datetime, CONVERT(VARCHAR(10),Shipdate,105),105) as shipdate) as Orderdt, shipdate as orderTm from
(select id,convert(Datetime,(substring(shipdate,1,5) + substring(shipdate,9,2) + substring(shipdate,5,3) + substring(shipdate,11,9)),105) as shipdate from ShipmentDetail) as a
) as a
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

Madhugundi 9-Aug-12 1:54am

Hi i got the solution but i am not able get on a perticular date i.e
query like:
where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE ''' +2012-07-25 + '%''
plese check my where condition did i place single quptes are correct i am getting error is
The data types varchar and varchar are incompatible in the modulo operator.
Why i written like is i want to check on a perticular date from user on CreatedDateTime contains 2012-07-25.
I think is little bit configue to you but try to understand my problem and please place the single quotes in correct,
Thanking you.

Aarti Meswania 9-Aug-12 1:59am

what is it 'CreatedDateTime', this field wasn't in your table,isn't it. dear you have solution,
have you run this Query I posted, before?
I had solved it as per your table structure and details.

select case when datepart(hour,ordertm)>12 then convert(varchar,datepart(hour,ordertm)-12) +' PM' else convert(varchar,datepart(hour,ordertm)) +' AM' end as hours,
count(*) as Total_orders from
(
select id,convert(Datetime, CONVERT(VARCHAR(10),Shipdate,105),105) as shipdate) as Orderdt, shipdate as orderTm from
(select id,convert(Datetime,(substring(shipdate,1,5) + substring(shipdate,9,2) + substring(shipdate,5,3) + substring(shipdate,11,9)),105) as shipdate from ShipmentDetail) as a
) as a
where (orderdt)='2012-08-08' group by datepart(hour,ordertm)

Madhugundi 9-Aug-12 2:23am

sorry i am new for this database someone as worked on shipdate instead of CreatedDateTime. So I didn't see that column.
Lastly i got solution by using one of proffessional post but i am not able write where condition on that post please check this:

Declare @Hours Table (Hour int);
Declare @i as int;
Set @i = 5
While @i < 24
Begin
Insert into @Hours
Values(@i);

Set @i = @i + 1;
End;

Select
[Hour of Day],
Count([TotalShipOrders]) as Total
From
(
SELECT
DATEPART(HOUR, convert(Datetime,CreatedDateTime,13)) AS [Hour of Day]
, Id AS [TotalShipOrders]
FROM ShipmentDetail where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE +2012-07-25 + '%'
Union ALL
SELECT Hour, NULL
From @Hours
) as Data

GROUP BY [Hour of Day]
ORDER BY [Hour of Day]

i want the where condition is like
where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE '2012-07-25 %'

2012-07-25 it is passed by user from Application.
CreatedDateTime is like 2012-07-25 15:54:32.843

Plese help how to place single quotes.

Aarti Meswania 9-Aug-12 2:36am

It's really strange I have post total solution of query and you are not accepting that and working on a false way. any way it's your matter,
solution is
LIKE '2012-07-25 %'

Madhugundi 9-Aug-12 3:01am

it returns zero rows for every hour
ok in your post what is ordertm i have not any column like.

Aarti Meswania 9-Aug-12 3:06am

send your create table script and 2-3 rows, I will tell you. and see proper way shiptm is derived field I have separate date value name it shipdate & time value name it shiptm.

Madhugundi 9-Aug-12 3:12am

sorry i cant send script

Aarti Meswania 9-Aug-12 3:16am

Ok hope you will solve your self and if you are not getting error in my query then it's working , for each hour you are getting count()=0 then solve that problem, you are still struggling with date when it's solved,isn't it strange?
any way good luck!
:)