Very difficult query, not sure how to handle without a cursor.

  • I have a scenario here I have been wrestling over off and on for weeks.

    We have to tables, one lists Classes, and one lists Entry Codes for certain classes.

    DECLARE @Class TABLE (ClassID char(8), CourseID varchar(10), ClassCapacity int)

    INSERT INTO @Class (ClassID, CourseID, ClassCapacity)

    SELECT '3520B014','ENGL&101',25

    UNION ALL

    SELECT '3525B014','ENGL&101',25

    UNION ALL

    SELECT '3530B014','ENGL&101',25

    DECLARE @EntryCode TABLE (EntryCodeID varchar(9), CourseID varchar(10))

    INSERT INTO @EntryCode (EntryCodeID, CourseID)

    SELECT '10515B014','ENGL&101'

    UNION ALL

    SELECT '10594B014','ENGL&101'

    UNION ALL

    SELECT '10625B014','ENGL&101'

    UNION ALL

    SELECT '10727B014','ENGL&101'

    UNION ALL

    SELECT '10736B014','ENGL&101'

    UNION ALL

    SELECT '10790B014','ENGL&101'

    UNION ALL

    SELECT '11092B014','ENGL&101'

    UNION ALL

    SELECT '11228B014','ENGL&101'

    UNION ALL

    SELECT '11627B014','ENGL&101'

    UNION ALL

    SELECT '11826B014','ENGL&101'

    UNION ALL

    SELECT '12562B014','ENGL&101'

    UNION ALL

    SELECT '12767B014','ENGL&101'

    UNION ALL

    SELECT '12777B014','ENGL&101'

    UNION ALL

    SELECT '12936B014','ENGL&101'

    UNION ALL

    SELECT '13229B014','ENGL&101'

    UNION ALL

    SELECT '13451B014','ENGL&101'

    UNION ALL

    SELECT '13478B014','ENGL&101'

    UNION ALL

    SELECT '13778B014','ENGL&101'

    UNION ALL

    SELECT '13907B014','ENGL&101'

    UNION ALL

    SELECT '13977B014','ENGL&101'

    UNION ALL

    SELECT '14004B014','ENGL&101'

    UNION ALL

    SELECT '14010B014','ENGL&101'

    UNION ALL

    SELECT '14134B014','ENGL&101'

    UNION ALL

    SELECT '14261B014','ENGL&101'

    UNION ALL

    SELECT '14273B014','ENGL&101'

    UNION ALL

    SELECT '14311B014','ENGL&101'

    UNION ALL

    SELECT '14463B014','ENGL&101'

    UNION ALL

    SELECT '15060B014','ENGL&101'

    UNION ALL

    SELECT '15741B014','ENGL&101'

    UNION ALL

    SELECT '15787B014','ENGL&101'

    UNION ALL

    SELECT '15892B014','ENGL&101'

    UNION ALL

    SELECT '16148B014','ENGL&101'

    UNION ALL

    SELECT '16374B014','ENGL&101'

    UNION ALL

    SELECT '16673B014','ENGL&101'

    UNION ALL

    SELECT '17067B014','ENGL&101'

    UNION ALL

    SELECT '17162B014','ENGL&101'

    UNION ALL

    SELECT '17201B014','ENGL&101'

    UNION ALL

    SELECT '17273B014','ENGL&101'

    UNION ALL

    SELECT '17396B014','ENGL&101'

    UNION ALL

    SELECT '17624B014','ENGL&101'

    UNION ALL

    SELECT '17659B014','ENGL&101'

    UNION ALL

    SELECT '17723B014','ENGL&101'

    UNION ALL

    SELECT '17894B014','ENGL&101'

    UNION ALL

    SELECT '18158B014','ENGL&101'

    UNION ALL

    SELECT '18291B014','ENGL&101'

    UNION ALL

    SELECT '18308B014','ENGL&101'

    UNION ALL

    SELECT '18387B014','ENGL&101'

    UNION ALL

    SELECT '18917B014','ENGL&101'

    UNION ALL

    SELECT '19046B014','ENGL&101'

    UNION ALL

    SELECT '20037B014','ENGL&101'

    UNION ALL

    SELECT '20188B014','ENGL&101'

    UNION ALL

    SELECT '20204B014','ENGL&101'

    UNION ALL

    SELECT '20297B014','ENGL&101'

    UNION ALL

    SELECT '20446B014','ENGL&101'

    UNION ALL

    SELECT '20526B014','ENGL&101'

    UNION ALL

    SELECT '20621B014','ENGL&101'

    UNION ALL

    SELECT '20651B014','ENGL&101'

    UNION ALL

    SELECT '20963B014','ENGL&101'

    UNION ALL

    SELECT '21088B014','ENGL&101'

    UNION ALL

    SELECT '21415B014','ENGL&101'

    UNION ALL

    SELECT '21517B014','ENGL&101'

    UNION ALL

    SELECT '21969B014','ENGL&101'

    UNION ALL

    SELECT '21983B014','ENGL&101'

    UNION ALL

    SELECT '22154B014','ENGL&101'

    UNION ALL

    SELECT '22514B014','ENGL&101'

    UNION ALL

    SELECT '22535B014','ENGL&101'

    UNION ALL

    SELECT '22552B014','ENGL&101'

    UNION ALL

    SELECT '22558B014','ENGL&101'

    UNION ALL

    SELECT '22598B014','ENGL&101'

    UNION ALL

    SELECT '23037B014','ENGL&101'

    UNION ALL

    SELECT '23397B014','ENGL&101'

    UNION ALL

    SELECT '23531B014','ENGL&101'

    UNION ALL

    SELECT '23567B014','ENGL&101'

    UNION ALL

    SELECT '24236B014','ENGL&101'

    UNION ALL

    SELECT '24382B014','ENGL&101'

    UNION ALL

    SELECT '24617B014','ENGL&101'

    UNION ALL

    SELECT '24711B014','ENGL&101'

    UNION ALL

    SELECT '24853B014','ENGL&101'

    UNION ALL

    SELECT '24963B014','ENGL&101'

    UNION ALL

    SELECT '25151B014','ENGL&101'

    UNION ALL

    SELECT '25169B014','ENGL&101'

    UNION ALL

    SELECT '25939B014','ENGL&101'

    UNION ALL

    SELECT '26015B014','ENGL&101'

    UNION ALL

    SELECT '26056B014','ENGL&101'

    UNION ALL

    SELECT '26147B014','ENGL&101'

    UNION ALL

    SELECT '26273B014','ENGL&101'

    UNION ALL

    SELECT '26560B014','ENGL&101'

    UNION ALL

    SELECT '26891B014','ENGL&101'

    UNION ALL

    SELECT '27035B014','ENGL&101'

    UNION ALL

    SELECT '27129B014','ENGL&101'

    UNION ALL

    SELECT '27448B014','ENGL&101'

    UNION ALL

    SELECT '27464B014','ENGL&101'

    UNION ALL

    SELECT '28025B014','ENGL&101'

    UNION ALL

    SELECT '28068B014','ENGL&101'

    UNION ALL

    SELECT '28101B014','ENGL&101'

    UNION ALL

    SELECT '28136B014','ENGL&101'

    UNION ALL

    SELECT '28873B014','ENGL&101'

    UNION ALL

    SELECT '28895B014','ENGL&101'

    UNION ALL

    SELECT '28993B014','ENGL&101'

    UNION ALL

    SELECT '29228B014','ENGL&101'

    UNION ALL

    SELECT '29558B014','ENGL&101'

    UNION ALL

    SELECT '29680B014','ENGL&101'

    UNION ALL

    SELECT '29711B014','ENGL&101'

    UNION ALL

    SELECT '29715B014','ENGL&101'

    UNION ALL

    SELECT '29840B014','ENGL&101'

    CourseID is the join key between these two tables. EntryCodeID is the code itself.

    What we would like to do is select ClassCapacity + 10 entry codes to each ClassID. The EntryCodeIDs must be unique, must not overlap into other ClassIDs.

    Is there a way accomplish this without a cursor? Iā€™m thinking a CTE might work, but I have never been able to completely wrap my head around their syntax.

    Suggestions? Running on SQL 2008 R2.

    Thank you in advance!

    Mike

  • Is this a homework assignment?

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Hi and welcome to SSC. Can you turn this into readily consumable ddl (create table statements) and sample data (insert statements)? This can absolutely be done without a cursor but I can't quite get what you are trying to do here. Take a few minutes and read the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 ā€“ Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is not a homework assignment. I work at a community college and this is a report that is currently manually edited and distributed via word documents to all instructors who teach classes requiring entry codes. We are hoping to automate the distribution by using data driven email subscriptions in SSRS.

    My apologies for not using proper posting etiquette. Below is the sample data. Three classes with a capacity of 25, 105 entry codes (25+10 x 3)

    The goal is to create a report which in effect assigns entry codes to classes.

    Ideally, it would be something like this, grouped by ClassID:

    3525B014 (capacity of 25)

    -10515B014

    -10594B014

    -10625B014

    -10727B014

    -10736B014

    -10790B014

    -11092B014

    -11228B014

    .... (continues listing of 35 codes)

    DECLARE @Class TABLE (ClassID char(8), CourseID varchar(10), ClassCapacity int)

    INSERT INTO @Class (ClassID, CourseID, ClassCapacity)

    SELECT '3520B014','ENGL&101',25

    UNION ALL

    SELECT '3525B014','ENGL&101',25

    UNION ALL

    SELECT '3530B014','ENGL&101',25

    DECLARE @EntryCode TABLE (EntryCodeID varchar(9), CourseID varchar(10))

    INSERT INTO @EntryCode (EntryCodeID, CourseID)

    SELECT '10515B014','ENGL&101'

    UNION ALL

    SELECT '10594B014','ENGL&101'

    UNION ALL

    SELECT '10625B014','ENGL&101'

    UNION ALL

    SELECT '10727B014','ENGL&101'

    UNION ALL

    SELECT '10736B014','ENGL&101'

    UNION ALL

    SELECT '10790B014','ENGL&101'

    UNION ALL

    SELECT '11092B014','ENGL&101'

    UNION ALL

    SELECT '11228B014','ENGL&101'

    UNION ALL

    SELECT '11627B014','ENGL&101'

    UNION ALL

    SELECT '11826B014','ENGL&101'

    UNION ALL

    SELECT '12562B014','ENGL&101'

    UNION ALL

    SELECT '12767B014','ENGL&101'

    UNION ALL

    SELECT '12777B014','ENGL&101'

    UNION ALL

    SELECT '12936B014','ENGL&101'

    UNION ALL

    SELECT '13229B014','ENGL&101'

    UNION ALL

    SELECT '13451B014','ENGL&101'

    UNION ALL

    SELECT '13478B014','ENGL&101'

    UNION ALL

    SELECT '13778B014','ENGL&101'

    UNION ALL

    SELECT '13907B014','ENGL&101'

    UNION ALL

    SELECT '13977B014','ENGL&101'

    UNION ALL

    SELECT '14004B014','ENGL&101'

    UNION ALL

    SELECT '14010B014','ENGL&101'

    UNION ALL

    SELECT '14134B014','ENGL&101'

    UNION ALL

    SELECT '14261B014','ENGL&101'

    UNION ALL

    SELECT '14273B014','ENGL&101'

    UNION ALL

    SELECT '14311B014','ENGL&101'

    UNION ALL

    SELECT '14463B014','ENGL&101'

    UNION ALL

    SELECT '15060B014','ENGL&101'

    UNION ALL

    SELECT '15741B014','ENGL&101'

    UNION ALL

    SELECT '15787B014','ENGL&101'

    UNION ALL

    SELECT '15892B014','ENGL&101'

    UNION ALL

    SELECT '16148B014','ENGL&101'

    UNION ALL

    SELECT '16374B014','ENGL&101'

    UNION ALL

    SELECT '16673B014','ENGL&101'

    UNION ALL

    SELECT '17067B014','ENGL&101'

    UNION ALL

    SELECT '17162B014','ENGL&101'

    UNION ALL

    SELECT '17201B014','ENGL&101'

    UNION ALL

    SELECT '17273B014','ENGL&101'

    UNION ALL

    SELECT '17396B014','ENGL&101'

    UNION ALL

    SELECT '17624B014','ENGL&101'

    UNION ALL

    SELECT '17659B014','ENGL&101'

    UNION ALL

    SELECT '17723B014','ENGL&101'

    UNION ALL

    SELECT '17894B014','ENGL&101'

    UNION ALL

    SELECT '18158B014','ENGL&101'

    UNION ALL

    SELECT '18291B014','ENGL&101'

    UNION ALL

    SELECT '18308B014','ENGL&101'

    UNION ALL

    SELECT '18387B014','ENGL&101'

    UNION ALL

    SELECT '18917B014','ENGL&101'

    UNION ALL

    SELECT '19046B014','ENGL&101'

    UNION ALL

    SELECT '20037B014','ENGL&101'

    UNION ALL

    SELECT '20188B014','ENGL&101'

    UNION ALL

    SELECT '20204B014','ENGL&101'

    UNION ALL

    SELECT '20297B014','ENGL&101'

    UNION ALL

    SELECT '20446B014','ENGL&101'

    UNION ALL

    SELECT '20526B014','ENGL&101'

    UNION ALL

    SELECT '20621B014','ENGL&101'

    UNION ALL

    SELECT '20651B014','ENGL&101'

    UNION ALL

    SELECT '20963B014','ENGL&101'

    UNION ALL

    SELECT '21088B014','ENGL&101'

    UNION ALL

    SELECT '21415B014','ENGL&101'

    UNION ALL

    SELECT '21517B014','ENGL&101'

    UNION ALL

    SELECT '21969B014','ENGL&101'

    UNION ALL

    SELECT '21983B014','ENGL&101'

    UNION ALL

    SELECT '22154B014','ENGL&101'

    UNION ALL

    SELECT '22514B014','ENGL&101'

    UNION ALL

    SELECT '22535B014','ENGL&101'

    UNION ALL

    SELECT '22552B014','ENGL&101'

    UNION ALL

    SELECT '22558B014','ENGL&101'

    UNION ALL

    SELECT '22598B014','ENGL&101'

    UNION ALL

    SELECT '23037B014','ENGL&101'

    UNION ALL

    SELECT '23397B014','ENGL&101'

    UNION ALL

    SELECT '23531B014','ENGL&101'

    UNION ALL

    SELECT '23567B014','ENGL&101'

    UNION ALL

    SELECT '24236B014','ENGL&101'

    UNION ALL

    SELECT '24382B014','ENGL&101'

    UNION ALL

    SELECT '24617B014','ENGL&101'

    UNION ALL

    SELECT '24711B014','ENGL&101'

    UNION ALL

    SELECT '24853B014','ENGL&101'

    UNION ALL

    SELECT '24963B014','ENGL&101'

    UNION ALL

    SELECT '25151B014','ENGL&101'

    UNION ALL

    SELECT '25169B014','ENGL&101'

    UNION ALL

    SELECT '25939B014','ENGL&101'

    UNION ALL

    SELECT '26015B014','ENGL&101'

    UNION ALL

    SELECT '26056B014','ENGL&101'

    UNION ALL

    SELECT '26147B014','ENGL&101'

    UNION ALL

    SELECT '26273B014','ENGL&101'

    UNION ALL

    SELECT '26560B014','ENGL&101'

    UNION ALL

    SELECT '26891B014','ENGL&101'

    UNION ALL

    SELECT '27035B014','ENGL&101'

    UNION ALL

    SELECT '27129B014','ENGL&101'

    UNION ALL

    SELECT '27448B014','ENGL&101'

    UNION ALL

    SELECT '27464B014','ENGL&101'

    UNION ALL

    SELECT '28025B014','ENGL&101'

    UNION ALL

    SELECT '28068B014','ENGL&101'

    UNION ALL

    SELECT '28101B014','ENGL&101'

    UNION ALL

    SELECT '28136B014','ENGL&101'

    UNION ALL

    SELECT '28873B014','ENGL&101'

    UNION ALL

    SELECT '28895B014','ENGL&101'

    UNION ALL

    SELECT '28993B014','ENGL&101'

    UNION ALL

    SELECT '29228B014','ENGL&101'

    UNION ALL

    SELECT '29558B014','ENGL&101'

    UNION ALL

    SELECT '29680B014','ENGL&101'

    UNION ALL

    SELECT '29711B014','ENGL&101'

    UNION ALL

    SELECT '29715B014','ENGL&101'

    UNION ALL

    SELECT '29840B014','ENGL&101'

  • Not sure if I have your requirement right for this, but here's a start.

    This will fill each class to capacity before moving to the next. If you want an even spread then a few more calculations will be required

    with cte1 as (

    SELECT CourseID,

    ClassID,

    ClassCapacity,

    -- Number the classes per course

    ROW_NUMBER() OVER (PARTITION BY CourseID ORDER BY ClassID) classNum,

    -- count of the classes in the course

    COUNT(ClassID) OVER (PARTITION BY CourseID) cntClass

    FROM #Class c

    )

    ,cte2 as (

    SELECT CourseID,

    ClassID,

    EntryCodeID,

    classNum,

    -- group the entries for each class

    ((ROW_NUMBER() OVER (PARTITION BY ClassID ORDER BY EntryCodeID) - 1) / (ClassCapacity + 10)) + 1 classGrp

    FROM cte1 c

    CROSS APPLY (SELECT TOP (cntClass * (ClassCapacity + 10)) EntryCodeID FROM #EntryCode ec WHERE ec.CourseID = c.CourseID) e -- get the maximum amount of entries that will fit the course

    )

    SELECT CourseID, ClassID, EntryCodeID

    FROM cte2

    WHERE classNum = classGrp

    ORDER BY CourseID, ClassID;

  • My solution uses a "tally table" in a CTE to explode the list of classes into a list where each class is listed multiple times, specifically ClassCapacity + 10 times, and then the rows are numbered consecutively within each CourseID. A second CTE takes all the available entry codes and numbers them consecutively, also within each CourseID.

    The main query then aligns the CourseID and the consecutively numbered rows, pulling out in the SELECT statement just the columns you need.

    with

    exploded_classes as

    (select ClassID,

    CourseID,

    ClassCapacity,

    class_iteration = ROW_NUMBER() over (partition by CourseID order by ClassID)

    from @Class inner join

    (select top 4000 N = row_number() over (order by (select null)) from master.sys.all_columns) as tally on tally.N <= ClassCapacity + 10),

    numbered_codes as

    (select EntryCodeID,

    CourseID,

    code_iteration = ROW_NUMBER() over (partition by CourseID order by EntryCodeID)

    from @EntryCode)

    select exploded_classes.ClassID,

    exploded_classes.CourseID,

    numbered_codes.EntryCodeID

    from exploded_classes inner join

    numbered_codes on exploded_classes.CourseID = numbered_codes.CourseID and exploded_classes.class_iteration = numbered_codes.code_iteration

    By the way, the 4000 in the tally table is just an artifact from another use I was making of the same tally table subquery. It can likely be reduced greatly to just the maximum number of iterations needed to cover the max(ClassCapacity) + 10 value.

    Also, the tally table is a great way to solve problems like this one. I recommend searching for Jeff Moden's articles on the topic, as it is from him that I learned great tips on how to make and to use one.

  • You can find Jeff's tally table article here.

    http://www.sqlservercentral.com/articles/62867/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 ā€“ Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Micky! This is spot on.

  • You're welcome

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply