SQL Server Performance

Grouping age with parameter

Discussion in 'SQL Server 2005 General Developer Questions' started by bisjom, Aug 24, 2006.

  1. bisjom New Member

    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...
  2. patel_mayur New Member

    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 !!!
  3. bisjom New Member

    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..
  4. patel_mayur New Member

    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 !!!
  5. Roji. P. Thomas New Member

    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
  6. bisjom New Member

    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..

  7. bisjom New Member

Share This Page