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

    Unanswered: exempt participants

    Hello I have a report that I created on the matrix that shows all the active particpants with their hours and the total. But I was told that I need to have Exempt particpants on the report also whether they have hours or not. I am unsure how to get exempts on the report since they have no hours. I'm working with two tables one is the people_tbl and the Tanfactivity_tbl. I need the Active and the Exempt Employees to show even if they dont have any hours and I am unsure as how to do that, can anyone help pls



    Code:
    SELECT     People_tbl.[Parent ID], People_tbl.[Student Last Name], People_tbl.[Student First Name], TanfActivity_tbl.EventDate, TanfActivity_tbl.[Earned hours], 
                          TanfActivity_tbl.[Catagory for hours], TanfActivity_tbl.[State Catagory], TanfActivity_tbl.[Services Covered], TanfActivity_tbl.[State Services Covered], 
                          People_tbl.[Referral Date], People_tbl.ReferralStatus, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Month, People_tbl.Weekly
    FROM         People_tbl INNER JOIN
                          TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID]
    WHERE     (People_tbl.ReferralStatus IN (N'Active', N'Exempt')) AND (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) AND 
                          (TanfActivity_tbl.[Earned hours] IS NOT NULL) AND (TanfActivity_tbl.[Catagory for hours] IN (N'ITP', N'Parenting', N'Education', N'Culture', 
                          N'Job Skills Training', N'Life Skills', N'Travel')) AND (People_tbl.TANF = N'Yes') AND (People_tbl.Adult_Child = N'Adult')
    ORDER BY CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Parenting' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Job Skills Training' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Culture' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Education' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Life Skills' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Travel' THEN 0 ELSE 1 END, People_tbl.[Student Last Name], People_tbl.[Student First Name]

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       People_tbl.[Parent ID], People_tbl.[Student Last Name]
    ,  People_tbl.[Student First Name], TanfActivity_tbl.EventDate
    ,  TanfActivity_tbl.[Earned hours], TanfActivity_tbl.[Catagory for hours]
    ,  TanfActivity_tbl.[State Catagory], TanfActivity_tbl.[Services Covered]
    ,  TanfActivity_tbl.[State Services Covered], People_tbl.[Referral Date]
    ,  People_tbl.ReferralStatus, People_tbl.TANF
    ,  People_tbl.Adult_Child, People_tbl.[Month]
    ,  People_tbl.Weekly
       FROM People_tbl
       INNER JOIN TanfActivity_tbl
          ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID]
       WHERE  ( N'Exempt' = People_tbl.ReferralStatus)
          OR  (N'Active'  = People_tbl.ReferralStatus
             AND (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) 
             AND (TanfActivity_tbl.[Earned hours] IS NOT NULL) 
             AND (TanfActivity_tbl.[Catagory for hours] IN (N'ITP', N'Parenting'
    ,           N'Education', N'Culture'
    ,           N'Job Skills Training', N'Life Skills', N'Travel')) 
             AND (People_tbl.TANF = N'Yes') 
             AND (People_tbl.Adult_Child = N'Adult'))
       ORDER BY
          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Parenting' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Job Skills Training' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Culture' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Education' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Life Skills' THEN 0 ELSE 1 END
    ,     CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Travel' THEN 0 ELSE 1 END
    ,     People_tbl.[Student Last Name]
    ,     People_tbl.[Student First Name]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    The only problem Is the participants that the exempts are showing but so are their hours from when they werent exempt and they want that to be blank. They dont want to see the hours they just want to see their name on the report. Does that make sense, other wise when you run the report for 05/01/2010 to 05/31/2010 you see all their activities (hours) for when they were active and I need that to be blank for exempts. Does that make sense??

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there a way to use the tables to determine when they are exempt and when they are non-exempt? If that information (when they change from exempt to non-exempt and back) isn't in the tables, then there isn't any way to report it.

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

Posting Permissions

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