display data in report grouped by datetime

Hello,

I have the following situation :

I have a reportmodel where i have the a datetime value (dd/mm/yyyy hh:mms), a salespersonid , a salespersondescription, and an amountsold.

i would like to display a report where i can see per hour (start worktime is 8 am and end is 8 pm) which salesperson sells the most. the objectif of this report is to see in what period of the day the sales are highest.

Can anybody provide me some tips how to accomplish this.

Vinnie

You might be able to take advantage of the DATEPART function to group your sales figures. I'm not sure I have the correct visualization of your report, but you might be able to start with a query like:

Code Snippet

declare @.reportModel table
( salesPersonId integer,
salesPersonDescription varchar(20),
amountSold decimal (9,2),
salesDateTime datetime
)
insert into @.reportModel
select 1, 'The Boss', 29.95, '6/15/7 7:45' union all
select 1, 'The Boss', 10.95, '6/15/7 9:21' union all
select 1, 'The Boss', 149.99, '6/15/7 10:15' union all
select 2, 'Salesman #2', 17.45, '6/15/7 8:05' union all
select 2, 'Salesman #2', 21.25, '6/15/7 8:15' union all
select 2, 'Salesman #2', 79.99, '6/15/7 8:59' union all
select 2, 'Salesman #2', 9.95, '6/15/7 9:09' union all
select 2, 'Salesman #2', 19.95, '6/15/7 9:21' union all
select 2, 'Salesman #2', 21.45, '6/15/7 10:29'
--select * from @.reportModel

select datepart (hh, salesDateTime) as HourOfDay,
cast(sum (amountSold) as decimal(15,2)) as HourlySalesTotal,
count(*) as numberOfSales,
salesPersonId,
salesPersonDescription
from @.reportModel
group by datepart (hh, salesDateTime),
salesPersonDescription,
salesPersonId

which is capable of delivering results such as:

Code Snippet

HourOfDay HourlySalesTotal numberOfSales salesPersonId salesPersonDescription
-- -- - - -
7 29.95 1 1 The Boss
8 118.69 3 2 Salesman #2
9 29.90 2 2 Salesman #2
9 10.95 1 1 The Boss
10 21.45 1 2 Salesman #2
10 149.99 1 1 The Boss