CODEWITHSUNDEEP
sql-serverdateaverage
ARr0w
ARr0w

Reputation: 1731

Get the average Hour period from a selected Date range

I have a order table which happens to have a column of DataType "Datetime" which contains Date and Time of the arrival of product to the Company(customer) and i have multiple type of users their types are like: Employee, CompanyOwners(customers),Dispatchers..etc

I have managed to get the date range of orders against to the specific user who is the owner of the company using the following T-SQL query below:

declare @username varchar(130), @DateFrom varchar(10), @DateTo varchar(10)
set @username = 'chabow';set @DateFrom='4/01/2018';set @DateTo='6/30/2018'
;with cte as (
    select count(o.ArrivalDate) as orders_count, cast(o.ArrivalDate as Date) as ArrivalDate
    from view_MembershipUsers msu
    inner join XrefCompanyUsers rfu on  msu.UserName = rfu.UserName
    inner join Company f on f.CompanyID = rfu.CompanyID
    inner join orders o on o.CompanyID = f.CompanyID
    where msu.UserName = @username and
    (o.ArrivalDate >= @DateFrom and  o.ArrivalDate <= @DateTo)
    group  by cast(o.ArrivalDate as Date)
    )
    select *,
    (select max(orders_count)+2 from  cte) as Total
    from cte    
    order by ArrivalDate

What i actually need to retrieve is the Average hour of arrival of product along with the data i have retrieved using above query from selected date range. I am clueless to how i can get the average in hours from this date range without getting the date difference, because i need to read "date" of every row to get the most possible accurate average hour.

I have extracted some sample dates for ease in order for you guys to help me.

create table SelectedData
(ArrivalDate date, ActualDateTime datetime)
insert into SelectedData values
('2018-04-01','2018-04-01 20:45:00.000'),
('2018-04-04','2018-04-04 19:00:00.000'),
('2018-04-05','2018-04-05 14:00:00.000'),
('2018-04-05','2018-04-05 14:23:00.000'),
('2018-04-05','2018-04-05 18:30:00.000'),
('2018-04-06','2018-04-06 12:30:00.000'),
('2018-04-06','2018-04-06 18:08:00.000')

declare @DateFrom varchar(10), @DateTo varchar(10)
    set @DateFrom='4/01/2018';set @DateTo='6/30/2018'

    select *
    from SelectedData   
    order by ArrivalDate

Thanks and regards.

Upvotes: 5

Views: 113

Answers (1)

WynDiesel
WynDiesel

Reputation: 1214

Change your type to DateTime, and not date. Like this :

drop table SelectedData
create table SelectedData
(ArrivalDate date, ActualDateTime datetime)
insert into SelectedData values
('2018-04-01','2018-04-01 20:45:00.000'),
('2018-04-04','2018-04-04 19:00:00.000'),
('2018-04-05','2018-04-05 14:00:00.000'),
('2018-04-05','2018-04-05 14:23:00.000'),
('2018-04-05','2018-04-05 18:30:00.000'),
('2018-04-06','2018-04-06 12:30:00.000'),
('2018-04-06','2018-04-06 18:08:00.000')

declare @DateFrom varchar(10), @DateTo varchar(10)
set @DateFrom='4/01/2018';set @DateTo='6/30/2018'

select avg(datePart(hour, ActualDateTime))
from SelectedData   
--order by ArrivalDate

And then get the datepart of hours from the date, and then the average of that.

Upvotes: 3

Related Questions