SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please Help Me Pull this off

    hello Gentlemen,

    i am currently at a cross road, i have to pull off a report for a university, with all students and their registration number in a row and all the courses they have registered in a session as columns. Now, the interesting thing is that as the rows increase horizontally downwards with the list of students and their registration numbers in a class, the columns increase vertically simultaneously with the courses that was registered, the course units and marks obtained.

    please see schema of tables with explanation below:

    institution_Courses is where all university courses are held
    institution_programme is where all the programmes the university offers are stored
    institution_PersonalData is where all the student profiles are held
    Institution_Academicprofile is where all the academic profiles of the students are held
    Institution_Programme_Courses is where all the courses a programme offeres are held i.e computer science offers csc 101, csc 112, etc...
    Institution_Programme_Course_Registration is where all student course registrations are stored across different sessions



    CREATE TABLE [dbo].[Institution_Courses](
    [CourseId] [int] IDENTITY(1,1) NOT NULL,
    [CourseCode] [varchar](20) NULL,
    [courseName] [varchar](150) NULL,
    [Activated] [bit] NULL,
    CONSTRAINT [PK_Institution_Courses] PRIMARY KEY CLUSTERED
    (
    [CourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_Programme](
    [ProgrammeId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeTypeId] [int] NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [CertificateId] [int] NULL,
    [ProgrammeName] [varchar](500) NULL,
    [StartLevel] [varchar](50) NULL,
    [EndLevel] [varchar](50) NULL,
    [Duration] [int] NULL,
    [UnitsRequired] [int] NULL,
    [Activated] [bit] NULL,
    [categoryid] [int] NULL,
    CONSTRAINT [PK_Institution_Programme] PRIMARY KEY CLUSTERED
    (
    [ProgrammeId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_PersonalData](
    [PersonalId] [int] IDENTITY(1,1) NOT NULL,
    [SessionId] [int] NOT NULL,
    [Surname] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [DateofBirth] [datetime] NULL,
    [PlaceofBirth] [varchar](50) NULL,
    [Sex] [char](1) NULL,
    [Religion] [int] NULL,
    [MaritalStatus] [int] NULL,
    [PhoneNumber] [varchar](50) NULL,
    [EmailAddress] [varchar](50) NULL,
    [NationalityId] [int] NULL,
    [StateofOrigin] [int] NULL,
    [LGA] [int] NULL,
    [HomeTown] [varchar](50) NULL,
    [RefCode] [uniqueidentifier] NOT NULL,
    [DateFilled] [datetime] NULL,
    [PictureURL] [varchar](100) NULL,
    [StatusId] [int] NOT NULL,
    [contact_address] [varchar](max) NULL,
    [ModeOfEntry] [varchar](50) NULL,
    CONSTRAINT [PK_Institution_PersonalData] PRIMARY KEY CLUSTERED
    (
    [PersonalId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_AcademicProfile](
    [AcademicDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [MatricNo] [varchar](50) NULL,
    [ProgrammeId] [int] NULL,
    [CurrentLevel] [int] NULL,
    [CurrentSession] [int] NULL,
    [EntryRegNo] [varchar](50) NULL,
    [AcademicStatusId] [int] NULL,
    [categoryid] [int] NULL,
    [NDGPA] [varchar](10) NULL,
    [HNDGPA] [varchar](10) NULL,
    PRIMARY KEY CLUSTERED
    (
    [AcademicDetailsId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Courses](
    [ProgrammeCourseId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [CourseTypeId] [int] NOT NULL,
    [CourseUnit] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [Activated] [bit] NULL,
    [pass_mark] [numeric](18, 2) NULL,
    CONSTRAINT [PK_Institution_Programme_Courses] PRIMARY KEY CLUSTERED
    (
    [ProgrammeCourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Course_Registration](
    [CourseRegId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [SessionId] [int] NOT NULL,
    [DateReg] [datetime] NULL,
    [Approved] [bit] NULL,
    [DateApproved] [datetime] NULL,
    [TestScore] [decimal](18, 2) NULL,
    [ExamScore] [decimal](18, 2) NULL,
    [Total] [decimal](18, 2) NULL,
    [DateRecorded] [datetime] NULL,
    [CourseUnit] [int] NULL,
    [CourseType] [varchar](20) NULL,
    CONSTRAINT [PK_Institution_Programme_Course_Registration] PRIMARY KEY CLUSTERED
    (
    [CourseRegId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Please help as i am at a cross road.

    also attached is a sample of the report to be produced

    Thanks Gurus.
    Attached Files Attached Files

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What database are you using?
    And what is the problem you're having?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's microsoft sql server, guido (biggest hints are the square brackets and the IDENTITY property)

    what he wants is a PIVOT report query written for him, i.e. a crosstab layout

    sounds like a homework assignment to me, no way i'm doing it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello Guys, thanks for your comments. i beg to disagree it is a home work or assignment, else i would have shown the teacher to what extent i have gone and then the teacher can tell me where exactly where i got it wrong, so i can get better at it next time, this is my principle about learning. unfortunately, this is not so and i cannot show the client query that does not churn out his report, have tried using the cross tab query as indicated by r937 but not just getting it. i would appreciate any help that can be rendered as time is runing out on me. yes its MS SQL Server 2008R2.

    thanks

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Can't help you with MS SQL server, sorry.
    Quote Originally Posted by nellyihu View Post
    i would have shown the teacher to what extent i have gone and then the teacher can tell me where exactly where i got it wrong, so i can get better at it next time, this is my principle about learning.
    Yes, that's what works best here too
    i cannot show the client query that does not churn out his report
    Why not?

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks Buddy, will keep on searching till i get a clue.
    Why not?
    because the company have been paid to deliver. i had pulled it off using classic asp but if you print, does not display header and footers that is why i resorted to sql reporting services however i am new to cross tabs therefore do not know how to construct the query. would appreciate a hint to let me carry on. thanks all the same buddy. cheers

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh... ok thx


Bookmarks

Posting Permissions

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