This is my updated Logic
declare @dt date,@UserCompanyId int = 100
declare @year char(4) = '2014',
@month char(2) = '03'
set @dt = @year + '-' + @month + '-01'
;with data as (
Select L.PageName,UC.CompanyName,Datediff(ms,PageStartDate,PageEndDate)/1000 [diff],DATENAME(month,PageStartDate)[Month] from #Response L join #User U
on(L.IdUser= U.IdUser) join #UserCompany UC on(U.UserCompanyId= UC.UserCompanyId)
where PageStartDate > @dt and PageStartDate < dateadd(mm, 1, @dt)
and PageEndDate > @dt and PageEndDate < dateadd(mm, 1, @dt)
and Uc.UserCompanyId = @UserCompanyId
--where YEAR(PageStartDate) = @year and YEAR(PageEndDate) = @year and MONTH(PageStartDate) = @month and Uc.UserCompanyId = @UserCompanyId
)
SELECT distinct PageName, ,[0-1] = SUM(CASE WHEN diff BETWEEN 0 AND 1 THEN 1 ELSE 0 END)
,[1-2] = SUM(CASE WHEN diff BETWEEN 1 AND 2 THEN 1 ELSE 0 END)
,[2-3] = SUM(CASE WHEN diff BETWEEN 2 AND 3 THEN 1 ELSE 0 END)
,[3-4] = SUM(CASE WHEN diff BETWEEN 3 AND 4 THEN 1 ELSE 0 END)
,[4-5] = SUM(CASE WHEN diff BETWEEN 4 AND 5 THEN 1 ELSE 0 END)
,[5-6] = SUM(CASE WHEN diff BETWEEN 5 AND 6 THEN 1 ELSE 0 END)
,[6-7] = SUM(CASE WHEN diff BETWEEN 6 AND 7 THEN 1 ELSE 0 END)
,[7+] = SUM(CASE WHEN diff >= 7 THEN 1 Else 0 END),CompanyName,MONTH from data D
group by PageName,D.CompanyName,d.Month order by Month,CompanyName
Any comments or suggestions please because i will be dealing this logic with 500000+ records.