Home » SQL & PL/SQL » SQL & PL/SQL » datesheet query
datesheet query [message #116432] Wed, 20 April 2005 02:03 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have 96 courses in a semester. I have found out the clashes among them as below. the third column is the no of students reading both courses.

(See attached File)

I am making my mind for creating examination datesheet.
My question is:
could I group the courses which have no clashes among them. eg. 1. EE222 and EE332
2. MM323, ES371 and CS461

Waiting for your reply eagerly...
Khan


  • Attachment: ds.txt
    (Size: 174.59KB, Downloaded 380 times)
Re: datesheet query [message #116752 is a reply to message #116432] Thu, 21 April 2005 18:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It might be easier to extract the desired results from your original tables, but you did not supply those, so I loaded the data from your ds.txt into an external table, and queried that. Since there are 70 distinct classes, then there are 4900 possible combinations of classes. If you subtract from those 4900 possible combinations of classes, the "clashes" that you have identified, then you are left with the ones for which no students are taking both classes. You can then use a method that I learned from William Robertson, to group those non-clashing classes together for each class. Please see the demonstration below. I am not sure if this is exactly what you want, as your requirements were a bit fuzzy, with only a minimal example of desired output.

scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle'
  2  /

Directory created.

scott@ORA92> CREATE TABLE ds_tab
  2    (course_c1 VARCHAR2(5),
  3  	course_c2 VARCHAR2(5),
  4  	students  NUMBER)
  5  ORGANIZATION EXTERNAL
  6    (TYPE ORACLE_LOADER
  7  	DEFAULT DIRECTORY my_dir
  8  	ACCESS PARAMETERS
  9  	  (FIELDS TERMINATED BY WHITESPACE
 10  	     (course_c1,
 11  	      course_c2,
 12  	      students))
 13    LOCATION ('ds.txt'))
 14  /

Table created.

scott@ORA92> SELECT COUNT(*) FROM ds_tab
  2  /

  COUNT(*)
----------
      1352

scott@ORA92> SELECT COUNT (DISTINCT course_c1) FROM ds_tab
  2  /

COUNT(DISTINCTCOURSE_C1)
------------------------
                      70

scott@ORA92> COLUMN no_clashes_with FORMAT A80 WORD_WRAPPED
scott@ORA92> SELECT course_c1 AS course,
  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (course_c2, ', '), 3)) AS no_clashes_with
  3  FROM   (SELECT course_c1, course_c2,
  4  		    ROW_NUMBER () OVER
  5  		      (PARTITION BY course_c1 ORDER BY course_c2) AS curr,
  6  		    ROW_NUMBER () OVER
  7  		      (PARTITION BY course_c1 ORDER BY course_c2) - 1 AS prev
  8  	     FROM   (SELECT a.course_c1, b.course_c2
  9  		     FROM   (SELECT DISTINCT course_c1 FROM ds_tab) a,
 10  			    (SELECT DISTINCT course_c1 AS course_c2 FROM ds_tab) b
 11  		     MINUS
 12  		     SELECT course_c1, course_c2
 13  		     FROM   ds_tab))
 14  START  WITH curr = 1
 15  CONNECT BY PRIOR curr = prev AND PRIOR course_c1 = course_c1
 16  GROUP  BY course_c1
 17  /

COURS NO_CLASHES_WITH
----- --------------------------------------------------------------------------------
CS211 CS313, CS419, CS433, CS461, CS467, CS478, EE332, EE333, EE341, EE371, EE424,
      EE434, EE461, EE462, EE474, ES333, ES341, ES342, ES371, ES376, ES445, ES446,
      ES464, ES465, ES474, ES475, ME102, ME212, ME213, ME313, ME333, ME352, ME362,
      ME416, ME464, ME474, ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324,
      MM362, MM426, MM471, PH101, PH102

CS223 CS313, CS327, CS331, CS419, CS433, CS461, CS467, CS478, EE212, EE231, EE332,
      EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES213, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      HM102, ME102, ME212, ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474,
      ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471,
      PH101, PH102

CS313 CS211, CS223, CS419, CS461, CS478, EE212, EE222, EE223, EE231, EE332, EE333,
      EE341, EE371, EE434, EE461, EE462, EE474, ES213, ES464, ES465, ES475, ME102,
      ME212, ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

CS322 CS419, CS433, CS461, CS467, CS478, EE212, EE223, EE231, EE332, EE333, EE341,
      EE371, EE434, EE461, EE462, EE474, ES201, ES213, ES333, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME102, ME212, ME213, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MM471, MT102, PH101, PH102

CS327 CS223, CS419, CS433, CS461, CS467, CS478, EE212, EE222, EE223, EE231, EE332,
      EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES201, ES213,
      ES333, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      HM102, ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464,
      ME474, ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MT101, MT102, PH101, PH102

CS331 CS223, CS419, CS461, CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341,
      EE371, EE434, EE461, EE462, EE474, ES201, ES213, ES333, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

CS351 CS419, EE212, EE223, EE231, EE434, EE461, EE462, EE474, ES201, ES213, ES333,
      ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102,
      ME102, ME212, ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM212,
      MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101,
      PH102

CS419 CS211, CS223, CS313, CS322, CS327, CS331, CS351, EE212, EE222, EE223, EE231,
      EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE474, ES201, ES213,
      ES333, ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474,
      ES475, HM102, HM322, ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362,
      ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362,
      MM426, MM471, MS311, MT101, MT102, PH101, PH102

CS433 CS211, CS223, CS322, CS327, EE212, EE222, EE223, EE231, EE332, EE333, EE341,
      EE371, EE424, EE434, EE461, EE462, EE474, ES201, ES213, ES333, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME212, ME213, ME291, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

CS461 CS211, CS223, CS313, CS322, CS327, CS331, EE222, EE223, EE231, EE332, EE341,
      EE371, EE424, EE461, EE474, ES213, ES333, ES341, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MS311, MT101, MT102, PH101, PH102

CS467 CS211, CS223, CS322, CS327, EE212, EE222, EE223, EE231, EE332, EE333, EE341,
      EE371, EE424, EE434, EE461, EE462, EE474, ES201, ES213, ES333, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME212, ME213, ME291, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

CS478 CS211, CS223, CS313, CS322, CS327, CS331, EE212, EE222, EE223, EE231, EE332,
      EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE474, ES201, ES213, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102,
      MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102,
      PH101, PH102

EE212 CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478, EE424,
      EE461, EE474, ES202, ES213, ES333, ES341, ES342, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212, ME213, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MM471, MT101

EE222 CS313, CS327, CS331, CS419, CS433, CS461, CS467, CS478, EE332, EE333, EE341,
      EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES213, ES333, ES342, ES361,
      ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME313, ME333, ME362,
      ME474, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM471

EE223 CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478, EE332,
      EE333, EE341, EE371, EE424, EE461, EE474, ES213, ES333, ES341, ES342, ES361,
      ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212,
      ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM102, MM212,
      MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MS311

EE231 CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE424, EE434, EE461, EE462, EE463, EE474, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME102, ME313, ME333, ME352,
      ME362, ME416, ME464, ME474, ME496, MM212, MM231, MM233, MM314, MM323, MM324,
      MM362, MM426, MM471

EE332 CS211, CS223, CS313, CS322, CS327, CS331, CS419, CS433, CS461, CS467, CS478,
      EE222, EE223, EE461, EE474, ES201, ES202, ES213, ES333, ES341, ES342, ES361,
      ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475, ME102, ME212, ME213,
      ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

EE333 CS211, CS223, CS313, CS322, CS327, CS331, CS419, CS433, CS467, CS478, EE222,
      EE223, EE461, EE474, ES202, ES213, ES333, ES341, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, ME102, ME212, ME213, ME291, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM102, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

EE341 CS211, CS223, CS313, CS322, CS327, CS331, CS419, CS433, CS461, CS467, CS478,
      EE222, EE223, EE434, EE461, EE462, EE463, EE474, ES201, ES202, ES213, ES341,
      ES361, ES445, ES446, ES464, ES465, ES474, ES475, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

EE371 CS211, CS223, CS313, CS322, CS327, CS331, CS419, CS433, CS461, CS467, CS478,
      EE222, EE223, EE434, EE461, EE462, EE463, EE474, ES201, ES202, ES213, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474,
      ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471,
      MT101, MT102, PH101, PH102

EE424 CS211, CS223, CS327, CS419, CS433, CS461, CS467, CS478, EE212, EE222, EE223,
      EE231, EE461, EE462, ES201, ES213, ES361, ES464, ES465, ES475, ME102, ME212,
      ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212,
      MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101,
      PH102

EE434 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478,
      EE222, EE231, EE341, EE371, EE461, ES202, ES213, ES333, ES341, ES342, ES361,
      ES371, ES376, ES445, ES446, ES464, ES465, ME212, ME213, ME291, ME313, ME333,
      ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323,
      MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

EE461 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE474, ES201, ES202, ES213, ES333, ES341, ES342, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM102, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MS311, MT101, MT102, PH101,
      PH102

EE462 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478,
      EE222, EE231, EE341, EE371, EE424, ES202, ES213, ES333, ES341, ES342, ES361,
      ES371, ES376, ES445, ES446, ME212, ME213, ME291, ME313, ME333, ME352, ME362,
      ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362,
      MM426, MM471, MS311, MT101, MT102, PH101, PH102

EE463 CS223, CS327, EE222, EE231, EE341, EE371, ES213, ES333, ES342, ES361, ES371,
      ES376, ES445, ES446, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MT101, MT102, PH101, PH102

EE474 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE461, ES201,
      ES202, ES213, ES333, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465,
      HM102, HM322, ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MT101, MT102, PH101, PH102

ES201 CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478, EE332, EE341, EE371,
      EE424, EE461, EE474, ES342, ES361, ES445, ES446, ES464, ES465, ES474, ES475,
      HM102, ME102, ME212, ME213, ME416, ME464, ME474, ME496, MM102, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT102, PH101, PH102

ES202 EE212, EE332, EE333, EE341, EE371, EE434, EE461, EE462, EE474, ES342, ES361,
      ES371, ES376, ME313, ME333, ME352, ME362, PH101, PH102

ES213 CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462,
      EE463, EE474, ES333, ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464,
      ES465, ES474, ES475, HM322, ME102, ME212, ME213, ME313, ME333, ME352, ME362,
      ME416, ME464, ME474, ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324,
      MM362, MM426, MM471, MT101, PH101, PH102

ES333 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE371, EE434, EE461, EE462, EE463,
      EE474, ES213, ES445, ES474, ES475, HM102, ME102, ME212, ME213, ME291, ME313,
      ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MM426, MT101, MT102, PH101, PH102

ES341 CS211, CS223, CS419, CS461, CS478, EE212, EE223, EE231, EE332, EE333, EE341,
      EE371, EE434, EE461, EE462, ES213, ES445, ES446, ES465, ME212, ME213, ME333,
      ME352, ME362, MM102, MM212, MM231, MM233, MT101, MT102, PH102

ES342 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS478, EE212, EE222, EE223,
      EE231, EE332, EE333, EE371, EE434, EE461, EE462, EE463, EE474, ES201, ES202,
      ES213, ES361, ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102,
      ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496,
      MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101,
      MT102, PH101, PH102

ES361 CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478, EE212,
      EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462,
      EE463, EE474, ES201, ES202, ES213, ES342, ES376, ES445, ES446, ES464, ES465,
      ES474, ES475, HM102, ME102, ME212, ME213, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, ME496, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362,
      MM426, MM471, MS311, MT101, MT102, PH101, PH102

ES371 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE371, EE434, EE461, EE462, EE463,
      EE474, ES202, ES213, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213,
      ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

ES376 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE371, EE434, EE461, EE462, EE463,
      EE474, ES202, ES213, ES342, ES361, ES445, ES446, ES464, ES465, ES475, HM102,
      ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474,
      MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT101,
      MT102, PH101, PH102

ES445 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE434, EE461, EE462,
      EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES376, ES464, ES465,
      ES474, ES475, ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MT101, MT102, PH101, PH102

ES446 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE434, EE461, EE462,
      EE463, EE474, ES201, ES213, ES341, ES342, ES361, ES376, ES464, ES465, ES474,
      ES475, ME102, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464,
      ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471,
      MT101, MT102, PH101, PH102

ES464 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE474, ES201, ES213, ES342, ES361, ES371, ES376, ES445, ES446, ES474,
      ES475, HM102, HM322, ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416,
      ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MS311, MT101,
      MT102, PH101, PH102

ES465 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE474, ES201, ES213, ES341, ES342, ES361, ES371, ES376, ES445, ES446,
      ES474, ES475, HM102, HM322, ME212, ME213, ME291, ME313, ME333, ME352, ME362,
      ME416, ME464, ME474, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362,
      MM426, MS311, MT101, MT102, PH101, PH102

ES474 CS211, CS223, CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478, EE212,
      EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE461, ES201, ES213, ES333,
      ES342, ES361, ES371, ES445, ES446, ES464, ES465, HM322, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MM426, MM471, MT101, MT102, PH101, PH102

ES475 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS467, CS478,
      EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE461, ES201,
      ES213, ES333, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, HM322,
      ME212, ME213, ME291, ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102,
      MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471, MS311, MT101,
      MT102, PH101, PH102

HM102 CS223, CS327, CS331, CS351, CS419, EE212, EE223, EE461, EE474, ES201, ES333,
      ES342, ES361, ES371, ES376, ES464, ES465, ME313, ME333, ME352, ME362, ME464,
      ME474, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM471

HM322 CS419, EE212, EE461, EE474, ES213, ES464, ES465, ES474, ES475, ME102, ME474,
      MM212, MM231, MM233, MS311, MT101, MT102, PH101, PH102

ME102 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, EE212, EE223, EE231,
      EE332, EE333, EE341, EE371, EE424, EE461, EE474, ES201, ES213, ES333, ES342,
      ES361, ES371, ES376, ES445, ES446, HM322, ME212, ME213, ME291, ME416, ME464,
      ME474, MM212, MM231, MM233, MM314, MM323, MM324, MM362

ME212 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462,
      EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ES474, ES475, ME102, ME313, ME333, ME416, ME474, ME496,
      MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM471, PH101, PH102

ME213 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462,
      EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ES474, ES475, ME102, ME313, ME333, ME352, ME362, ME416,
      ME464, ME474, ME496, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426,
      MM471, MS311, PH101, PH102

ME291 CS327, CS419, CS433, CS461, CS467, CS478, EE332, EE333, EE341, EE371, EE424,
      EE434, EE461, EE462, EE463, EE474, ES333, ES342, ES371, ES376, ES445, ES446,
      ES464, ES465, ES474, ES475, ME102, ME474, MM314, MM323, MM324, MM362, PH102

ME313 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES202, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME212, ME213, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MT102, PH102

ME333 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES202, ES213, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME212, ME213, MM212,
      MM231, MM233, MM314, MM323, MM324, MM362, MM426, MT102, PH102

ME352 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434, EE461,
      EE462, EE463, EE474, ES202, ES213, ES333, ES341, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME213, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MM471, MT102, PH102

ME362 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES202, ES213, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME213, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MT102, PH102

ME416 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434, EE461,
      EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376, ES445,
      ES446, ES464, ES465, ES474, ES475, ME102, ME212, ME213, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MT101, MT102, PH102

ME464 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434, EE461,
      EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376, ES445,
      ES446, ES465, ES474, ES475, HM102, ME102, ME213, MM212, MM231, MM233, MM314,
      MM323, MM324, MM362, MT101, MT102, PH101, PH102

ME474 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212, ME213,
      ME291, MM102, MM212, MM231, MM233, MM314, MM323, MM324, MM362, MM426, MM471,
      MT101, MT102, PH101, PH102

ME496 CS211, CS223, CS322, CS327, EE212, EE223, EE231, EE333, EE371, EE461, ES201,
      ES213, ES342, ES361, ME212, ME213, MM212, MM231, MM233, MM323, MM362, MT101,
      MT102, PH102

MM102 CS211, CS223, CS327, CS419, CS433, CS461, CS467, CS478, EE223, EE332, EE333,
      EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES201, ES213, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      ME474, MM314, MM323, MM324, MM362, MM471, MS311

MM212 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212,
      ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM362, MM426,
      MM471, MS311, MT102, PH101, PH102

MM231 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212,
      ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM362, MM426,
      MM471, MS311, MT102, PH101, PH102

MM233 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES341, ES342, ES361, ES371,
      ES376, ES445, ES446, ES464, ES465, ES474, ES475, HM102, HM322, ME102, ME212,
      ME213, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM426, MM471,
      MS311, MT102, PH101, PH102

MM314 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MT101, MT102, PH101,
      PH102

MM323 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM102, MM426, MM471,
      MT101, MT102, PH101, PH102

MM324 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, MM102, MT102, PH101, PH102

MM362 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376,
      ES445, ES446, ES464, ES465, ES474, ES475, HM102, ME102, ME212, ME213, ME291,
      ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM102, MM212, MM231,
      MT102, PH101, PH102

MM426 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434, EE461,
      EE462, EE463, EE474, ES201, ES213, ES333, ES342, ES361, ES371, ES376, ES445,
      ES446, ES465, ES474, ES475, ME213, ME313, ME333, ME474, MM212, MM231, MM233,
      MM323, MT101, MT102, PH101, PH102

MM471 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE212, EE222, EE223, EE231, EE332, EE333, EE341, EE371, EE424, EE434,
      EE461, EE462, EE463, EE474, ES201, ES213, ES342, ES361, ES371, ES376, ES445,
      ES446, ES474, ES475, HM102, ME212, ME213, ME313, ME352, ME474, MM102, MM212,
      MM231, MM233, MM323, MT102, PH101, PH102

MS311 CS419, CS461, EE223, EE461, EE462, ES361, ES464, ES465, ES475, HM322, ME213,
      MM102, MM212, MM231, MM233, MT102, PH102

MT101 CS313, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478, EE212, EE332,
      EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES213, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      HM322, ME416, ME464, ME474, ME496, MM314, MM323, MM426

MT102 CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467, CS478, EE332,
      EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474, ES201, ES333,
      ES341, ES342, ES361, ES371, ES376, ES445, ES446, ES464, ES465, ES474, ES475,
      HM322, ME313, ME333, ME352, ME362, ME416, ME464, ME474, ME496, MM212, MM231,
      MM233, MM314, MM323, MM324, MM362, MM426, MM471, MS311

PH101 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474,
      ES201, ES202, ES213, ES333, ES342, ES361, ES371, ES376, ES445, ES446, ES464,
      ES465, ES474, ES475, HM322, ME212, ME213, ME464, ME474, MM212, MM231, MM233,
      MM314, MM323, MM324, MM362, MM426, MM471, PH102

PH102 CS211, CS223, CS313, CS322, CS327, CS331, CS351, CS419, CS433, CS461, CS467,
      CS478, EE332, EE333, EE341, EE371, EE424, EE434, EE461, EE462, EE463, EE474,
      ES201, ES202, ES213, ES333, ES341, ES342, ES361, ES371, ES376, ES445, ES446,
      ES464, ES465, ES474, ES475, HM322, ME212, ME213, ME291, ME313, ME333, ME352,
      ME362, ME416, ME464, ME474, ME496, MM212, MM231, MM233, MM314, MM323, MM324,
      MM362, MM426, MM471, MS311, PH101


70 rows selected.

Re: datesheet query [message #424982 is a reply to message #116752] Tue, 06 October 2009 22:28 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I came here quickly( Laughing ) again.
surly, this is the required output. But I want to go a step ahead. The scenario is:

every course has a level. there are only four levels of courses. e.g. (100,200,300,400)

example:

course Level
CS101 100
CS221 200
CS331 300
EE452 400


Required Query: to generate Examination Datesheet

Examination continues for =<7 days, with three slots(timings) on each day.




I plan to 

1.  place 100 level course one on each day of examinatin.

2. Place common courses at 200,300 and 400 level relative to 100 level courses.

3. Place courses common to three or four faculties relative to already placed courses.

4. place courses common to two faculties relative to already places courses.

5. place courses for each level of each faculty for sophomore, junior and senior level.

6. validate and optimize.

7. issue date sheet



a sample PDF document is attached.

so from the above (Barbara Boehmer) reply, which logic will produce the desired (a step forward) output? and how?

Riaz

[Updated on: Tue, 06 October 2009 22:38]

Report message to a moderator

Re: datesheet query [message #426725 is a reply to message #424982] Mon, 19 October 2009 03:14 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Any idea please?

Riaz
Re: datesheet query [message #426797 is a reply to message #426725] Mon, 19 October 2009 12:37 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
rzkhan wrote on Mon, 19 October 2009 01:14
Any idea please?

Riaz


1. Four and a half years is a bit long to wait to provide feedback as to whether a previous response was what you wanted or not.

2. You should read the forum guidelines for instructions on how we expect questions to be posted. You will get faster, better respones from properly posted questions. You need to supply things like create table and isnert statements for sample data. Also, many people cannot download files, so it is best to include examples as part of the text of the post.

3. The purpose of this forum is not to have your homework done for you. It is to help you when you get stuck. You need to show some effort of your own. Post some code taht you tried and the errors or results that you got.


Previous Topic: Create View with last 10080 records
Next Topic: to know free and used spaces in a table
Goto Forum:
  


Current Time: Fri Dec 09 03:37:40 CST 2016

Total time taken to generate the page: 0.15208 seconds