May 15, 2008 at 8:00 am
Hi All,
Great forum!!!
Here's my problem. I'm trying to build a course schedule of classes for my job and I want to stop the course descriptions (varchar(max)) from repeating in each record. My customer feels once is enough. He only wants to see one record with a course description and all subsequent records with the same course description are returned... however, without any data in the course description field.
Here's an example of what I'm trying to do:
Course Room Date Description
CIS101 123 1/1/08 Basic CIS class
CIS101 124 1/1/08
CIS101 125 1/1/08
CIS102 111 1/1/08 Advanced CIS clas
CIS102 112 1/1/08
CIS103 136 1/1/08 Bla Bla Bla
Any Ideas??
Thanks !
May 15, 2008 at 8:28 am
Please post the layout of your tables. That information is needed to provide an accurate reply.
Marvin Dillard
Senior Consultant
Claraview Inc
May 15, 2008 at 8:30 am
In this situation what you'd want to do is normalize a little by splitting the table into two tables.
You'd have one table, called Course (for example) that contains all of the fields that define a Course - The CourseId, Date and Description. I've made an assumption here that a given course has only one date. If that doesn't apply then take Date out.
Then you'd have another table called CourseRoom (as an example), which contains the CourseId and the room number.
CourseId Date Description
CIS101 1/1/08 Basic CIS class
CIS101 1/1/08
CIS101 1/1/08
CIS102 1/1/08 Advanced CIS clas
CIS102 1/1/08
CIS103 1/1/08 Bla Bla Bla
CourseId Room
CIS101 123
CIS101 124
CIS101 125
CIS102 111
CIS102 112
CIS103 136
In the first table, Course, CourseId is the primary key that identifies a Course row. In the second table, the primary key would be the combination of CourseId and Room. In other words, one course can only be run in one room. If my assumption regarding date was wrong then perhaps Date could go in the second table. Meaning that a Course can only be run in one room in a given date.
Whenever you return data relating to a course and the room it's being run in you then have to join the two tables - this is the cost of normalization.
Hope that helps.
May 15, 2008 at 10:05 am
Thanks Marvin!
SELECT distinct top (100)percent
dbo.COURSES.CRS_SUBJECT AS 'Course_Subject',
dbo.COURSES.CRS_NO AS 'Course_Number',
REPLACE(dbo.COURSES.CRS_TITLE,'ý',' ') AS 'Course_Title',
dbo.COURSE_SECTIONS.SEC_SYNONYM AS 'Synonym',
dbo.COURSE_SECTIONS.SEC_NO AS 'Section',
dbo.COURSE_SEC_MEETING.CSM_START_DATE AS 'Start_Date',
dbo.COURSE_SEC_MEETING.CSM_END_DATE AS 'End_Date',
dbo.COURSE_SEC_MEETING.CSM_BLDG AS 'Bldg',
dbo.COURSE_SEC_MEETING.CSM_ROOM AS 'Room',
dbo.COURSE_SECTIONS.SEC_LOCATION AS 'Location',
COALESCE(dbo.REG_BILLING_RATES.RGBR_CHARGE_AMT,0) AS 'Tuition',
COALESCE(reg2.RGBR_CHARGE_AMT,0) AS 'Lab Fee',
NULLIF (
CASE WHEN dbo.COURSE_SEC_MEETING.CSM_MONDAY IS NOT NULL
THEN 'M ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_TUESDAY IS NOT NULL
THEN 'Tu ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_WEDNESDAY IS NOT NULL
THEN 'W ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_THURSDAY IS NOT NULL
THEN 'Th ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_FRIDAY IS NOT NULL
THEN 'F ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_SATURDAY IS NOT NULL
THEN 'Sa ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_SUNDAY IS NOT NULL
THEN 'Su ' ELSE '' END, ' ' ) AS 'Meets',
dbo.COURSE_SEC_MEETING.CSM_START_TIME AS 'Start_Time',
dbo.COURSE_SEC_MEETING.CSM_END_TIME AS 'End_Time',
REPLACE (CAST(dbo.COURSES.CRS_DESC as VARCHAR(max)),'ý',' ') AS 'Course_Description',
dbo.COURSE_SECTIONS.SEC_CEUS AS 'CEUs'
FROM
dbo.COURSES
INNER JOIN
REG_BILLING_RATES ON
COURSES.COURSES_ID = REG_BILLING_RATES.RGBR_TUITION_COURSE
INNER JOIN
REG_BILLING_RATES reg2 ON
COURSES.COURSES_ID = reg2.RGBR_TUITION_COURSE
INNER JOIN
dbo.COURSE_SECTIONS
INNER JOIN
dbo.COURSE_SEC_MEETING ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.COURSE_SEC_MEETING.CSM_COURSE_SECTION
INNER JOIN
dbo.SEC_STATUSES ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.SEC_STATUSES.COURSE_SECTIONS_ID
INNER JOIN
dbo.TERMS ON
dbo.COURSE_SECTIONS.SEC_TERM = dbo.TERMS.TERMS_ID ON
dbo.COURSES.COURSES_ID = dbo.COURSE_SECTIONS.SEC_COURSE
LEFT OUTER JOIN
dbo.ACAD_REQMTS ON
dbo.COURSES.COURSES_ID = dbo.ACAD_REQMTS.ACR_PREREQ_COURSE
LEFT OUTER JOIN
dbo.PERSON
RIGHT OUTER JOIN
dbo.COURSE_SEC_FACULTY ON
dbo.PERSON.ID = dbo.COURSE_SEC_FACULTY.CSF_FACULTY ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.COURSE_SEC_FACULTY.CSF_COURSE_SECTION
May 15, 2008 at 10:15 am
Karl,
Thanks for your insight. I'm really new at this and a lot of the code I just posted was generated with the help of query designer.
I don't see how spliting up the tables in that way will get me to the finish line. 🙂
At first I thought the trick would have involved isolating the Course Description field in its own table, but it looks like I'm waaay off the mark.
How does your solution omit the redundant course descriptions? I'll read your post a couple more times.
Tyty!
Chris
May 15, 2008 at 10:15 am
Depending on the output mechanism - you may find it easiest to handle this kind of output in the output mechanism. For example - SSRS and Crystal both have fairly easy ways to handle this without having to mess with your query at all. If you happen to be outputting it to something like that, might be easiest there.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 10:20 am
Matt,
Good show! I do have Crystal 9 that I'm starting to get a handle on.
Would you have any buzzwords that could point me in the right direction?
Thanks!!
Chris
May 15, 2008 at 10:30 am
Chris (5/15/2008)
Matt,Good show! I do have Crystal 9 that I'm starting to get a handle on.
Would you have any buzzwords that could point me in the right direction?
Thanks!!
Chris
Look at the field properties (formatting as I recall) after you've dropped them onto the report. I think it's something like "suppress duplicates".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 10:42 am
Hi,
This should also do the trick for you:
SELECT
Course
,Room
,DAte
,NOte
, CASE WHEN Row_Number() OVER (PARTITION BY Course,DAte Order BY Course,DAte) = 1
THEN NOTE
ELSE NULL
END as [Note]
FROM MyTable
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 15, 2008 at 10:44 am
ooooops sorry it's late in the day
this is what you want (removing the one note field)
SELECT
Course
,Room
,DAte
, CASE WHEN Row_Number() OVER (PARTITION BY Course,DAte Order BY Course,DAte) = 1
THEN NOTE
ELSE NULL
END
FROM MyTable
you could also change the partition to make it work just on the Course regardless of the date
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 9:14 am
Chris,
I tried to insert your statement but I'm getting errors that say:
Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'NOTE'.
Here's what I did:
SELECT distinct top (100)percent
dbo.COURSES.CRS_SUBJECT as 'Course_Subject',
dbo.COURSES.CRS_NO AS 'Course_Number',
REPLACE(dbo.COURSES.CRS_TITLE,'ý',' ') AS 'Course_Title',
dbo.COURSE_SECTIONS.SEC_SYNONYM AS 'Synonym',
dbo.COURSE_SECTIONS.SEC_NO AS 'Section',
dbo.COURSE_SEC_MEETING.CSM_START_DATE AS 'Start_Date',
dbo.COURSE_SEC_MEETING.CSM_END_DATE AS 'End_Date',
dbo.COURSE_SEC_MEETING.CSM_BLDG AS 'Bldg',
dbo.COURSE_SEC_MEETING.CSM_ROOM AS 'Room',
dbo.COURSE_SECTIONS.SEC_LOCATION AS 'Location',
COALESCE(dbo.REG_BILLING_RATES.RGBR_CHARGE_AMT,0) AS 'Tuition',
COALESCE(reg2.RGBR_CHARGE_AMT,0) AS 'Lab Fee',
NULLIF (CASE WHEN dbo.COURSE_SEC_MEETING.CSM_MONDAY IS NOT NULL
THEN 'M ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_TUESDAY IS NOT NULL
THEN 'Tu ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_WEDNESDAY IS NOT NULL
THEN 'W ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_THURSDAY IS NOT NULL
THEN 'Th ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_FRIDAY IS NOT NULL
THEN 'F ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_SATURDAY IS NOT NULL
THEN 'Sa ' ELSE '' END + CASE WHEN dbo.COURSE_SEC_MEETING.CSM_SUNDAY IS NOT NULL
THEN 'Su ' ELSE '' END, '') AS 'Meets',
dbo.COURSE_SEC_MEETING.CSM_START_TIME AS 'Start_Time',
dbo.COURSE_SEC_MEETING.CSM_END_TIME AS 'End_Time',
REPLACE (CAST(dbo.COURSES.CRS_DESC as VARCHAR(max)),'ý',' ') AS 'Course_Description',
dbo.COURSE_SECTIONS.SEC_CEUS AS 'CEUs',
CASE WHEN Row_Number()
OVER (PARTITION BY
dbo.COURSES.CRS_DESC,
dbo.COURSE_SEC_MEETING.CSM_START_DATE Order BY dbo.COURSES.CRS_DESC,dbo.COURSE_SEC_MEETING.CSM_START_DATE) = 1
THEN NOTE
ELSE NULL
END
FROM
dbo.COURSES
INNER JOIN
REG_BILLING_RATES ON
COURSES.COURSES_ID = REG_BILLING_RATES.RGBR_TUITION_COURSE
INNER JOIN
REG_BILLING_RATES reg2 ON
COURSES.COURSES_ID = reg2.RGBR_TUITION_COURSE
INNER JOIN
dbo.COURSE_SECTIONS
INNER JOIN
dbo.COURSE_SEC_MEETING ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.COURSE_SEC_MEETING.CSM_COURSE_SECTION
INNER JOIN
dbo.SEC_STATUSES ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.SEC_STATUSES.COURSE_SECTIONS_ID
INNER JOIN
dbo.TERMS ON
dbo.COURSE_SECTIONS.SEC_TERM = dbo.TERMS.TERMS_ID ON
dbo.COURSES.COURSES_ID = dbo.COURSE_SECTIONS.SEC_COURSE
LEFT OUTER JOIN
dbo.ACAD_REQMTS ON
dbo.COURSES.COURSES_ID = dbo.ACAD_REQMTS.ACR_PREREQ_COURSE
LEFT OUTER JOIN
dbo.PERSON
RIGHT OUTER JOIN
dbo.COURSE_SEC_FACULTY ON
dbo.PERSON.ID = dbo.COURSE_SEC_FACULTY.CSF_FACULTY ON
dbo.COURSE_SECTIONS.COURSE_SECTIONS_ID = dbo.COURSE_SEC_FACULTY.CSF_COURSE_SECTION
May 16, 2008 at 9:28 am
Like Matt suggested, leave the presentation in the reporting application. Making SQL do this is overly complex and will slow the whole thing down. Crystal does pretty layouts and such better than SQL, since that's what it's for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2008 at 10:44 pm
Chris (5/15/2008)
Hi All,Great forum!!!
Here's my problem. I'm trying to build a course schedule of classes for my job and I want to stop the course descriptions (varchar(max)) from repeating in each record. My customer feels once is enough. He only wants to see one record with a course description and all subsequent records with the same course description are returned... however, without any data in the course description field.
Here's an example of what I'm trying to do:
Course Room Date Description
CIS101 123 1/1/08 Basic CIS class
CIS101 124 1/1/08
CIS101 125 1/1/08
CIS102 111 1/1/08 Advanced CIS clas
CIS102 112 1/1/08
CIS103 136 1/1/08 Bla Bla Bla
Any Ideas??
Thanks !
Please click on the URL in my signature line... It would be real nice if you did the things that appear in that article so you can get better answers quicker... 😉
Christopher Stobbs almost had it... and it's not that "complex"... 😉
--===== Create and populate a test table
-- This is not part of the solution... it's what the op
-- should have posted.
CREATE TABLE #MyHead
(Course VARCHAR(20),
Room VARCHAR(10),
Date DATETIME,
Description VARCHAR(50))
INSERT INTO #MyHead
(Course,Room,Date,Description)
SELECT 'CIS101','123','1/1/08','Basic CIS class' UNION ALL
SELECT 'CIS101','124','1/1/08','Basic CIS class' UNION ALL
SELECT 'CIS101','125','1/1/08','Basic CIS class' UNION ALL
SELECT 'CIS102','111','1/1/08','Advanced CIS clas' UNION ALL
SELECT 'CIS102','112','1/1/08','Advanced CIS clas' UNION ALL
SELECT 'CIS103','136','1/1/08','Bla Bla Bla '
--===== This is the solution...
;WITH
cteCourse AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Course,Date ORDER BY Course,Date,Room) AS Rank,
Course,Room,Date,Description
FROM #MyHead
)
SELECT Course,Room,Date,
CASE
WHEN Rank = 1
THEN Description
ELSE ''
END AS Description
FROM cteCourse
--===== Housekeeping
DROP TABLE #MyHead
The "key" is to use the CTE to gather the info and give it the row numbers like above... doesn't matter how complex the query (I showed a very simple one). Then, the SELECT from the CTE will do the formatting very nicely.
--Jeff Moden
May 19, 2008 at 7:43 am
RBAR,
Thats it!
That should do it.
I also went to the link you mentioned and I can see how it could have helped the people in the room.
My thanks to everyone for their input and patience. 🙂
Chris
May 19, 2008 at 6:44 pm
Heh.. "Oh no"... I've earned the nickname of the code I hate the most 😛
Glad it helped, Chris.
Do keep in mind that the others are correct, though... if you have a presentation layer, let it do the work... keeps the server from having to... distributed processing and all those good buzz words...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply