question

helal avatar image
helal asked

Calculate number of groups and group size with multiple criteria

My data looks like this: ![alt text][1] I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1 The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two. Any suggestions? Thank You, Helal [1]: /storage/temp/2504-groupsize.png
sql-server-2008-r2partition
groupsize.png (20.3 KiB)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I can help you and write query for this example, but... There is to many special cases and it is impossible to write query to cover all your special cases...
0 Likes 0 ·
You mention you have already tried using a PARTITION..OVER query - why don't you show us what you have tried so far
0 Likes 0 ·
I believe i mentioned in above that in the first temp table (Temp1) I have: select GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the next temp table (Temp2) Select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.