Jump to content

Need help Please


kahunabee

Recommended Posts

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...