sql dense rank

I am trying to come up with a rank with grouping.

For example,
My Data looks like this
cnt2 	sessionKey	officeStateKey	sessionUnitKey	          sessionStart	                                           sessionEnd
1	            156211	             2	                       172958	           2015-09-07 13:55:00.000	          2015-09-07 14:55:00.000
2	            156211	             2	                        172959	            2015-09-07 15:55:00.000 	2015-09-07 17:55:00.000
3	            156211	             2	                          172960	              2015-09-09 13:55:00.000	  2015-09-09 14:55:00.000
4	            156211	             2	                        172961	              2015-09-09 15:55:00.000	  2015-09-09 17:55:00.000
5	            156211	             2	                         172962	           2015-09-11 15:55:00.000	           2015-09-11 17:55:00.000
5	            156211	             2	                         172963	           2015-09-11 15:55:00.000	           2015-09-11 17:55:00.000

Open in new window

What I wan to get is the following:  SO basically I need to count pairs grouped by sessionID and sessionStart. The same sessionId grouped with the pair on the same date
cnt2 	sessionKey	officeStateKey	sessionUnitKey	          sessionStart	                                           sessionEnd
1	            156211	             2	                       172958	           2015-09-07 13:55:00.000	          2015-09-07 14:55:00.000
2                  156211	             2	                        172959	            2015-09-07 15:55:00.000 	2015-09-07 17:55:00.000
1	            156211	             2	                          172960	              2015-09-09 13:55:00.000	  2015-09-09 14:55:00.000
2	            156211	             2	                        172961	              2015-09-09 15:55:00.000	  2015-09-09 17:55:00.000
1	            156211	             2	                         172962	           2015-09-11 15:55:00.000	           2015-09-11 17:55:00.000
2	            156211	             2	                         172963	           2015-09-11 15:55:00.000	           2015-09-11 17:55:00.000

Open in new window


And the query looks like this
SELECT 
DENSE_RANK() OVER (ORDER BY T.sessionKey,T.sessionStart) as cnt2

,T.sessionKey , T.officeStateKey , T.sessionUnitKey , T.sessionStart , T.sessionEnd 
 
   
   FROM Session T 

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SO basically I need to count pairs grouped by sessionID and sessionStart.
Give this a whirl..

SELECT DENSE_RANK() OVER (PARTITION BY sessionID ORDER BY T.sessionKey,T.sessionStart) as cnt2, T.sessionKey , T.officeStateKey , T.sessionUnitKey , T.sessionStart , T.sessionEnd
FROM Session T
0
erikTsomikSystem Architect, CF programmer Author Commented:
when I add partition I get the following . My cn2 does not get reset. On the row 3 the cnt2 should start again with 1. Because we are on the same session but it is a different date 9-9-2015 than 9-7-2015

cnt2	sessionKey	officeStateKey	sessionUnitKey	sessionStart	sessionEnd
1	156211	2	172958	2015-09-07 13:55:00.000	2015-09-07 14:55:00.000
2	156211	2	172959	2015-09-07 15:55:00.000	2015-09-07 17:55:00.000
3	156211	2	172960	2015-09-09 13:55:00.000	2015-09-09 14:55:00.000
4	156211	2	172961	2015-09-09 15:55:00.000	2015-09-09 17:55:00.000
5	156211	2	172962	2015-09-11 15:55:00.000	2015-09-11 17:55:00.000
5	156211	2	172963	2015-09-11 15:55:00.000	2015-09-11 17:55:00.000

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
I got it

,ROW_NUMBER() over(PARTITION BY T.sessionKey,
                         CONVERT(NVARCHAR(25), t.sessionStart, 111)
                         ORDER BY t.sessionStart ASC) as cnt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
yes partitioning by session and date (not date & time) should do it

for the purposes of that partition it isn't necessary to use NVARCHAR or 25 characters however

as a general rule, try to avoid pushing date/time data into strings if you can, it is always better for performance to keep them as date/time types, here are some alternatives:

dateadd(day, datediff(day,0, t.sessionStart ), 0) -- any sql server version

cast(t.sessionStart as date) -- sql 2008 onward

e.g.
,ROW_NUMBER() over(PARTITION BY T.sessionKey,
                         dateadd(day, datediff(day,0, t.sessionStart ), 0)
                         ORDER BY t.sessionStart ASC) as cnt
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
erikTsomik, do you still need help with this question?
0
erikTsomikSystem Architect, CF programmer Author Commented:
As I commented out my solution works for me
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.