Tricky Logic Using Group by - SQL Server Forums

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.

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

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

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.

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.

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

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]

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.

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources