SQLTeam.com | Weblogs | Forums

Data grouping based on time hour

Hi,
Below is sample data. Would like to group data on hourly basis and based on company

declare @callcentertable table(callid int, companyname varchar(50), custid int,callcenteruser varchar(50),isFinished bit,createdtime datetime)
insert into @callcentertable values(101,'ABC Limited',500,'Max',1,'2019-07-03 08:15:05')
insert into @callcentertable values(101,'ABC Limited',501,'Max',0,'2019-07-03 08:30:18')
insert into @callcentertable values(101,'ABC Limited',502,'Max',1,'2019-07-03 09:10:05')
insert into @callcentertable values(101,'XYZ Limited',503,'Max',0,'2019-07-03 09:40:20')

select * from @callcentertable

---------Expected result -------
Time CompanyName Contacted NotContacted User
8-9 AM ABC Limited 1 1 Max
9-10 AM ABC Limited 1 0 Max
9-10 AM XYZ Limited 0 1 Max

please expound on above columns. never mind I get now

This will get you started - there are other ways to calculate the hour category, for example - using a tally/numbers table or inline tally table.

Declare @callcentertable Table(callid int
      , CompanyName varchar(50)
      , CustID int
      , CallCenterUser varchar(50)
      , isFinished bit
      , CreatedTime datetime);

 Insert Into @callcentertable 
 Values (101, 'ABC Limited', 500, 'Max', 1, '2019-07-03 08:15:05')
      , (101, 'ABC Limited', 501, 'Max', 0, '2019-07-03 08:30:18')
      , (101, 'ABC Limited', 502, 'Max', 1, '2019-07-03 09:10:05')
      , (101, 'XYZ Limited', 503, 'Max', 0, '2019-07-03 09:40:20');

   With hourCategories
     As (
 Select *
   From (
 Values ( 0, '00-01AM')
      , ( 1, '01-02AM')
      , ( 2, '02-03AM')
      , ( 3, '03-04AM')
      , ( 4, '04-05AM')
      , ( 5, '05-06AM')
      , ( 6, '06-07AM')
      , ( 7, '07-08AM')
      , ( 8, '08-09AM')
      , ( 9, '09-10AM')
      , (10, '10-11AM')
      , (11, '11-12AM')
      , (12, '12-01PM')
      , (13, '01-02PM')
      , (14, '02-03PM')
      , (15, '03-04PM')
      , (16, '04-05PM')
      , (17, '05-06PM')
      , (18, '06-07PM')
      , (19, '07-08PM')
      , (20, '08-09PM')
      , (21, '09-10PM')
      , (22, '10-11PM')
      , (23, '11-12AM')
        ) As c(hour, category)
        )
 Select ContactDate = cast(CreatedTime As date)
      , ContactHour = hc.category
      , CompanyName
      , Contacted = sum(iif(isFinished = 1, 1, 0))
      , NotContacted = sum(iif(isFinished = 0, 1, 0))
      , [User] = max(CallCenterUser)
   From @callcentertable                cc
  Inner Join hourCategories             hc On hc.hour = datepart(hour, cc.CreatedTime)
  Group By
        cast(CreatedTime As date)
      , hc.category
      , CompanyName;
1 Like