Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using Gropy by with Tricky Logic Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 8:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 97, Visits: 333
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.
Post #1563044
Posted Friday, April 18, 2014 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 97, Visits: 333
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.
Post #1563130
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse