Grouping age with parameter

have a big table in which I have DOB, I have to extract age from DOB first then I have to group them into different groups.
I think I can extract age from the table. But the problem is in this grouping part.
So I created a simple table with a field Age , having values from 1 to 100.
I a trying to group that table according to the users need. That is: i have a parameter in the report called The count for grouping,they can give count from 1 to 10.
Consider @Count as the parameter for count.
What I am planning to do is to find out the grouping first and I have to find out each records from my big table that fit in this group..
Is that clear..??
I tried to make a group with your command , but it is not giving me the proper values for @count=2 and @count=3.because it is giving the group like:
1 1
2 3
4 5
etc for the @count=2.
I am confused whether I can use this to find out the records from the big table. I have to find out total score of the that agegroup and display in the report like:
age gorup totalscore
1 -2 25( what ever value from the table after checking)
3-4 30
5-6 10
etc...

I have doubt that you can get the result in single select query...
Not sure but is this you are looking for.



Declare @Stud Table ( Rno int, Age int )
Insert Into @Stud Values ( 1, 10 )
Insert Into @Stud Values ( 2, 11 )
Insert Into @Stud Values ( 3, 8 )
Insert Into @Stud Values ( 4, 5 )
Insert Into @Stud Values ( 5, 25 )
Insert Into @Stud Values ( 6, 33 )
Insert Into @Stud Values ( 7, 27 )
Insert Into @Stud Values ( 8, 10 )
Insert Into @Stud Values ( 9, 4 )
Insert Into @Stud Values ( 10, 7 )

Declare @Result Table ( AgeGrp Varchar(100), AgeCount int )

Declare @FromAge int, @ToAge int
Declare @Count int

Set @Count = 3
Set @FromAge = 1
Set @ToAge = 1

WHILE(@ToAge <= 100 )
BEGIN
Set @ToAge = @FromAge + @Count - 1

INSERT @Result
Select Convert(Varchar,@FromAge) + ' - ' + Convert(Varchar,@ToAge) ,
Count(1) From @Stud Where Age Between @FromAge AND @ToAge

Set @FromAge = @ToAge + 1

END
Select * From @Result





Knowledge is not Power !!!
But When its acted upon then its Power !!!

Hi,
Actually I am doing this in Reporting services and I need the @FromAge and @ToAge values to check with my actual table. But your query is not the only one I am looking for.
I can do the same with a select statement like:
select Min(Age),Max(Age) from tblAge group by Floor(Age/@Count)
where @Count is the interval for the grouping.But that is not working fine when the count is 2 0r three.
It is giving correct group when I do it with your query,thanks ....
But my question, How can I get the other values , like scores, from my big table according to this grouping???
Can I do all of these in a single procedure ??

thanks..

Can you post your table structure with some sample data and desired result ?<br /><br />BTW you can do all the things in single Procedure. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br /><br />Knowledge is not Power !!!<br />But When its acted upon then its Power !!!

Is this what you are looking for?

Declare @Stud Table ( Rno int, Age int )
Insert Into @Stud Values ( 1, 10 )
Insert Into @Stud Values ( 2, 11 )
Insert Into @Stud Values ( 3, 8 )
Insert Into @Stud Values ( 4, 5 )
Insert Into @Stud Values ( 5, 25 )
Insert Into @Stud Values ( 6, 33 )
Insert Into @Stud Values ( 7, 27 )
Insert Into @Stud Values ( 8, 10 )
Insert Into @Stud Values ( 9, 4 )
Insert Into @Stud Values ( 10, 7 )

DECLARE @interval int
SET @interval = 10

SELECT (Age / @interval * @interval) as [From], (Age / @interval)* @interval + @interval -1 as [To] , Count(*) As [Count]
FROM @Stud
GROUP BY (Age / @interval * @interval), (Age / @interval)* @interval + @interval -1

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

My goal is to change the given stored procedure so that I can find out the different age gorup according to users parameter and find out sumof these values for that group:
s.TVmins, s.Notional$, COUNT(*) AS Qty, SUM(s.TVmins) AS TVMinsAmt, SUM(s.Notional$) AS NotionalAmt
For that I am planning to put another parameter @count for the group interval and I need to group accordingly.
So my answer should look like:
if the user gave the @count value as 10:
the result should:

age group TVMins Notional

1-9 1560 125632( the sum of that particluar group)
10-19 -- --
---
91-100 -- ---
-----------------------------------------------
ALTER PROCEDURE [dbo].[sp_PlanningData]
@ProgrammeID numeric,
@RegionID numeric,
@SiteID numeric,
@COCGroup varchar(50),
@Provider varchar(50),
@Schedule varchar(50),
@StartDate datetime,
@EndDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@sqlnvarchar(4000),
@paramlistnvarchar(4000)

SELECT @sql = 'SELECT
dm.DOB,
dm.Suburb,
vs.RID,
s.TVmins,
s.Notional$,
COUNT(*) AS Qty,
SUM(s.TVmins) AS TVMinsAmt,
SUM(s.Notional$) AS NotionalAmt

FROM dbo.lkpService s
INNER JOIN dbo.tmpValidServices_ALL vs ON s.Code = vs.Service
INNER JOIN dbo.tmpDemographics_ALL dm ON dm.RID = vs.RID '

IF @COCGroup IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON vs.COC = c.pvcode '

IF @ProgrammeID IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '

SELECT @sql = @sql + 'WHERE s.Schedule = @Schedule '

IF @StartDate IS NOT NULL
SELECT @sql = @sql + ' AND (vs.Complete >= @StartDate ) '

IF @EndDate IS NOT NULL
SELECT @sql = @sql + ' AND (vs.Complete <= @EndDate ) '

IF @ProgrammeID IS NOT NULL
SELECT @sql = @sql + ' AND (ag.AgencyTypeID = @ProgrammeID)'

IF @SiteID IS NOT NULL
SELECT @sql = @sql + 'AND (ag.EXACT# = @SiteID) '

IF @COCGroup IS NOT NULL
SELECT @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '

IF @Provider IS NOT NULL
SELECT @sql = @sql + 'AND (vs.Provider = @Provider) '

SELECT @sql = @sql + 'GROUP dm.Suburb,vs.RID, s.TVmins, s.Notional$ '

SELECT @paramlist =
'@ProgrammeIDnumeric,
@RegionIDnumeric,
@SiteIDnumeric,
@COCGroupvarchar(50),
@Providervarchar(50),
@Schedule varchar(50),
@StartDatedatetime,
@EndDatedatetime '

EXEC sp_executesql @sql,@paramlist,@ProgrammeID,@RegionID,@SiteID,@COCGroup,@Provider,@Schedule,@StartDate,@EndDate

END
-------------------------------------------------------



Hope this will help.. it is really urgent one.. I am trying my best to find it out..
Thanks for your help..

FYI....
The solution for this can be viewed at :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=694652&SiteID=1&mode=1

Thank you for your response...