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

• Headlock Mike

  SSC Veteran

  Points: 225

  More actions

  May 20, 2013 at 2:24 pm

  #294167

  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

• Kurt W. Zimmerman

  SSCrazy Eights

  Points: 8676

  More actions

  May 20, 2013 at 2:27 pm

  #1616783

  Is this a homework assignment?

  Kurt

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

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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 20, 2013 at 2:28 pm

  #1616784

  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/

• Headlock Mike

  SSC Veteran

  Points: 225

  More actions

  May 20, 2013 at 2:52 pm

  #1616793

  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'

• mickyT

  SSChampion

  Points: 10360

  More actions

  May 20, 2013 at 6:41 pm

  #1616821

  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;

• geoff5

  Hall of Fame

  Points: 3560

  More actions

  May 21, 2013 at 9:02 am

  #1617065

  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.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 21, 2013 at 9:06 am

  #1617072

  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/

• Headlock Mike

  SSC Veteran

  Points: 225

  More actions

  May 22, 2013 at 7:59 am

  #1617444

  Thanks Micky! This is spot on.

• mickyT

  SSChampion

  Points: 10360

  More actions

  May 22, 2013 at 1:58 pm

  #1617572

  You're welcome