Gap data for attendance

• kangarolf

  Hall of Fame

  Points: 3432

  More actions

  June 27, 2018 at 3:10 am

  #371333

  Hi all,
  Thanks in advance for any help.
  I have been asked to produce a CSV which shows current session bookings but they want to show gaps.
  The script below creates the sort of table structure we have. There is a row for each session with the maximum number of slots available and then another table with a row for each person that has joined a session.
  I want to return something like this

  First session, slot 1, person
  First session, slot 2, person
  First session, slot 3, person
  First session, slot 4, EMPTY
  First session, slot 5, EMPTY
  I've tried a few things with a tally/numbers table but havent got anywhere yet!
  Thanks
  Rolf

  
CREATE TABLE SESSIONS(ID INT, SESSIONNAME VARCHAR(250), MAXSLOTS INT)

  insert into SESSIONS
select 1, 'First Session', 5
union all
select 2, 'Second Session',3
union all
select 3, 'Third Session',3

  CREATE TABLE SLOTS(ID INT, SESSIONID INT, NAME VARCHAR(250) )

  insert into SLOTS
select 1, 1, 'First Person'
union all
select 2, 1, 'Second Person'
union all
select 3, 1, 'Third Person'
union all
select 4, 2, 'Third Person'
union all
select 5, 3, 'Third Person'
union all
select 6, 2, 'First Person'
union all
select 7, 2, 'Second Person'
union all
select 3, 3, 'First Person'
union all
select 3, 3, 'Second Person'

  SELECT * FROM SESSIONS
SELECT * FROM SLOTS ORDER BY SESSIONID, NAME
SELECT * FROM SESSIONS INNER JOIN SLOTS ON SLOTS.SESSIONID = SESSIONS.ID ORDER BY SESSIONS.ID, SLOTS.NAME

  DROP TABLE SESSIONS
DROP TABLE SLOTS

• Thom A

  SSC Guru

  Points: 99130

  More actions

  June 27, 2018 at 5:42 am

  #1995422

  You've included DDL and INSERT statement, which is great(!), however, you haven't included any details of what your expected output is here. Could you show us what data you would want to obtain/output from the sample data you have?

  Thanks.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• kangarolf

  Hall of Fame

  Points: 3432

  More actions

  June 27, 2018 at 5:57 am

  #1995424

  Hi there,
  Something like this,

  SESSIONNAME, SLOTNUMBER, FULL, PERSON
  First session, 1, 1, First Person
  First session, 2, 1, Second Person
  First session, 3, 1, Third Person
  First session, 4, 0, EMPTY
  First session, 5, 0, EMPTY

  But for all possible sessions and slots. I'm not sure if I have created this in the correct format.
  Thanks
  Rolf

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 27, 2018 at 7:16 am

  #1995434

  I came up with this:
  
CREATE TABLE SESSIONS(ID INT, SESSIONNAME VARCHAR(250), MAXSLOTS INT);

  insert into SESSIONS
  select 1, 'First Session', 5
  union all
  select 2, 'Second Session',3
  union all
  select 3, 'Third Session',3;

  
  CREATE TABLE SLOTS(ID INT, SESSIONID INT, NAME VARCHAR(250) );

  insert into SLOTS
  select 1, 1, 'First Person'
  union all
  select 2, 1, 'Second Person'
  union all
  select 3, 1, 'Third Person'
  union all
  select 4, 2, 'Third Person'
  union all
  select 5, 3, 'Third Person'
  union all
  select 6, 2, 'First Person'
  union all
  select 7, 2, 'Second Person'
  union all
  select 3, 3, 'First Person'
  union all
  select 3, 3, 'Second Person';

  WITH eTally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n))
  , SessionSlots AS (
  SELECT
    SessionName = .[SESSIONNAME]
    , SessionId = .[ID]
    , SlotId = [ca1].[n]
  FROM
    [dbo].[SESSIONS] AS
    CROSS APPLY (SELECT TOP (.[MAXSLOTS]) n FROM [eTally])ca1(n)
  ), SlotAssignments AS (
  SELECT
    PersonName = .[NAME]
    , SessionId = .[SESSIONID]
    , AssignedSlot = ROW_NUMBER() OVER (PARTITION BY .[SESSIONID] ORDER BY .[NAME])
  FROM
    [dbo].[SLOTS] AS )  --SELECT * FROM [SlotAssignments]
  SELECT
    [ss].[SessionName]
    , [ss].[SlotId]
    , SlotFull = CASE WHEN [sa].[PersonName] IS NULL THEN 0 ELSE 1 END
    , Person = ISNULL([sa].[PersonName],'Empty') 
  FROM
    [SessionSlots] AS [ss]
    LEFT OUTER JOIN [SlotAssignments] AS [sa]
      ON ([ss].[SessionId] = [sa].[SessionId] AND [ss].[SlotId] = [sa].[AssignedSlot]);

  DROP TABLE SESSIONS;
  DROP TABLE SLOTS;
  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 27, 2018 at 7:32 am

  #1995440

  Lynn Pettis - Wednesday, June 27, 2018 7:15 AM

  I came up with this:
  
CREATE TABLE SESSIONS(ID INT, SESSIONNAME VARCHAR(250), MAXSLOTS INT);

  insert into SESSIONS
  select 1, 'First Session', 5
  union all
  select 2, 'Second Session',3
  union all
  select 3, 'Third Session',3;

  
  CREATE TABLE SLOTS(ID INT, SESSIONID INT, NAME VARCHAR(250) );

  insert into SLOTS
  select 1, 1, 'First Person'
  union all
  select 2, 1, 'Second Person'
  union all
  select 3, 1, 'Third Person'
  union all
  select 4, 2, 'Third Person'
  union all
  select 5, 3, 'Third Person'
  union all
  select 6, 2, 'First Person'
  union all
  select 7, 2, 'Second Person'
  union all
  select 3, 3, 'First Person'
  union all
  select 3, 3, 'Second Person';

  WITH eTally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n))
  , SessionSlots AS (
  SELECT
    SessionName = .[SESSIONNAME]
    , SessionId = .[ID]
    , SlotId = [ca1].[n]
  FROM
    [dbo].[SESSIONS] AS
    CROSS APPLY (SELECT TOP (.[MAXSLOTS]) n FROM [eTally])ca1(n)
  ), SlotAssignments AS (
  SELECT
    PersonName = .[NAME]
    , SessionId = .[SESSIONID]
    , AssignedSlot = ROW_NUMBER() OVER (PARTITION BY .[SESSIONID] ORDER BY .[NAME])
  FROM
    [dbo].[SLOTS] AS )  --SELECT * FROM [SlotAssignments]
  SELECT
    [ss].[SessionName]
    , [ss].[SlotId]
    , SlotFull = CASE WHEN [sa].[PersonName] IS NULL THEN 0 ELSE 1 END
    , Person = ISNULL([sa].[PersonName],'Empty') 
  FROM
    [SessionSlots] AS [ss]
    LEFT OUTER JOIN [SlotAssignments] AS [sa]
      ON ([ss].[SessionId] = [sa].[SessionId] AND [ss].[SlotId] = [sa].[AssignedSlot]);

  DROP TABLE SESSIONS;
  DROP TABLE SLOTS;
  

  It's Awesome

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 27, 2018 at 7:32 am

  #1995441

  Lynn Pettis - Wednesday, June 27, 2018 7:15 AM

  I came up with this:
  
CREATE TABLE SESSIONS(ID INT, SESSIONNAME VARCHAR(250), MAXSLOTS INT);

  insert into SESSIONS
  select 1, 'First Session', 5
  union all
  select 2, 'Second Session',3
  union all
  select 3, 'Third Session',3;

  
  CREATE TABLE SLOTS(ID INT, SESSIONID INT, NAME VARCHAR(250) );

  insert into SLOTS
  select 1, 1, 'First Person'
  union all
  select 2, 1, 'Second Person'
  union all
  select 3, 1, 'Third Person'
  union all
  select 4, 2, 'Third Person'
  union all
  select 5, 3, 'Third Person'
  union all
  select 6, 2, 'First Person'
  union all
  select 7, 2, 'Second Person'
  union all
  select 3, 3, 'First Person'
  union all
  select 3, 3, 'Second Person';

  WITH eTally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n))
  , SessionSlots AS (
  SELECT
    SessionName = .[SESSIONNAME]
    , SessionId = .[ID]
    , SlotId = [ca1].[n]
  FROM
    [dbo].[SESSIONS] AS
    CROSS APPLY (SELECT TOP (.[MAXSLOTS]) n FROM [eTally])ca1(n)
  ), SlotAssignments AS (
  SELECT
    PersonName = .[NAME]
    , SessionId = .[SESSIONID]
    , AssignedSlot = ROW_NUMBER() OVER (PARTITION BY .[SESSIONID] ORDER BY .[NAME])
  FROM
    [dbo].[SLOTS] AS )  --SELECT * FROM [SlotAssignments]
  SELECT
    [ss].[SessionName]
    , [ss].[SlotId]
    , SlotFull = CASE WHEN [sa].[PersonName] IS NULL THEN 0 ELSE 1 END
    , Person = ISNULL([sa].[PersonName],'Empty') 
  FROM
    [SessionSlots] AS [ss]
    LEFT OUTER JOIN [SlotAssignments] AS [sa]
      ON ([ss].[SessionId] = [sa].[SessionId] AND [ss].[SlotId] = [sa].[AssignedSlot]);

  DROP TABLE SESSIONS;
  DROP TABLE SLOTS;
  

  Why it is posted multiple times and I am not able to delete it

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 27, 2018 at 7:32 am

  #1995442

  Lynn Pettis - Wednesday, June 27, 2018 7:15 AM

  I came up with this:
  
CREATE TABLE SESSIONS(ID INT, SESSIONNAME VARCHAR(250), MAXSLOTS INT);

  insert into SESSIONS
  select 1, 'First Session', 5
  union all
  select 2, 'Second Session',3
  union all
  select 3, 'Third Session',3;

  
  CREATE TABLE SLOTS(ID INT, SESSIONID INT, NAME VARCHAR(250) );

  insert into SLOTS
  select 1, 1, 'First Person'
  union all
  select 2, 1, 'Second Person'
  union all
  select 3, 1, 'Third Person'
  union all
  select 4, 2, 'Third Person'
  union all
  select 5, 3, 'Third Person'
  union all
  select 6, 2, 'First Person'
  union all
  select 7, 2, 'Second Person'
  union all
  select 3, 3, 'First Person'
  union all
  select 3, 3, 'Second Person';

  WITH eTally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt1(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt2(n))
  , SessionSlots AS (
  SELECT
    SessionName = .[SESSIONNAME]
    , SessionId = .[ID]
    , SlotId = [ca1].[n]
  FROM
    [dbo].[SESSIONS] AS
    CROSS APPLY (SELECT TOP (.[MAXSLOTS]) n FROM [eTally])ca1(n)
  ), SlotAssignments AS (
  SELECT
    PersonName = .[NAME]
    , SessionId = .[SESSIONID]
    , AssignedSlot = ROW_NUMBER() OVER (PARTITION BY .[SESSIONID] ORDER BY .[NAME])
  FROM
    [dbo].[SLOTS] AS )  --SELECT * FROM [SlotAssignments]
  SELECT
    [ss].[SessionName]
    , [ss].[SlotId]
    , SlotFull = CASE WHEN [sa].[PersonName] IS NULL THEN 0 ELSE 1 END
    , Person = ISNULL([sa].[PersonName],'Empty') 
  FROM
    [SessionSlots] AS [ss]
    LEFT OUTER JOIN [SlotAssignments] AS [sa]
      ON ([ss].[SessionId] = [sa].[SessionId] AND [ss].[SlotId] = [sa].[AssignedSlot]);

  DROP TABLE SESSIONS;
  DROP TABLE SLOTS;
  

  It's Awesome

  Saravanan

• Thom A

  SSC Guru

  Points: 99130

  More actions

  June 27, 2018 at 7:38 am

  #1995446

  saravanatn - Wednesday, June 27, 2018 7:32 AM

  Why it is posted multiple times and I am not able to delete it

  You can't delete post on SSC after you've posted them. I'd suggest, however, the reason there are 3 posts is because you clicking the "Post reply" button multiple (3) times (possibly in impatience 😉 ).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• kangarolf

  Hall of Fame

  Points: 3432

  More actions

  June 27, 2018 at 7:51 am

  #1995447

  Thank you that works on the test data, I'll give it some real world examples.
  I really need to learn more about the over by clause!

  Rolf

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 27, 2018 at 8:06 am

  #1995454

  Thom A - Wednesday, June 27, 2018 7:38 AM

  saravanatn - Wednesday, June 27, 2018 7:32 AM

  Why it is posted multiple times and I am not able to delete it

  You can't delete post on SSC after you've posted them. I'd suggest, however, the reason there are 3 posts is because you clicking the "Post reply" button multiple (3) times (possibly in impatience 😉 ).

  Yes your are right

  Saravanan