SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using Gropy by with Tricky Logic


using Gropy by with Tricky Logic

Author
Message
KGJ-Dev
KGJ-Dev
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 933
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.
KGJ-Dev
KGJ-Dev
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 933
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search