Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Twelve totals in one query

    Hi everyone I have to create a report on reporting services that will give me 12 totals. One total has to do with a total number of tanf clients and it should be 45 but I keep getting 18, and the total number of One parent Families but I keep getting and Two-Parent Families which should be 42. then on the same report I haveto get the kids ages, Like the total kids that are age 0-3 and kids that are 4-5 and kids ta are k-12. Can anyone help with this please. I hope that made some kind of sense? All of this has to show on one report



    Code:
    SELECT     People_tbl.[Parent ID], People_tbl.[Student First Name] AS [Parent First Name], People_tbl.[Student Last Name] AS [Parent Last Name], 
    
                          People_tbl.[Referral Date], Student_ind.[Student First Name] AS [First Name], Student_ind.[Student Last Name] AS [Last Name], Student_ind.Adult_Child,
    
                           Student_ind.Age, People_tbl.ReferralStatus, People_tbl.TANF, People_tbl.State, People_tbl.TypeOfParticipant
    
    FROM         Student_ind RIGHT OUTER JOIN
    
                          People_tbl ON Student_ind.[Parent ID] = People_tbl.[Parent ID]
    
    WHERE     (People_tbl.ReferralStatus = N'Active') AND (People_tbl.TANF = N'Yes') AND (People_tbl.State = N'yes') AND 
    
                          (People_tbl.TypeOfParticipant IN (N'Single Parent w/child 1 to 6 yrs', N'Single Parent w/child 6 yrs & up', N'Two-Parent Family'))

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I did a basic clean up of the query to get:
    Code:
    SELECT
       People_tbl.[Parent ID]
    ,  People_tbl.[Student First Name] AS [Parent First Name]
    ,  People_tbl.[Student Last Name] AS [Parent Last Name]
    ,  People_tbl.[Referral Date]
    ,  Student_ind.[Student First Name] AS [First Name]
    ,  Student_ind.[Student Last Name] AS [Last Name]
    ,  Student_ind.Adult_Child
    ,  Student_ind.Age
    ,  People_tbl.ReferralStatus
    ,  People_tbl.TANF
    ,  People_tbl.State
    ,  People_tbl.TypeOfParticipant
       FROM Student_ind
       RIGHT OUTER JOIN People_tbl
          ON Student_ind.[Parent ID] = People_tbl.[Parent ID]
       WHERE  (People_tbl.ReferralStatus = N'Active')
          AND (People_tbl.TANF = N'Yes')
          AND (People_tbl.State = N'yes')
          AND (People_tbl.TypeOfParticipant IN (
             N'Single Parent w/child 1 to 6 yrs'
    ,        N'Single Parent w/child 6 yrs & up'
    ,        N'Two-Parent Family'))
    Now I need to start unravelling how the pieces fit together, but that will have to come later!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is absolutely pure SWAG at this point, but try:
    Code:
    SELECT COUNT(*) AS {total_rows]
    ,  Count(CASE WHEN (N'Yes' = People_tbl.TANF) THEN 1 END) AS TANF
    ,  Count(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single%') THEN 1 END) AS [Single]
    ,  Count(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Two%') THEN 1 END) AS [Two]
    ,  Count(CASE WHEN (Student_ind.Age <= 3) THEN 1 END) AS [Child 1-3]
       FROM Student_ind
       RIGHT OUTER JOIN People_tbl
          ON (Student_ind.[Parent ID] = People_tbl.[Parent ID])
       WHERE  (People_tbl.ReferralStatus = N'Active')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Pat Phelan View Post
    This is absolutely pure SWAG at this point, but try:
    Code:
    SELECT COUNT(*) AS [total_rows]
    ,  Count(CASE WHEN (N'Yes' = People_tbl.TANF) THEN 1 END) AS TANF
    ,  Count(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single%') THEN 1 END) AS [Single]
    ,  Count(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Two%') THEN 1 END) AS [Two]
    ,  Count(CASE WHEN (Student_ind.Age <= 3) THEN 1 END) AS [Child 1-3]
       FROM Student_ind
       RIGHT OUTER JOIN People_tbl
          ON (Student_ind.[Parent ID] = People_tbl.[Parent ID])
       WHERE  (People_tbl.ReferralStatus = N'Active')
    -PatP
    THANK YOU SO MUCH PAT..This is what I was looking for. Apparently the People_tbl and the Student_ind table do not have a relationship. Also I the total is 58 when it should be 45 for the total rows and the total for TANF. Thank you again Pat this helped allot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •