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

    Unanswered: One report for 10 centers

    I have a question, I have about 10 centers that are going to be using our database and the structure of the tables is the same accept for the primary key, and I am wondering if they will have an impact on the report. the reason why i ask this is becasue I have a report that reflects all the centers activities for the state. I was wonder if anyone had any suggestions. I'm doing this in Reporting Services. this is the select statement I am using. I was wondering since the primary keys are differnent would that have an impact on calculating of the report. Would that matter or something, its just a question I had because I wasnt sure. One database has a Nvchar as a primary key (natuarl key) the other has a Indentity key (INT). would that have an impact on the reporing?? Cause one of the groups is by the ParentID. That is still in the other databases its just not the primary key, but it is Indexed



    Code:
    FROM         School_tbl INNER JOIN
                          People_tbl ON School_tbl.PeopleID = People_tbl.PeopleID INNER JOIN
                          StateCategory_tbl ON People_tbl.PeopleID = StateCategory_tbl.PeopleID INNER JOIN
                          StateServices_tbl ON People_tbl.PeopleID = StateServices_tbl.PeopleID INNER JOIN
                          ParticipantActivity ON People_tbl.PeopleID = ParticipantActivity.PeopleID
    WHERE     (School_tbl.schoolID IS NOT NULL) AND (People_tbl.Adult_Child = N'Child') AND (StateCategory_tbl.StateCategory IN (N'Academic Support', 
                          N'Self Concept', N'Counseling')) AND (ParticipantActivity.[Activity Date] BETWEEN @Beginning_ActivityDate AND @End_ActivityDate)
    ORDER BY CASE WHEN StateCategory_tbl.[StateCategory] = N'Academic Support' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'In - Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'One-on-One Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Small Group Hours (up to 3) Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Large Group Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Self Directed - Academic Service' THEN 0 ELSE 1 END, 
                          CASE WHEN StateCategory_tbl.[StateCategory] = N'Self Concept' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Youth Leadership Hrs' THEN 0 ELSE 1 END, 
                          CASE WHEN StateCategory_tbl.[StateCategory] = N'Counseling' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Vocational Planning Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Vocational Planning Hours' THEN 0 ELSE 1 END
    Last edited by desireemm; 10-16-09 at 16:37.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Data type conversions do impact efficiency of the resulting execution plan. In some cases SEEK gets replaced with INDEX SCAN, in others the index may be completely ignored, resulting in TABLE/CLUSTERED INDEX SCAN.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok so should I keep the Primary key the same in all databaes?? this is the select statement from the first and SEcond Center (second Database). since they dont have the same primary key will that mess up the reports?? The primary keys in reporting Servcies are just used for the first grouping set, it doesnt show in the report


    First Database SCAIR

    Code:
    SELECT     Student_ind.[Student ID], ParticipantActivity.[Activity Date], School_tbl.[Studentschool Id], School_tbl.schoolID, ParticipantActivity.[Total Time], 
                          ParticipantActivity.Services, ParticipantActivity.Activity, Student_ind.SSID, ParticipantActivity.[State Category], ParticipantActivity.[State Services]
    FROM         ParticipantActivity INNER JOIN
                          School_tbl INNER JOIN
                          Student_ind ON School_tbl.[Student ID] = Student_ind.[Student ID] ON ParticipantActivity.[Student ID] = Student_ind.[Student ID]
    WHERE     (ParticipantActivity.[Activity Date] BETWEEN @Beginning_ActivityDate AND @End_ActivityDate) AND (School_tbl.[Studentschool Id] IS NOT NULL) AND 
                          (School_tbl.schoolID IS NOT NULL) AND (ParticipantActivity.[State Category] IN (N'Academic Support', N'Self Concept', N'Counseling'))
    ORDER BY ParticipantActivity.[Activity Date], CASE WHEN ParticipantActivity.[State Category] = N'Academic Support' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'In-Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'One-on-One Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Small Group Tutoring Hrs' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Large Group Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Self Directed - Academic Service' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Category] = N'Self Concept' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Youth Leadership Hrs' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Category] = N'Counseling' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Individual Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Individual Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Individual Vocational Planning Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Group Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Group Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN ParticipantActivity.[State Services] = N'Group Vocational Planning Hours' THEN 0 ELSE 1 END
    Database Name American Indian Center
    Code:
    SELECT     School_tbl.schoolID, People_tbl.FirstName, People_tbl.LastName, People_tbl.Adult_Child, StateServices_tbl.StateServices, 
                          StateCategory_tbl.StateCategory, People_tbl.PeopleID, ParticipantActivity.[Activity Date], People_tbl.[Parent ID], People_tbl.SSID, 
                          ParticipantActivity.[Total Time]
    FROM         School_tbl INNER JOIN
                          People_tbl ON School_tbl.PeopleID = People_tbl.PeopleID INNER JOIN
                          StateCategory_tbl ON People_tbl.PeopleID = StateCategory_tbl.PeopleID INNER JOIN
                          StateServices_tbl ON People_tbl.PeopleID = StateServices_tbl.PeopleID INNER JOIN
                          ParticipantActivity ON People_tbl.PeopleID = ParticipantActivity.PeopleID
    WHERE     (School_tbl.schoolID IS NOT NULL) AND (People_tbl.Adult_Child = N'Child') AND (StateCategory_tbl.StateCategory IN (N'Academic Support', 
                          N'Self Concept', N'Counseling')) AND (ParticipantActivity.[Activity Date] BETWEEN @Beginning_ActivityDate AND @End_ActivityDate)
    ORDER BY CASE WHEN StateCategory_tbl.[StateCategory] = N'Academic Support' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'In - Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'One-on-One Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Small Group Hours (up to 3) Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Large Group Tutoring Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Self Directed - Academic Service' THEN 0 ELSE 1 END, 
                          CASE WHEN StateCategory_tbl.[StateCategory] = N'Self Concept' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Youth Leadership Hrs' THEN 0 ELSE 1 END, 
                          CASE WHEN StateCategory_tbl.[StateCategory] = N'Counseling' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Individual Vocational Planning Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateServices_tbl.[StateServices] = N'Group Vocational Planning Hours' THEN 0 ELSE 1 END
    Last edited by desireemm; 10-20-09 at 14:45.

Posting Permissions

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