Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Tricky Logic Using Group by

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-18 : 11:40:07
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 >=0 AND diff <2 THEN 1 ELSE 0 END)
,[1-2] = SUM(CASE WHEN diff >1 AND diff <3 THEN 1 ELSE 0 END)
,[2-3] = SUM(CASE WHEN diff >2 AND diff <4 THEN 1 ELSE 0 END)
,[3-4] = SUM(CASE WHEN diff >3 AND diff <5 THEN 1 ELSE 0 END)
,[4-5] = SUM(CASE WHEN diff > 4 aND diff <6 THEN 1 ELSE 0 END)
,[5-6] = SUM(CASE WHEN diff > 5 aND diff <7 THEN 1 ELSE 0 END)
,[6-7] = SUM(CASE WHEN diff > 6 aND diff <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 >=0 AND diff <2 THEN 1 ELSE 0 END)
,[1-2] = SUM(CASE WHEN diff >1 AND diff <3 THEN 1 ELSE 0 END)
,[2-3] = SUM(CASE WHEN diff >2 AND diff <4 THEN 1 ELSE 0 END)
,[3-4] = SUM(CASE WHEN diff >3 AND diff <5 THEN 1 ELSE 0 END)
,[4-5] = SUM(CASE WHEN diff > 4 aND diff <6 THEN 1 ELSE 0 END)
,[5-6] = SUM(CASE WHEN diff > 5 aND diff <7 THEN 1 ELSE 0 END)
,[6-7] = SUM(CASE WHEN diff > 6 aND diff <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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-18 : 11:53:03
I don't get the range break out [1-2], [2-3], etc. as it is really just [2], [3], etc. I'm not sure if that is a logic issue or not. For example: [2-3] = SUM(CASE WHEN diff >2 AND diff <4 THEN 1 ELSE 0 END) -- Will only get values for 3.

Other than that it seems to work just fine. I don't know how much data you are dealing with or what indexes you have. But, you might be able to gain some performance by converting your Year/Month into a Start Date and End Date and use that in your query. Then the optimizer could make use of an index on date, if one exists.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-18 : 12:05:17
couple other miner points/observations for performance:
- the DISTINCT is not buying you anything you should remove it.
- consider moving the #user and #userCompany joins out of the CTE and into the final query
- consider moving the aggregation into the cte

Be One with the Optimizer
TG
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-18 : 14:29:20
Hi Lampery,

Here is the 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 PageName, [0-1] = SUM(CASE WHEN diff >=0 AND diff <=1 THEN 1 ELSE 0 END)
,[1-2] = SUM(CASE WHEN diff >1 AND diff <=2 THEN 1 ELSE 0 END)
,[2-3] = SUM(CASE WHEN diff >2 AND diff <=3 THEN 1 ELSE 0 END)
,[3-4] = SUM(CASE WHEN diff >3 AND diff <=4 THEN 1 ELSE 0 END)
,[4-5] = SUM(CASE WHEN diff >4 aND diff <=5 THEN 1 ELSE 0 END)
,[5-6] = SUM(CASE WHEN diff >5 aND diff <=6 THEN 1 ELSE 0 END)
,[6-7] = SUM(CASE WHEN diff >6 aND diff <=7 THEN 1 ELSE 0 END)
,[7+] = SUM(CASE WHEN diff >= 7 THEN 1 ENd),CompanyName,MONTH from data D

group by PageName,D.CompanyName,d.Month order by Month,CompanyName


Any comments or sugestion


Hi TG,

I removed the Distinct.

Here is the modified code as per your suggestions.

declare @dt date,@UserCompanyId int = 100
declare @year char(4) = '2014',
@month char(2) = '03'

set @dt = @year + '-' + @month + '-01'

;with data as (
Select PageName,Datediff(ms,PageStartDate,PageEndDate)/1000 [diff],DATENAME(month,PageStartDate)[Month],IdUser from #Response
where PageStartDate > @dt and PageStartDate < dateadd(mm, 1, @dt)
and PageEndDate > @dt and PageEndDate < dateadd(mm, 1, @dt)
)

SELECT PageName, [0-1] = SUM(CASE WHEN diff >=0 AND diff <=1 THEN 1 ELSE 0 END)
,[1-2] = SUM(CASE WHEN diff >1 AND diff <=2 THEN 1 ELSE 0 END)
,[2-3] = SUM(CASE WHEN diff >2 AND diff <=3 THEN 1 ELSE 0 END)
,[3-4] = SUM(CASE WHEN diff >3 AND diff <=4 THEN 1 ELSE 0 END)
,[4-5] = SUM(CASE WHEN diff >4 aND diff <=5 THEN 1 ELSE 0 END)
,[5-6] = SUM(CASE WHEN diff >5 aND diff <=6 THEN 1 ELSE 0 END)
,[6-7] = SUM(CASE WHEN diff >6 aND diff <=7 THEN 1 ELSE 0 END)
,[7+] = SUM(CASE WHEN diff >= 7 THEN 1 ENd),UC.CompanyName from data D

join #User U on(D.IdUser= U.IdUser) join #UserCompany UC on(U.UserCompanyId= UC.UserCompanyId)

WHERE UC.UserCompanyId = @UserCompanyId

group by D.PageName,UC.CompanyName,d.Month order by Month,CompanyName


Any suggestions or comments please
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-18 : 15:01:55
You didn't really change anything did you, from a logic perspective?

,[1-2] = SUM(CASE WHEN diff >1 AND diff <3 THEN 1 ELSE 0 END)
is logically the same as:
,[1-2] = SUM(CASE WHEN diff >1 AND diff <=2 THEN 1 ELSE 0 END)

It can only be 2, it can never be 1. That may not matter, I just wasn't sure what the goal was.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-18 : 15:38:00
Hi Lampery,

The goal is, i want to segregate the page load time as 0 to 1 seconds, 1-2 seconds, 2-3 seconds, 3-4 seconds, 4-5 seconds, 5-6 seconds,6-7 seconds and 7+ seconds.

can you please post the improved query or your logic for my requirement will be great for me understand.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-18 : 17:03:03
Probably he's talking about this. If you look at these results then don't seem to match your logic:

select PageName
,datediff(ms, pagestartdate, pageenddate)/1000 as DIFF
,datediff(ms, pagestartdate, pageenddate) as DiffMS
from #Response l
join #User u on u.IDUser = l.IDUser
where YEAR(PageStartDate) = 2014
and YEAR(PageEndDate) = 2014
and MONTH(PageStartDate) = 3
and u.UserCompanyId = 100
order by pagename

OUTPUT:
PageName DIFF DiffMS
------------------------------ ----------- -----------
InBound Report 3 3073
InBound Report 0 513
Invoice Report 1 1153
Invoice Report 0 780
Login 0 0
Monthly Report 5 5163
OutBound Report 0 203
OutBound Report 1 1060

for the InBound Report there is one that is 513ms (0-1 seconds), and there is one that is 3073ms (3-4 seconds).
But your output shows 1 in [0-1] and 1 in [2-3].

Be One with the Optimizer
TG
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-18 : 17:09:57
[code]

SELECT
PageName, [0-1], [1-2], [2-3], [3-4], [4-5], [5-6], [6-7], [7+],
CompanyName, DATENAME(MONTH, [Month#]) AS [Month]
FROM (
SELECT
L.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),
UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0) AS [Month#]
FROM #Response L
INNER JOIN #User U on (L.IdUser= U.IdUser)
INNER JOIN #UserCompany UC on (U.UserCompanyId= UC.UserCompanyId)
CROSS APPLY (
SELECT Datediff(ms,PageStartDate,PageEndDate)/1000 AS [diff]
) AS ca1
WHERE
L.PageStartDate >= DATEADD(MONTH, @month - 1, CAST(CAST(@year AS CHAR(4)) + '0101' AS datetime)) AND
L.PageStartDate < DATEADD(MONTH, @month, CAST(CAST(@year AS CHAR(4)) + '0101' AS datetime)) AND
UC.UserCompanyId = @UserCompanyId
GROUP BY
UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0), L.PageName
--PageName, UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0)
) AS derived
ORDER BY
CompanyName, [Month#]

[/code]
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-18 : 19:02:49
Hi TG,

Thanks for your time on this post. Appreciate
Probably i should have to use between instead > and <=.

Hi Scot,

Thanks for your time on this post. Appreciate
Excellent and the logic works great. Would like to understand before i signoff from this post. what is the use of cross apply on your logic.

Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-19 : 01:00:23
The CROSS APPLY is used just to assign a name to "Diff" and be able to use that name in the calcs instead of using the full expression.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-20 : 16:25:09
Thanks Scott. I learnt from web about the use of cross and outer apply.
Go to Top of Page
   

- Advertisement -