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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 Data DudeCommented:
>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
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

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

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
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

PortletPaulEE Topic AdvisorCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
erikTsomik, do you still need help with this question?
erikTsomikSystem Architect, CF programmer Author Commented:
As I commented out my solution works for me
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.