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

    Red face Unanswered: Needs totals from two unrelated tables

    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

    This involves Two tables that arent really related. The people_tbl and the Student_tbl

    People_tbl

    Code:
    CREATE TABLE [dbo].[People_tbl](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[StudentID] [nvarchar](50) NULL,
    	[Referral Location] [nvarchar](50) NULL,
    	[Referral Date] [nvarchar](50) NULL,
    	[Registration Date] [nvarchar](50) NULL,
    	[Student First Name] [nvarchar](50) NULL,
    	[Student Last Name] [nvarchar](50) NULL,
    	[Student SS#] [nvarchar](50) NULL,
    	[Student Sex] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [nvarchar](50) NULL,
    	[Student Date of Birth] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANFreferral] [nvarchar](255) NULL,
    	[Enrollmentstatus] [nvarchar](255) NULL,
    	[EmailAddress] [nvarchar](100) NULL,
    	[ScannedDocuments] [nvarchar](50) NULL,
    	[ParentRelease] [nvarchar](50) NULL,
    	[StudentRelease] [nvarchar](50) NULL,
    	[DateReleased] [nvarchar](50) NULL,
    	[StudentAddress] [nvarchar](50) NULL,
    	[StudentZip] [nvarchar](50) NULL,
    	[StudentState] [nvarchar](50) NULL CONSTRAINT [DF_People_tbl_StudentState]  DEFAULT (N'CA'),
    	[StudentCity] [nvarchar](50) NULL,
    	[TribalAfflilation] [nvarchar](50) NULL,
    	[StateStudentID] [int] NULL,
    	[Phone] [nvarchar](50) NULL,
    	[Message] [nvarchar](50) NULL,
    	[TanfStaffMakeReferal] [nvarchar](50) NULL,
    	[TypeOfParticipant] [nvarchar](50) NULL,
    	[RequiredHours] [nvarchar](50) NULL,
    	[SpecialInstructions] [nvarchar](50) NULL,
    	[PntID] [int] NULL,
    	[Adult_Child] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[SSID] [nvarchar](50) NULL,
    	[PeopleID] [int] IDENTITY(1,1) NOT NULL,
    	[TANF] [nvarchar](50) NULL,
    	[ServiceArea] [nvarchar](50) NULL,
    	[Weekly] [int] NULL,
    	[Month] [int] NULL,
    	[ReferralStatus] [nvarchar](50) NULL,
    	[MaritalStatus] [nvarchar](50) NULL,
    	[Vetran] [nvarchar](50) NULL,
    	[TanfSpouseName] [nvarchar](50) NULL,
    	[TanfSpouseSocial] [nvarchar](50) NULL,
    	[ReferralDate] [datetime] NULL,
    	[FamilyHrID] [int] NULL,
    	[Pictures] [image] NULL,
    	[ScairCaseWorker] [nvarchar](50) NULL,
    	[ScairHoursOnly] [bit] NULL CONSTRAINT [DF_People_tbl_ScairHoursOnly]  DEFAULT ('0'),
    	[FamilyCodes] [nvarchar](50) NULL,
    	[StateParticipant] [nvarchar](50) NULL,
    	[StateActive_Inactive] [nvarchar](50) NULL,
    	[Counselor] [nvarchar](50) NULL,
    	[TtlCountableMnths] [nvarchar](50) NULL,
    	[Manzanita] [nvarchar](50) NULL,
     CONSTRAINT [PK_People_tbl] PRIMARY KEY CLUSTERED 
    (
    	[Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    student_tbl

    Code:
      CREATE TABLE [dbo].[Student_ind](
    	[Student ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[Referral Location] [nvarchar](50) NULL,
    	[Referral Date] [datetime] NULL,
    	[Registration Date] [datetime] NULL,
    	[Student First Name] [nvarchar](50) NULL,
    	[Student Last Name] [nvarchar](50) NULL,
    	[Student SS#] [nvarchar](50) NULL,
    	[Student Sex] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[Student Date of Birth] [datetime] NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANFreferral] [nvarchar](255) NULL,
    	[Enrollmentstatus] [nvarchar](255) NULL,
    	[EmailAddress] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_EmailAddress]  DEFAULT (N'Email Address'),
    	[ScannedDocuments] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_ScannedDocuments]  DEFAULT (N'Scanned Documents'),
    	[Culture] [bit] NULL,
    	[Education] [bit] NULL,
    	[YouthDevelopment] [bit] NULL,
    	[Sports_Fitness] [bit] NULL,
    	[Technology] [bit] NULL,
    	[ParentRelease] [nvarchar](50) NULL,
    	[StudentRelease] [nvarchar](50) NULL,
    	[DateReleased] [datetime] NULL,
    	[StudentAddress] [nvarchar](50) NULL,
    	[StudentZip] [nvarchar](50) NULL,
    	[StudentState] [nvarchar](50) NULL,
    	[StudentCity] [nvarchar](50) NULL,
    	[TribalAfflilation] [nvarchar](50) NULL,
    	[StateStudentID] [int] IDENTITY(1,1) NOT NULL,
    	[Parent1] [nvarchar](50) NULL,
    	[Parent2] [nvarchar](50) NULL,
    	[PeopleID] [int] NULL,
    	[Phone] [nvarchar](50) NULL,
    	[Message] [nvarchar](50) NULL,
    	[TanfStaffMakeReferal] [nvarchar](50) NULL,
    	[TypeOfParticipant] [nvarchar](50) NULL,
    	[RequiredHours] [nvarchar](50) NULL,
    	[SpecialInstructions] [nvarchar](50) NULL,
    	[PntID] [int] NULL,
    	[Adult_Child] [nvarchar](50) NULL,
    	[Ste_Tanf] [nvarchar](50) NULL,
    	[SSID] [nvarchar](50) NULL,
    	[TANF] [nvarchar](50) NULL,
    	[StudentsActivity] [nvarchar](50) NULL,
    	[Active_Inactive] [nvarchar](50) NULL,
    	[Manzanita] [nvarchar](50) NULL,
     CONSTRAINT [PK_Student_ind] PRIMARY KEY CLUSTERED 
    (
    	[Student ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER VIEW [dbo].[MonthlyReport_view]
    AS
    SELECT     COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 1 to 6 yrs') THEN 1 END) AS Single, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE 'Single Parent w/child 6 yrs & up') THEN 1 END) AS [Single Parent W/child 6 yrs & up], 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Two-Parent Family%') THEN 1 END) AS Two, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Non-Needy%') THEN 1 END) AS [Non-Needy], 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Disregarded%') THEN 1 END) AS Disregarded, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Exempt%') THEN 1 END) AS Exempt, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Counseling%') THEN 1 END) AS Counseling, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Other%') THEN 1 END) AS Other, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Active%') THEN 1 END) AS Active
    FROM         dbo.People_tbl
    WHERE     (ReferralStatus <> N'Inactive')
    GROUP BY TANF
    HAVING      (TANF = N'Yes')

  2. #2
    Join Date
    Sep 2010
    Posts
    1
    For the unrelated tables you may want to try subqueries. I apologize for not clearly understanding where the unrelated information is being pulled from. This example may help you understand in theory what needs to happen.


    Code:
    SELECT     COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 1 to 6 yrs') THEN 1 END) AS Single,
    //Start Subquery
    (Select Count(Children) from UnrelatedTable where Age Between 0 and 3) as ChildrenCount0To3
    //End Subquery
    FROM         dbo.People_tbl
    WHERE     (ReferralStatus <> N'Inactive')
    GROUP BY TANF
    HAVING      (TANF = N'Yes')

    Notice the line:
    (Select Count(Children) from UnrelatedTable where Age Between 0 and 3) as ChildrenCount0To3

    That is the subquery syntax to pull the count of children between the ages of 0 and 3. Of course you need to change UnrelatedTable to your table name and your field name to Count(YourFieldName).

    I hope this helps let me know if you need more clarification.

Posting Permissions

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