April 18, 2014 at 8:18 am
Hi,
Below is my Sample table, Data
create table #User(IdUser int primary key identity(1,1),UserCompanyId int,username varchar(50));
insert into #User values(100,'Jhon'),(100,'Dean'),(101,'Smith'),(103,'Billy');
create table #UserCompany(UserCompanyId int primary key identity(100,1),CompanyName varchar(50));
insert into #UserCompany values('Dominos'),('PizzaHut'),('Papachinos'),('McD');
Create table #Response(IdResponse int primary key identity(1,1),IdUser int,PageName varchar(30),PageStartDate datetime,PageEndDate datetime);
Insert into #Response values(1,'Login','Mar 26 2014 6:18PM','Mar 26 2014 6:18PM'),
(1,'InBound Report','2014-03-26 18:18:26.967','2014-03-26 18:18:30.040'),
(1,'OutBound Report','2014-03-26 18:19:56.873','2014-03-26 18:19:57.077'),
(1,'Invoice Report','2014-03-26 18:22:09.990','2014-03-26 18:22:10.770'),
(1,'Monthly Report','2014-03-26 18:22:23.423','2014-03-26 18:22:28.587'),
(2,'InBound Report','2014-03-26 18:24:18.257','2014-03-26 18:24:18.770'),
(2,'OutBound Report','2014-03-26 18:24:22.780','2014-03-26 18:24:23.840'),
(2,'Invoice Report','2014-03-26 18:26:13.813','2014-03-26 18:26:14.967'),
(1,'InBound Report','2014-02-26 18:18:26.967','2014-02-26 18:18:30.040'),
(1,'OutBound Report','2014-02-26 18:19:56.873','2014-02-26 18:19:57.077'),
(1,'Invoice Report','2014-02-26 18:22:09.990','2014-02-26 18:22:10.770'),
(1,'Monthly Report','2014-02-26 18:22:23.423','2014-02-26 18:22:28.587'),
(2,'InBound Report','2014-02-26 18:24:18.257','2014-02-26 18:24:18.770'),
(2,'OutBound Report','2014-02-26 18:24:22.780','2014-02-26 18:24:23.840'),
(2,'Invoice Report','2014-02-26 18:26:13.813','2014-02-26 18:26:14.967'),
(2,'Monthly Report','2014-02-26 18:26:24.810','2014-02-26 18:26:25.387'),
(3,'Login','Mar 26 2014 6:18PM','Mar 26 2014 6:18PM'),
(3,'InBound Report','2014-03-26 18:18:26.967','2014-03-26 18:18:30.040'),
(3,'OutBound Report','2014-03-26 18:19:56.873','2014-03-26 18:19:57.077'),
(3,'Invoice Report','2014-03-26 18:22:09.990','2014-03-26 18:22:10.770'),
(3,'Monthly Report','2014-03-26 18:22:23.423','2014-03-26 18:22:28.587'),
(3,'InBound Report','2014-03-26 18:24:18.257','2014-03-26 18:24:18.770'),
(3,'OutBound Report','2014-03-26 18:24:22.780','2014-03-26 18:24:23.840'),
(3,'Invoice Report','2014-03-26 18:26:13.813','2014-03-26 18:26:14.967');
SQL logic
;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)
)
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
If i do pass the parameters for filter, here is my logic
Declare @year int = 2014, @month int = 3, @UserCompanyId int = 100
;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 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
I am storing the pageload and unload date and time for calcualting how much time it takes.
Requirement:
Step1: Get the time time difference in ms and convert into seconds and sum count falls in time range
0-1 seconds
1-2 seconds
2-3 seconds
3-4 seconds
5-6 seconds
6-7 seconds
>= 7 seconds
order by month and IDUser
condition : i can pass the date and CompanyName as Paramenter to filter the data based on monthwise and companyName wise report
The above query works fine. Just wondering is there any better way to achieve this. i am a learning guy and would like to better way always because Unknown is Ocean.
Thanks in Advance to help on this post.
April 18, 2014 at 12:18 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply