Groups of Percentages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Groups of Percentages

Author

Topic

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-05 : 10:01:45

I need to get site names, total enrollments, total attendances, and a percentage of the attendances that are marked as 'present' within the fall 2013 session, preferably in a single query. I think I can figure how to make percentages for each site and individual attendee from there.

So far, my numbers are off, WAY off.
If I do a select count for EnrollmentId in the fall session I get 203

SELECT COUNT(EnrollmentsSet.Id)
FROM EnrollmentsSet
WHERE EnrollmentsSet.Session_Enrollments = 5
(203 rows)

When I add the Site names I get a different total of enrolled in the 8 rows that are returned

SELECT COUNT(EnrollmentsSet.Id) AS Enrolled, Sessions.SessionName, SitesSet.SiteName
FROM EnrollmentsSet INNER JOIN
Sessions ON EnrollmentsSet.Session_Enrollments = Sessions.Id INNER JOIN
CurrentSites ON EnrollmentsSet.Enrollments_CurrentSite = CurrentSites.Id AND Sessions.Id = CurrentSites.CurrentSite_Session INNER JOIN
SitesSet ON CurrentSites.CurrentSite_Sites = SitesSet.Id
WHERE (EnrollmentsSet.Session_Enrollments = 5)
GROUP BY Sessions.SessionName, SitesSet.SiteName

(8 rows, enrolled column sums at 201)

And I get even mor off when I try to figure attendances.

SELECT COUNT(EnrollmentsSet.Id) AS Enrolled, Sessions.SessionName, SitesSet.SiteName, COUNT(Attendances.Id) AS [Total Attendance]
FROM EnrollmentsSet INNER JOIN
Sessions ON EnrollmentsSet.Session_Enrollments = Sessions.Id INNER JOIN
CurrentSites ON EnrollmentsSet.Enrollments_CurrentSite = CurrentSites.Id AND Sessions.Id = CurrentSites.CurrentSite_Session INNER JOIN
SitesSet ON CurrentSites.CurrentSite_Sites = SitesSet.Id Right OUTER JOIN
Attendances ON EnrollmentsSet.Id = Attendances.Enrollments_Attendance
WHERE (EnrollmentsSet.Session_Enrollments = 5)
GROUP BY Sessions.SessionName, SitesSet.SiteName

(5 rows, but now the attendance and the enrollments are equal with the enrollments increasing to match attendances.)

It doesn't matter which way I alter the joins, Left right, or not the enrollments are way too big.
Please help.

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-05 : 10:04:23

Oh yes, I am using sql 2012

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 10:50:22

are you looking at tis?


SELECT DISTINCT COUNT(EnrollmentsSet.Id) OVER () AS Enrolled, Sessions.SessionName, SitesSet.SiteName, COUNT(Attendances.Id) OVER () AS [Total Attendance]
FROM EnrollmentsSet INNER JOIN
Sessions ON EnrollmentsSet.Session_Enrollments = Sessions.Id INNER JOIN
CurrentSites ON EnrollmentsSet.Enrollments_CurrentSite = CurrentSites.Id AND Sessions.Id = CurrentSites.CurrentSite_Session INNER JOIN
SitesSet ON CurrentSites.CurrentSite_Sites = SitesSet.Id Right OUTER JOIN
Attendances ON EnrollmentsSet.Id = Attendances.Enrollments_Attendance
WHERE (EnrollmentsSet.Session_Enrollments = 5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-05 : 11:02:44

@visakh16 Sorry, that makes the numbers even more outrageous.
I have have sensible counts when I am working with the enrollmentsSet table, even when I add the SiteSet joins. It gets out of whack when I add the Attendances Join. It is a one to many with the EnrollmentsSet as a foreign key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 12:36:00

then


SELECT DISTINCT COUNT(1) OVER (PARTITION BY EnrollmentsSet.Id) AS Enrolled, Sessions.SessionName, SitesSet.SiteName, COUNT(Attendances.Id) OVER () AS [Total Attendance]
FROM EnrollmentsSet INNER JOIN
Sessions ON EnrollmentsSet.Session_Enrollments = Sessions.Id INNER JOIN
CurrentSites ON EnrollmentsSet.Enrollments_CurrentSite = CurrentSites.Id AND Sessions.Id = CurrentSites.CurrentSite_Session INNER JOIN
SitesSet ON CurrentSites.CurrentSite_Sites = SitesSet.Id Right OUTER JOIN
Attendances ON EnrollmentsSet.Id = Attendances.Enrollments_Attendance
WHERE (EnrollmentsSet.Session_Enrollments = 5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-06 : 03:26:33

As you can see by the sample data below, the counts are way off. The Enrolled should equal around 203 records and the attendance total should be different amounts per site.

Enrolled SessionName SiteName Total Attendance
1 Fall 2013 Park (QST cts) 1263
7 Fall 2013 Bowie High 1263
7 Fall 2013 Park (QST cts) 1263
8 Fall 2013 High School 1263
8 Fall 2013 Special P 1263
8 Fall 2013 Park (QST cts) 1263
8 Fall 2013 High School 1263

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 04:10:05

I cant see why they're way off as i dont know what is your expected output.
See how to post a question properly.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Once you do that we will get an idea of what you're after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-06 : 10:25:53

Ok, apologies for not posting properly.
How do I get attendance percentages based on the number of enrollments, number of related attendances, and 12 day session?

My tables and data are below along with what I have done so far. I have made progress, but much is lacking.

/****** Object: Table [dbo].[Attendances] Script Date: 11/6/2013 8:56:29 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Attendances](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [nvarchar](255) NOT NULL,
[AttendanceDate] [datetime] NOT NULL,
[Comments] [nvarchar](255) NULL,
[RowVersion] [timestamp] NOT NULL,
[Enrollments_Attendance] [int] NOT NULL,
CONSTRAINT [PK_Attendances] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[CurrentSites] Script Date: 11/6/2013 8:56:29 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CurrentSites](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[SiteDirector_CurrentSite] [int] NOT NULL,
[SubProgram_CurrentSite] [int] NOT NULL,
[CurrentSite_Sites] [int] NOT NULL,
[CurrentSite_Session] [int] NOT NULL,
CONSTRAINT [PK_CurrentSites] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[EnrollmentsSet] Script Date: 11/6/2013 8:56:29 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EnrollmentsSet](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EnrollmentDate] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[Session_Enrollments] [int] NOT NULL,
[SubProgram_Enrollments] [int] NOT NULL,
[Enrollments_CurrentSite] [int] NOT NULL,
[Enrollments_Individual] [int] NOT NULL,
CONSTRAINT [PK_EnrollmentsSet] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[SitesSet] Script Date: 11/6/2013 8:56:29 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SitesSet](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SiteName] [nvarchar](255) NOT NULL,
[Type] [nvarchar](255) NOT NULL,
[Phone] [nvarchar](255) NULL,
[Address] [nvarchar](255) NOT NULL,
[City] [nvarchar](255) NOT NULL,
[ZipCode] [nvarchar](255) NOT NULL,
[NumberOfCourts] [nvarchar](255) NULL,
[RowVersion] [timestamp] NOT NULL,
[States_Sites] [int] NOT NULL,
CONSTRAINT [PK_SitesSet] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Attendances] ON

INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (1, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 1)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (2, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 2)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (3, N'Present', CAST(0x0000A17700000000 AS DateTime), NULL, 3)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (4, N'Present', CAST(0x0000A17500000000 AS DateTime), N'Good Kid or something as a comment', 5)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (5, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (6, N'Present', CAST(0x0000A17500000000 AS DateTime), N'Great Practice', 7)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (7, N'Present', CAST(0x0000A17C00000000 AS DateTime), N'Excused due to illness', 5)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (8, N'Present', CAST(0x0000A17C00000000 AS DateTime), N'Played in a tournament', 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (9, N'Present', CAST(0x0000A17C00000000 AS DateTime), NULL, 7)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (10, N'Present', CAST(0x0000A18300000000 AS DateTime), NULL, 5)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (11, N'Present', CAST(0x0000A18300000000 AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (12, N'Present', CAST(0x0000A18300000000 AS DateTime), NULL, 7)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (13, N'Present', CAST(0x0000A17500000000 AS DateTime), N'Good player', 8)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (14, N'Excused Absence', CAST(0x0000A17500000000 AS DateTime), N'Car trouble', 9)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (15, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 10)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (16, N'Present', CAST(0x0000A17C00000000 AS DateTime), NULL, 9)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (17, N'Present', CAST(0x0000A17C00000000 AS DateTime), NULL, 10)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (18, N'Tardy', CAST(0x0000A18300000000 AS DateTime), NULL, 10)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (19, N'Present', CAST(0x0000A18A00000000 AS DateTime), NULL, 5)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (20, N'Tardy', CAST(0x0000A18A00000000 AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (21, N'Present', CAST(0x0000A18A00000000 AS DateTime), NULL, 7)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (22, N'Present', CAST(0x0000A17C00000000 AS DateTime), NULL, 8)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (23, N'Present', CAST(0x0000A18300000000 AS DateTime), N'Left cap at the courts. Placed it in my duffle bag.', 8)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (24, N'Tardy', CAST(0x0000A18A00000000 AS DateTime), N'Car trouble reported by parent.', 8)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (25, N'Present', CAST(0x0000A18300000000 AS DateTime), NULL, 9)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (26, N'Present', CAST(0x0000A18A00000000 AS DateTime), NULL, 9)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (27, N'Tardy', CAST(0x0000A18A00000000 AS DateTime), NULL, 10)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (28, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 11)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (29, N'Present', CAST(0x0000A17C00000000 AS DateTime), NULL, 11)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (30, N'Present', CAST(0x0000A18300000000 AS DateTime), NULL, 11)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (31, N'Present', CAST(0x0000A18A00000000 AS DateTime), NULL, 11)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (32, N'Play Day', CAST(0x0000A1910178219D AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (33, N'Play Day', CAST(0x0000A1980178603A AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (34, N'P', CAST(0x0000A1BD00000000 AS DateTime), NULL, 14)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (35, N'P', CAST(0x0000A1BD00000000 AS DateTime), NULL, 12)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (36, N'P', CAST(0x0000A1BD00000000 AS DateTime), NULL, 13)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (37, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 14)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (38, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 12)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (39, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 11)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (40, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 10)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (41, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 9)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (42, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 8)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (43, N'Excused Absence', CAST(0x0000A1BE00000000 AS DateTime), NULL, 7)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (44, N'Tardy', CAST(0x0000A1BE00000000 AS DateTime), NULL, 6)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (45, N'Present', CAST(0x0000A1BE00000000 AS DateTime), NULL, 5)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (46, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 13)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (47, N'Tardy', CAST(0x0000A17500000000 AS DateTime), NULL, 14)
INSERT [dbo].[Attendances] ([Id], [Type], [AttendanceDate], [Comments], [Enrollments_Attendance]) VALUES (48, N'Present', CAST(0x0000A17500000000 AS DateTime), NULL, 12)
SET IDENTITY_INSERT [dbo].[Attendances] OFF
SET IDENTITY_INSERT [dbo].[CurrentSites] ON

INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (1, 1, 1, 7, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (2, 1, 1, 10, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (3, 5, 2, 9, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (4, 5, 3, 9, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (5, 4, 4, 10, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (6, 4, 1, 12, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (7, 6, 1, 11, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (8, 10, 1, 3, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (9, 4, 1, 12, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (10, 1, 1, 10, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (11, 4, 4, 10, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (12, 5, 2, 9, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (13, 5, 3, 9, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (14, 2, 1, 2, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (15, 9, 1, 1, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (16, 8, 1, 3, 1)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (17, 6, 1, 11, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (18, 3, 1, 6, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (19, 3, 1, 5, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (20, 1, 1, 7, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (21, 11, 1, 8, 4)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (22, 3, 1, 13, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (23, 12, 1, 4, 3)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (24, 5, 2, 9, 4)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (25, 5, 2, 9, 4)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (26, 10, 5, 3, 4)
INSERT [dbo].[CurrentSites] ([Id], [SiteDirector_CurrentSite], [SubProgram_CurrentSite], [CurrentSite_Sites], [CurrentSite_Session]) VALUES (27, 12, 5, 4, 4)
SET IDENTITY_INSERT [dbo].[CurrentSites] OFF
SET IDENTITY_INSERT [dbo].[EnrollmentsSet] ON

INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (1, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A17600C5C100 AS DateTime), CAST(0x0000A17600E6B680 AS DateTime), 3, 1, 2, 178)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (2, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A17600C5C100 AS DateTime), CAST(0x0000A17600E6B680 AS DateTime), 3, 1, 2, 123)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (3, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A17600F73848 AS DateTime), CAST(0x0000A176011826C0 AS DateTime), 3, 1, 1, 182)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (4, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A17600E6B680 AS DateTime), CAST(0x0000A1760107AC00 AS DateTime), 3, 1, 2, 158)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (5, CAST(0x0000A15900000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 280)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (6, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 269)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (7, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 282)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (8, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 284)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (9, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 20)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (10, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), CAST(0x0000A18300D63BC0 AS DateTime), 3, 1, 23, 287)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (11, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A183009450C0 AS DateTime), CAST(0x0000A18300B54640 AS DateTime), 3, 1, 23, 290)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (12, CAST(0x0000A17000000000 AS DateTime), CAST(0x0000A1850128A180 AS DateTime), CAST(0x0000A18501499700 AS DateTime), 3, 4, 10, 21)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (13, CAST(0x0000A17500000000 AS DateTime), CAST(0x0000A18500FF6EA0 AS DateTime), CAST(0x0000A18501206420 AS DateTime), 3, 1, 20, 146)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (14, CAST(0x0000A17000000000 AS DateTime), CAST(0x0000A1850128A180 AS DateTime), CAST(0x0000A18501499700 AS DateTime), 3, 1, 10, 29)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (15, CAST(0x0000A1E00092537F AS DateTime), CAST(0x0000A1D800947E43 AS DateTime), CAST(0x0000A1D800AD450C AS DateTime), 4, 1, 15, 25)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (16, CAST(0x0000A1D90092E6A0 AS DateTime), CAST(0x0000A1D800947534 AS DateTime), CAST(0x0000A1D800AD491A AS DateTime), 4, 1, 15, 47)
INSERT [dbo].[EnrollmentsSet] ([Id], [EnrollmentDate], [StartTime], [EndTime], [Session_Enrollments], [SubProgram_Enrollments], [Enrollments_CurrentSite], [Enrollments_Individual]) VALUES (17, CAST(0x0000A1D90093956E AS DateTime), CAST(0x0000A1D800948780 AS DateTime), CAST(0x0000A1D800AD48CD AS DateTime), 4, 1, 15, 51)
SET IDENTITY_INSERT [dbo].[EnrollmentsSet] OFF
SET IDENTITY_INSERT [dbo].[SitesSet] ON

INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (1, N'Bowie High School', N'School', NULL, N'2101 Highbanks Dr', N'Arlington', N'75204', N'8', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (2, N'Dealy, George Bannerman', N'School', NULL, N'6501 Royal Ln', N'Dallas', N'75230', N'2', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (3, N'Desoto West Middle School', N'School', NULL, N'800 N Westmoreland Rd', N'DeSoto', N'75115', N'4', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (4, N'DTA Addison', N'Other', NULL, N'14679 Midway Rd', N'Addison', N'75001', N'2', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (5, N'Duncanville High School', N'School', N'9727083700', N'900 Camp Wisdom Rd', N'Duncanville', N'75116', N'9', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (6, N'Islamic Center of Irving', N'School', NULL, N'2555 Esters Rd', N'Irving', N'75062', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (7, N'J. W. Ray Elementary', N'School', N'9727947700', N'1949 North Washington Ave', N'Dallas', N'75204', N'2', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (8, N'Jubilee at Randall Park', N'Club', NULL, N'5880 Columgia Ave', N'Dallas', N'75214', N'4', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (9, N'Kiest (Tennis Center)', N'Parks & Recreation', N'2144317497', N'2202 West Kiest Blvd', N'Dallas', N'75228', N'12', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (10, N'Kiest Park (QST cts)', N'Parks & Recreation', NULL, N'3880 South Hampton', N'Dallas', N'75224', N'16', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (11, N'South Grand Prairie High School', N'School', NULL, N'301 Warrior Trail', N'Grand Prairie', N'75020', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (12, N'Vickery Meadows (Princeton Courts)', N'Parks & Recreation', NULL, N'6121 Melody Lane', N'Dallas', N'75231', N'4', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (13, N'Irving PAL', N'School', N'9726006300', N'900 O''Connor', N'Irving', N'75061', N'8', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (17, N'Kiest Park (QST)', N'Parks & Rec', N'972 387 1538', N'2200 W. Kiest Blvd', N'Dallas', N'75224', N'28', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (18, N'Samuell Grand Tennis Center', N'Parks & Rec', N'214-670-1374', N'6200 E. Grand Ave', N'Dallas', N'75223', N'20', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (19, N'Dealey', N'School', N'817-247-9977', N'6501 Royal Ln', N'Dallas', N'75230', N'2', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (20, N'Pleasant Oaks', N'Parks & Rec', NULL, N'8701 Greenmound', N'Dallas', N'75227', N'4', 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (21, N'Harry Stone', N'Parks & Rec', NULL, N'2403 Millmar', N'Dallas', N'75228', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (22, N'Kleberg-Rylie', N'Parks & Rec', NULL, N'1515 Edd', N'Dallas', N'75253', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (23, N'Marcus', N'Parks & Rec', NULL, N'3003 Northhaven', N'Dallas', N'75229', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (24, N'Fireside', N'Parks & Rec', NULL, N'8601 Fireside', N'Dallas', N'75217', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (25, N'Exline/Dunn', N'Parks & Rec', NULL, N'2525 Pine St.', N'Dallas', N'75215', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (26, N'Tommy Allen', N'Parks & Rec', NULL, N'7071 Bonnie View', N'Dallas', N'75241', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (27, N'Willie B Johnson', N'Parks & Rec', NULL, N'12225 Willowdell', N'Dallas', N'75243', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (28, N'Grauwyler', N'Parks & Rec', NULL, N'7780 Harry Hines', N'Dallas', N'75235', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (29, N'Larry Johnson', N'Parks & Rec', NULL, N'3700 Dixon', N'Dallas', N'75210', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (30, N'Reverchon', N'Parks & Rec', NULL, N'3505 Maple Ave', N'Dallas', N'75219', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (31, N'Janie C. Turner', N'Parks & Rec', NULL, N'6424 Elam', N'Dallas', N'75217', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (32, N'Polk/Craddock Park', N'Parks & Rec', NULL, N'6801 Roper', N'Dallas', N'75209', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (33, N'Walnut Hill', N'Parks & Rec', NULL, N'10011 Midway Rd', N'Dallas', N'75229', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (34, N'Churchill', N'Parks & Rec', NULL, N'6906 Churchill Dr', N'Dallas', N'75230', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (35, N'Umphress', N'Parks & Rec', NULL, N'7616 Umphress', N'Dallas', N'75217', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (36, N'Cummings', N'Parks & Rec', NULL, N'2976 Cummings', N'Dallas', N'75216', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (37, N'Singing Hills', N'Parks & Rec', NULL, N'1909 Crouch', N'Dallas', N'75241', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (38, N'J. C. Phelps', N'Parks & Rec', NULL, N'3030 Tips', N'Dallas', N'75216', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (39, N'Nash Davis', N'Parks & Rec', NULL, N'3710 Hampton', N'Dallas', N'75224', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (40, N'Fruitdale/Cummings', N'Parks & Rec', NULL, N'4408 Vandervort', N'Dallas', N'75216', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (41, N'Jaycee', N'Parks & Rec', NULL, N'3114 Clymer', N'Dallas', N'75212', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (42, N'J. J. Craft', N'Parks & Rec', NULL, N'4500 Spring', N'Dallas', N'75210', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (43, N'Martin Weiss', N'Parks & Rec', NULL, N'1111 Martindale', N'Dallas', N'75211', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (44, N'Thurgood Marshall', N'Parks & Rec', N'(214) 670-1928', N'5150 Mark Trail', N'Dallas', N'75232', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (45, N'Eloise Lundy', N'Parks & Rec', NULL, N'1229 Sabine', N'Dallas', N'75203', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (46, N'M. L. King', N'Parks & Rec', NULL, N'2922 M. L. King Blvd', N'Dallas', N'75215', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (47, N'Bayless / Fannin / Zaragoza', N'School', NULL, N'2444 Telegraph', N'Dallas', N'75228', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (48, N'Hotchkiss', N'School', N'214-431-7497', N'6929 Town North', N'Dallas', N'75238', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (49, N'Macon / Pease', N'School', N'214-431-7497', N'650 Holcomb', N'Dallas', N'75217', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (50, N'Reinhardt / H. Meadows', N'School', N'214-431-7497', N'10122 Losa Dr', N'Dallas', N'75238', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (51, N'Arlington Park', N'School', N'214-734-5052', N'5606 Wayside', N'Dallas', N'75235', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (52, N'Cowart', N'School', N'214-734-5052', N'1515 Ravinia', N'Dallas', N'75211', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (53, N'Kahn', N'School', NULL, N'610 Franklin', N'Dallas', N'75211', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (54, N'Winnetka', N'School', N'214-734-5052', N'1121 S. Edgefield', N'Dallas', N'75208', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (55, N'Kiest Park (10 & Under)', N'Park', NULL, N'3880 S. Hampton', N'Dallas', N'75224', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (56, N'Campbell Green', N'School', NULL, N'16600 Parkhill Dr.', N'Dallas', N'75248', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (57, N'Lake Highlands', N'Parks and Recreation', NULL, N'9940 Whiterock Tr', N'Dallas', N'75216', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (58, N'Ridgewood', N'Parks and Recreation', NULL, N'6818 Fisher Rd', N'Dallas', N'75214', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (59, N'Blanton', N'School', NULL, N'8915 Greenmound', N'Dallas', N'75227', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (60, N'Highland Meadows', N'School', NULL, N'8939 Whitewing Lane', N'Dallas', N'75238', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (61, N'Ignacio Zaragoza', N'School', NULL, N'4550 Worth St', N'Dallas', N'75246', NULL, 44)
INSERT [dbo].[SitesSet] ([Id], [SiteName], [Type], [Phone], [Address], [City], [ZipCode], [NumberOfCourts], [States_Sites]) VALUES (62, N'Scottish Rite Hospital', N'Club', NULL, N'2222 Welborn', N'Dallas', N'75219', NULL, 44)
SET IDENTITY_INSERT [dbo].[SitesSet] OFF
/****** Object: Index [UK_EnrollmentsSet] Script Date: 11/6/2013 8:56:29 AM ******/
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [UK_EnrollmentsSet] UNIQUE NONCLUSTERED
(
[Session_Enrollments] ASC,
[Enrollments_CurrentSite] ASC,
[Enrollments_Individual] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Attendances] ADD CONSTRAINT [DF_Attendances_Type] DEFAULT ('') FOR [Type]
GO
ALTER TABLE [dbo].[Attendances] ADD CONSTRAINT [DF_Attendances_AttendanceDate] DEFAULT ((0)) FOR [AttendanceDate]
GO
ALTER TABLE [dbo].[Attendances] ADD CONSTRAINT [DF_Attendances_Enrollments_Attendance] DEFAULT ((0)) FOR [Enrollments_Attendance]
GO
ALTER TABLE [dbo].[CurrentSites] ADD CONSTRAINT [DF_CurrentSites_SiteDirector_CurrentSite] DEFAULT ((0)) FOR [SiteDirector_CurrentSite]
GO
ALTER TABLE [dbo].[CurrentSites] ADD CONSTRAINT [DF_CurrentSites_SubProgram_CurrentSite] DEFAULT ((0)) FOR [SubProgram_CurrentSite]
GO
ALTER TABLE [dbo].[CurrentSites] ADD CONSTRAINT [DF_CurrentSites_CurrentSite_Sites] DEFAULT ((0)) FOR [CurrentSite_Sites]
GO
ALTER TABLE [dbo].[CurrentSites] ADD CONSTRAINT [DF_CurrentSites_CurrentSite_Session] DEFAULT ((0)) FOR [CurrentSite_Session]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_EnrollmentDate] DEFAULT ((0)) FOR [EnrollmentDate]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_StartTime] DEFAULT ((0)) FOR [StartTime]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_EndTime] DEFAULT ((0)) FOR [EndTime]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_Session_Enrollments] DEFAULT ((0)) FOR [Session_Enrollments]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_SubProgram_Enrollments] DEFAULT ((0)) FOR [SubProgram_Enrollments]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_Enrollments_CurrentSite] DEFAULT ((0)) FOR [Enrollments_CurrentSite]
GO
ALTER TABLE [dbo].[EnrollmentsSet] ADD CONSTRAINT [DF_EnrollmentsSet_Enrollments_Individual] DEFAULT ((0)) FOR [Enrollments_Individual]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_SiteName] DEFAULT ('') FOR [SiteName]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_Type] DEFAULT ('') FOR [Type]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_Address] DEFAULT ('') FOR [Address]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_City] DEFAULT ('') FOR [City]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_ZipCode] DEFAULT ('') FOR [ZipCode]
GO
ALTER TABLE [dbo].[SitesSet] ADD CONSTRAINT [DF_SitesSet_States_Sites] DEFAULT ((0)) FOR [States_Sites]
GO
ALTER TABLE [dbo].[Attendances] WITH CHECK ADD CONSTRAINT [Enrollments_Attendance] FOREIGN KEY([Enrollments_Attendance])
REFERENCES [dbo].[EnrollmentsSet] ([Id])
GO
ALTER TABLE [dbo].[Attendances] CHECK CONSTRAINT [Enrollments_Attendance]
GO
ALTER TABLE [dbo].[CurrentSites] WITH CHECK ADD CONSTRAINT [CurrentSite_Session] FOREIGN KEY([CurrentSite_Session])
REFERENCES [dbo].[Sessions] ([Id])
GO
ALTER TABLE [dbo].[CurrentSites] CHECK CONSTRAINT [CurrentSite_Session]
GO
ALTER TABLE [dbo].[CurrentSites] WITH CHECK ADD CONSTRAINT [CurrentSite_Sites] FOREIGN KEY([CurrentSite_Sites])
REFERENCES [dbo].[SitesSet] ([Id])
GO
ALTER TABLE [dbo].[CurrentSites] CHECK CONSTRAINT [CurrentSite_Sites]
GO
ALTER TABLE [dbo].[CurrentSites] WITH CHECK ADD CONSTRAINT [SiteDirector_CurrentSite] FOREIGN KEY([SiteDirector_CurrentSite])
REFERENCES [dbo].[SiteDirectors] ([Id])
GO
ALTER TABLE [dbo].[CurrentSites] CHECK CONSTRAINT [SiteDirector_CurrentSite]
GO
ALTER TABLE [dbo].[CurrentSites] WITH CHECK ADD CONSTRAINT [SubProgram_CurrentSite] FOREIGN KEY([SubProgram_CurrentSite])
REFERENCES [dbo].[SubPrograms] ([Id])
GO
ALTER TABLE [dbo].[CurrentSites] CHECK CONSTRAINT [SubProgram_CurrentSite]
GO
ALTER TABLE [dbo].[EnrollmentsSet] WITH CHECK ADD CONSTRAINT [Enrollments_CurrentSite] FOREIGN KEY([Enrollments_CurrentSite])
REFERENCES [dbo].[CurrentSites] ([Id])
GO
ALTER TABLE [dbo].[EnrollmentsSet] CHECK CONSTRAINT [Enrollments_CurrentSite]
GO
ALTER TABLE [dbo].[EnrollmentsSet] WITH CHECK ADD CONSTRAINT [Enrollments_Individual] FOREIGN KEY([Enrollments_Individual])
REFERENCES [dbo].[Individuals] ([Id])
GO
ALTER TABLE [dbo].[EnrollmentsSet] CHECK CONSTRAINT [Enrollments_Individual]
GO
ALTER TABLE [dbo].[EnrollmentsSet] WITH CHECK ADD CONSTRAINT [Session_Enrollments] FOREIGN KEY([Session_Enrollments])
REFERENCES [dbo].[Sessions] ([Id])
GO
ALTER TABLE [dbo].[EnrollmentsSet] CHECK CONSTRAINT [Session_Enrollments]
GO
ALTER TABLE [dbo].[EnrollmentsSet] WITH CHECK ADD CONSTRAINT [SubProgram_Enrollments] FOREIGN KEY([SubProgram_Enrollments])
REFERENCES [dbo].[SubPrograms] ([Id])
GO
ALTER TABLE [dbo].[EnrollmentsSet] CHECK CONSTRAINT [SubProgram_Enrollments]
GO
ALTER TABLE [dbo].[SitesSet] WITH CHECK ADD CONSTRAINT [States_Sites] FOREIGN KEY([States_Sites])
REFERENCES [dbo].[StatesSet] ([Id])
GO
ALTER TABLE [dbo].[SitesSet] CHECK CONSTRAINT [States_Sites]
GO

####################################################################################################

My Current DML:

;WITH Enrollments AS (SELECT en.Id, s.SiteName, COALESCE(COUNT (en.Id),0) AS EnrollmentCount
FROM dbo.EnrollmentsSet en --LEFT JOIN dbo.Attendances at ON en.Id = at.Enrollments_Attendance
INNER JOIN dbo.CurrentSites cs ON en.Enrollments_CurrentSite = cs.Id
INNER JOIN dbo.SitesSet s ON cs.CurrentSite_Sites = s.Id
WHERE en.Session_Enrollments = 3
GROUP BY s.SiteName, en.Id
),

Attendances AS (SELECT a.Enrollments_Attendance, COALESCE(COUNT(a.Id),0) AS AttendanceCount
FROM dbo.Attendances a LEFT JOIN dbo.EnrollmentsSet en ON a.Enrollments_Attendance = en.Id
WHERE en.Session_Enrollments = 3
GROUP BY a.Enrollments_Attendance
)
SELECT e.SiteName,
COALESCE(e.EnrollmentCount,0) AS EnrollmentCount,
COALESCE(a.AttendanceCount,0) AS AttendanceCount,
a.AttendanceCount / 12 * 100 AS [Session Attendance Percentage]
FROM Attendances a FULL JOIN Enrollments e ON a.Enrollments_Attendance = e.Id
ORDER BY e.SiteName
#####################################################################################################

My expression a.AttendanceCount / 12 * 100 AS [Session Attendance Percentage] gets zeros or nulls.

I expect to see something like

SiteName EnrollmentCount AttendanceCount Session Attendance Percentage
DTA Addison 1 5 42
DTA Addison 1 7 58
DTA Addison 1 5 42
DTA Addison 1 5 42

wdarnellg
Starting Member

14 Posts

Posted - 2013-11-06 : 15:36:28

Ok, GOT IT! Adding CAST(a.AttendanceCount as decimal(20,4)) / 12 * 100 AS [Session Attendance Percentage] to the query has everything counting and showing percentages. I got a little boost from the following article:
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/aggregates-with-multiple-tables

The problem was harder than I realized. It is good to know I am not the only one to have faced it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources