Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pivot table plus more issues -  need help with denormalizing data

Posted on 2007-11-24
6
Medium Priority
?
332 Views
Last Modified: 2012-05-05
I am building course calendar for some school. Everything is done except for the actual display of the calendar. I have to work on existing database structure -  meaning - I can add my tables and stored procs but can not change the ones that already exist.  On top of that - calendar display needs to be in the form of pivoted table (html table at that) that has the columns set to dates with subsets of time frames (morning, afternoon and evening) and rows set to class room  numbers.
The challenge lays in the way that data is stored and in the display format of calendar (pivot table).
Data is stored as follows:
course_id, section_id, semester, class_room, start_date, end_date, number_of_sessions, session_start_time, session_end_time, Mon, Tue, Wed, Thu, Fri, Sat, Sun

- Mon through Sun - contain the value set to 1 if class happens on that day of the week
- session_start_time and session_end_time are the start and end times for classes (ex. 8:00AM - 6:00PM)

My idea is to create another table based on existing date I described above that will have the following data in it:
course_id, section_id, semester, class_room, session_date, lesson_number, session_start_time, session_end_time, time_frame
 the problem I have is with mapping data from one table to another:
-  session_date -  needs to be a date calculated based on start_date, number_of_sessions or end_date, and Mon trough Sun values (if 1 then number of sessions advances)
- lesson_number -  i guess that would be the advancing session number from calculation of session_date
- time_frame -  well -  there I was planing to put morning, afternoon, evening, morning_afternoon, afternoon_evening, all_day -  that is based on the session_start_time and session_end_time. Time frames would be timed 9:00AM - 12:00PM, 12:00PM - 5:00PM, 5:00PM - 10:00PM ( session_start and end times could be something like 8:30AM - 6:30PM - so they do not correspond exactly to time frame intervals but fall within one of the intervals defined above.

So - how do I map that data and display it in the format I want?
I am out of ideas and would give 1 000 000  points if I only could to expert who helps me with that.
Ideally -  I would like to see as much ready solution as possible -  but I will really appreciate any pointers I can get.
 
Thanks in advance




0
Comment
Question by:lescluster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20358747
Can you have more than one  course occupying the same classroom for different times within a subset of a day?
0
 

Author Comment

by:lescluster
ID: 20358895
Mike - that's a YES
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20360144
You need a recordset that has one entry per course_id, per date, per day subset. It should have the Class_Room and the Start/End times. This would act as a base for your Crosstab, showing the Course_IDs at the intersection of the Class-ID rows and The Date/Day Subset columns.
You can get this with a Select statement. For that you'll need a 'helper' table, I usually call it Nums, with a single integer column, Num. Populate this with the integers from 0 to a number comfortably greater than the number of days in a Semester, one number per row. The basic idea of using Nums is to create several output rows where there is only one input row. So, to start with, if you want one row per Course_ID, by date for all the course instances on Mondays in a semester:
Assume two date parameters, @Start and @End for the semester.

Select C.*, DateAdd(d, Num, @Start) as CalDate
From tblCourses C, Nums
Where
Mon = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 2
And Num Between 0 and DateDiff(d, @Start, @End)

This can be extended to the other days of the week, but I don't have time for that right now. I'll pick it up again tomorrow if you want to pursue this line of attack.

Hope this helps,
Mike

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:lescluster
ID: 20360173
Mike - let's continue -  I was thinking about it along the same lines.
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 total points
ID: 20360460
Just found an extra 5 mins. This, I think, adds the rest of the days of the week:

Select C.*, DateAdd(d, Num, @Start) as CalDate
From tblCourses C, Nums
Where
Mon = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 2
Tue = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 3
Wed = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 4
Thu = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 5
Fri = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 6
Sat = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 7
Sun = 1 And DatePart(dw, DateAdd(d, Num, @Start)) = 1
And Num Between 0 and DateDiff(d, @Start, @End)

How should the course data display in the columns? Just once base on start time, or in each that it occurs?
Is the Start/End time printed with the course?
0
 

Author Comment

by:lescluster
ID: 20360587
I would like to show course_id and section_id together with start and end times once that spans across as many time frames as it is affecting. I was also thinking about generating bars the length of time intervals and actually get rid of hard set time frames.
Start/end time are printed with the course/ section info .
Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn how to count occurrences of each item in an array.

671 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question