Solved

setup age groups in a table

Posted on 2014-04-23
4
158 Views
Last Modified: 2014-04-24
I am putting together an activities table that will correspond relationally from another table.   I am trying to figure out how to label predetermined age groups and all I can think of is to put them in the following columns.  Since there can be more than one age group, for each activity, I figure setup 5 different columns for the age groups.  Do you see any concerns doing it this way?

SELECT TOP 1000 [ID]
      ,[Activity Name]
      ,[Activity Desc]
      ,[Activity Outcome]
      ,[Location]
      ,[To]
      ,[From]
      ,[0 to 7]
      ,[8 to 12]
      ,[13 to 17]
      ,[18 to 20]
      ,[21 and over]
      ,[PIID]
  FROM [CAPRegistration].[dbo].[tblOrgActivities]

Open in new window

0
Comment
Question by:al4629740
  • 2
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40018882
Generally age is always stored in a single column, and the fancy T-SQL is reserved for reports.

In your case, with those ranges, you could try this..

SELECT activity_name, case
   when age <= 7 then 'Ages 0-7'
   when age <= 12 then 'Ages 8 to 12'
   when age <= 17 then 'Ages 13 to 17'
   when age <= 20 then 'Ages 18 to 20'
   else '21 and over' end as age_ranges
FROM tblOrgActivities

I have an article called SQL Server CASE Solutionsthat illustrates this, and other CASE solutions.

>Since there can be more than one age group
You'll have to give some more detail as to how this works.
0
 

Author Comment

by:al4629740
ID: 40018910
I have a form in Visual Basic where you check off your age groups for the activity.  Since you can have more than one age group you can check off 0 to 7 and 8 to 12 for that activity.
0
 

Author Comment

by:al4629740
ID: 40018914
I would need to put an age range in and not an age
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40018918
In that case, choices are...
*  The ranges as you have them in the form.  Probably the easiest choice.
*  Have the 'Option Group' a single value, and in your form handle writing  multiple values into an single value.  Handy, but also a lot of UI work.
*  Having a 'one to many' table to contain the age range selections.  Might not be that useful.

The first one is by far the easiest.  The problem lies if someone wants to change up the ranges that they're hard-coded.   Also, make sure there is a textbox that stores the current date, as two years from now these age ranges will mature, for lack of better word..

Good luck.
Jim
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now