Gap data for attendance

Question

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

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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 · Answer 7

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 · Answer 8

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 · Answer 9

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