Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
grimmus
Yak Posting Veteran
53 Posts |
 Posted - 2011-10-24 : 09:57:32
|
Hi,I am trying to write a query that will check what category a hospital visitor falls under. There are 3 categories (infrequent, regular, frequent). I seem to be getting confused with the different selection criteria for each group and the date comparison.Visitors can be categorised as 'infrequent' if they fall into one of the following groups : - They have a status of newcomer (1) - They have not made any hospital visits ever OR their only visit was for less than 10 minutess in the last 18 months. - Their last visit was for less than 5 minutes - Their last 2 visits were both less than 10 minutesVisitors can be categorised as 'regular' if they fall into the following group : - visitors who have not made 3 visit's of more than 10 mins in their last 3 visits and have one visit of less than 5 minutes in their last 5 visit times, with all 5 visits having been done in the last 12 months. - Experts with no visits of less than 10 minutes in their last 5 visits, with all 5 visits having been performed in the last 12 months and at least one in the last 3 months.Visitors can be categorised as 'frequent' if they match none of the criteria above OR fall into one of the following groups : - Visitors whose last 3 Visits were all 10 mins or more, with all 3 visits having been performed in last 12 months and at least one in the last 3 months.TABLESVisitor-------VisitorID (PK)VisitorStatus (int) (1=newcomer,2=standard)Visits--------------VisitID (PK)VisitorID (FK)VisitDateVisitDuration (minutes)The results should update the table below with the correct category based on the selection criteriaVisitorCategory---------------VisitorcategoryID (PK)visitorID (FK)VisitorCategory (int) (1=infrequent,2=regular,3=frequent)Could anyone help point me in the right direction with this ? I seem to get more confused when i read over the requirements again.  Thanks so much |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:15:18
|
| seems like an assignment question. we will help you provided you've at least made a try can you post what you tried yet? unless you try it out yourself, dont expect any help from us as you wont gain anything from it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
grimmus
Yak Posting Veteran
53 Posts |
Posted - 2011-10-24 : 11:34:43
|
| It's actually a task for work, but i understand your point about me at least trying to solve it.I'll reply again in a few hours with my attempts so far.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:39:25
|
quote:
Originally posted by grimmus
It's actually a task for work, but i understand your point about me at least trying to solve it.I'll reply again in a few hours with my attempts so far.Thanks
Thanks for the spirit shown  you'll definitely gain a lot by this attitude------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Complex query for hospital visitors