kahunabee Posted March 3, 2015 Share Posted March 3, 2015 I have a table that has the following fields: Visit_ID CPCCID Date Time_IN Time-Out Course LA_CPCCID there are 3 sessions, 9-12, 12-3 and 3-6 I need script that will calculate which session has the most visitors. I have this attached code that will determine the session # and the max count select Time_In , CASE When cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' /* and date = cast(GETDATE() as date)*/ then 'Session 2' when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' /*and date = cast(GETDATE() as date)*/ then 'Session 3' else 'Session 1' end "sessions" from Lab_Visits2; select max(visit.cnt) from (select course, count(course) cnt from Lab_Visits2 group by Course) visit; Link to comment Share on other sites More sharing options...
justsomeguy Posted March 3, 2015 Share Posted March 3, 2015 It sounds like you need to combine those queries, and group the count by the session name instead of the course column. Link to comment Share on other sites More sharing options...
kahunabee Posted March 3, 2015 Author Share Posted March 3, 2015 I do not know how to do that. I got some help and got this with cte as ( select case when cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' and Visit_Date = cast(GETDATE() as date)then 'Session 2' when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' and Visit_Date = cast(GETDATE() as date) then 'Session 3' else 'Session 1' end session from Lab_Visits2 ), ctecnt as ( select session, count(*) cnt from cte group by session ) select session, (cnt) from ( select session, cnt, row_number() over (order by cnt desc) rn from ctecnt ) t where rn = 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now