November 28, 2011 at 7:40 am
I don't do enough sql programming anymore to be able to figure out the following: if I have courses offered in different regions and the registrar wants the course catalog to group them according their location so they will print so that students in each region can register for courses located geographically close to each other, that isn't a problem for me. However, the desire is that online courses print in ALL groupings AND as their own section for the students who only take courses online.
I am trying to do this using the reporting tool that comes with our student management software, I would compare it to an impaired cousin of Crystal Reports. If a temp table is used, it would have to be derived.
what I am using currently:
computing:
case when section_schedules.loc_cde in (group1) then 'a' when section_schedules.loc_cde in (group2) then 'b' when section_schedules.loc_cde in (group3) then 'c' when section_schedules.loc_cde in (group4) then 'd' when section_schedules.loc_cde = 'onlne' then 'o' when section_schedules.loc_cde = location5 then 'p' else 'z' end as sort
my first order variable is sort, then by course information. I tried using a user-defined field on a "location master" table to group the location codes into regions as well. Group 1 currently contains all of the locations in "Region 1" as well as online.
I really have no idea how to get it to work using this reporting tool. Or if this makes sense to anyone but me...
November 28, 2011 at 8:39 am
You want each online course to appear in all regions, so select the onlinecourses only and get the product with the distinct regions, then combine this back into the non-online courses an do your grouping and sorting accordingly.
Perhaps something like this pseudo-code
;WITH OnlineCourses AS (
(... SELECT online courses only FROM yourTable ... )
), OnlineCrossRegions AS (
(... SELECT Just the DISTINCT NonOnline Regions, with the online Courses
FROM yourTable
CROSS JOIN OnlineCourses ...)
)
SELECT NonOnline Courses FROM yourTable
UNION SELECT OnlineCrossRegions
GROUPING/SORTING
November 28, 2011 at 8:44 am
mgoldade (11/28/2011)
However, the desire is that online courses print in ALL groupings AND as their own section for the students who only take courses online.
Remember that CASE is a "function" and can only produce a single result for each record. You're trying to get it to return multiple results (each of several regions) for a single record (each online course). It just won't work.
You need to have some way to produce multiple results for some records. Typically this will involve some sort of UNION, CROSS JOIN, or a 1-to-many join.
Assuming that "onlne" is included in each of your groups, here is a (completely untested) method of producing your results.
SELECT *
FROM Section_Schedules
CROSS APPLY (
SELECT 'a'
WHERE Section_Schedules.Loc_Cde IN (group1) -- whatever your group1 is including 'onlne'
UNION
SELECT 'b'
WHERE Section_Schedules.Loc_Cde IN (group2) -- whatever your group2 is including 'onlne'
UNION
SELECT 'c'
WHERE Section_Schedules.Loc_Cde IN (group3) -- whatever your group3 is including 'onlne'
-- etc., etc.,
) AS Sort(Sort)
Drew
Edited: To correct syntax error. That's the problem with not having sample data to test with.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 28, 2011 at 9:36 am
I. LOVE. you guys. I'm not just saying that....
Weeping tears of joy. Endless thanks, etc etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply