Invited, But Did You Show?

Question

I hope I am in the correct forum. I am not very fluent with sql so I use the designer a lot to get started. I think I am going to need a subquery or two. The following Excel workbook image shows an example of part of what I need to extract from the database records.

I have a need to get info from 4 tables. I need 3 Columns of data, two of them totals. I get the count from a table that has registration records, but I also need the highest number attended in a single day from the related attendance records where the attendees are marked 'Present'. I haven't been able to make a subquery that gives correct attendee numbers to each row. I would appreciate any help. Here's what I have:

SELECT     sj_Sites.SiteName, COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], COUNT(sj_ProgramAttendance.AttendanceID) 
                      AS [Highest Single Day Attendance]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID INNER JOIN
                      sj_ProgramAttendance ON sj_ProgramRegistrations.RegID = sj_ProgramAttendance.RegID
--WHERE     (sj_ProgramAttendance.AttendanceType = N'Present')
GROUP BY sj_Sites.SiteName

Answer 1

Why did you remark WHERE clause?

It seems it should be OK if where clause is not remarked, however, would you please share the results of the above query including WHERE clause please?

Cheers

---

MCP, MCTS, MCITP

Answer 2

Thanks for the reply. Below is the result with the comment. I get a count of the total registrations.

But in the result run with the WHERE clause I get the total from the filter on both columns. I need the Total enrollments and a total count for the highest single day.

I will add a follow up post with a query I tried as a subquery that falls short of expectation too.

Answer 3

And here is the attempt at a subquery and its results; the problem is that the Highest Single Day Attendance should be a different total in each row as the SiteName will have different enrollments and attendances:

SELECT     sj_Sites.SiteName, COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], (SELECT Top 0.1 Percent COUNT(sj_ProgramAttendance.RegID) AS [Daily Attendance]
FROM         sj_ProgramAttendance INNER JOIN
                      sj_ProgramRegistrations ON sj_ProgramAttendance.RegID = sj_ProgramRegistrations.RegID INNER JOIN
                      sj_SiteRegistration ON sj_ProgramRegistrations.SiteRegID = sj_SiteRegistration.SiteRegID
GROUP BY sj_ProgramAttendance.AttendanceType, sj_ProgramAttendance.AttendanceDate
HAVING      (sj_ProgramAttendance.AttendanceType = N'Present')) AS [Highest Single Day Attendance]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID INNER JOIN
                      sj_ProgramAttendance ON sj_ProgramRegistrations.RegID = sj_ProgramAttendance.RegID
--WHERE     (sj_ProgramAttendance.AttendanceType = N'Present')
GROUP BY sj_Sites.SiteName

Answer 4

Hmm,

Are you sure about your database design? It seems you have a record including "64" for Highest Single Day Attendance in your table called sj_ProgtramAttendance. So, in this case it will be repeated in each row for Number Enrolled. Don't you think it would be better to have a One-to-Many relationship between sj_SiteRegistration and sj_ProgramAttendance instead of the  relationship between sj_ProgramRegistration and sj_ProgtramAttendance?

Cheers

---

MCP, MCTS, MCITP

Answer 5

Hello,

Attendance = count of people having followed a program ? If yes 64 is a correct value ( but i may go wrong with my poor english ).

I suppose that the query is done on a SQL Server instance. Is it possible to have the full version ( 2008,2008 R2, 2012,... + last installed service pack ) and the full edition ( Express,Web,Standard,Entreprise,...) ?

As your thread is not related to SQL Server Data Access but query creation/optimization, i will move this thread towards the TRANSACT-SQL forum .

Please, could you post the structure of your table(s) including the indexes/keys (primary or not ) ?

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

Hello,

Move done. I hope you will find a full and answer on this forum

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 7

May be you need this

SELECT     sj_Sites.SiteName, COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], COUNT(sj_ProgramAttendance.AttendanceID) 
                      AS [Highest Single Day Attendance]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID LEFT JOIN
                      sj_ProgramAttendance ON sj_ProgramRegistrations.RegID = sj_ProgramAttendance.RegID AND (sj_ProgramAttendance.AttendanceType = N'Present')
GROUP BY sj_Sites.SiteName

---

Thanks and regards, Rishabh K

Answer 8

Hmm,

Are you sure about your database design? It seems you have a record including "64" for Highest Single Day Attendance in your table called sj_ProgtramAttendance. So, in this case it will be repeated in each row for Number Enrolled. Don't you think it would be better to have a One-to-Many relationship between sj_SiteRegistration and sj_ProgramAttendance instead of the  relationship between sj_ProgramRegistration and sj_ProgtramAttendance?

Cheers

---

MCP, MCTS, MCITP

 This design exists because a student can register for a different class at different times of the year and the locations could change. Therefore it seems to make sense to have a one to many with a registrations table and then track the attendances to that registration ID.

Answer 9

Hello,

Move done. I hope you will find a full and answer on this forum

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Thank you for your help. I will provide more info on the structure asap.

Answer 10

Try the below one:

SELECT	sj_Sites.SiteName, 
		COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], 
		(
			SELECT Top	1 COUNT(sj_ProgramAttendance.AttendanceID) AS [Daily Attendance]
			FROM    sj_ProgramAttendance 
			WHERE	sj_ProgramAttendance.RegID = sj_ProgramRegistrations.RegID
			AND		sj_ProgramAttendance.AttendanceType = 'Present'
			GROUP BY sj_ProgramAttendance.AttendanceDate 
			ORDER BY 1 DESC
		) AS [Highest Single Day Attendance]
FROM        sj_Sites 
INNER JOIN	sj_SiteRegistration 
ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
INNER JOIN	sj_ProgramRegistrations 
ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID 
GROUP BY sj_Sites.SiteName

Thanks!

Answer 11

Try the below one:

SELECT	sj_Sites.SiteName, 
		COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], 
		(
			SELECT Top	1 COUNT(sj_ProgramAttendance.AttendanceID) AS [Daily Attendance]
			FROM    sj_ProgramAttendance 
			WHERE	sj_ProgramAttendance.RegID = sj_ProgramRegistrations.RegID
			AND		sj_ProgramAttendance.AttendanceType = 'Present'
			GROUP BY sj_ProgramAttendance.AttendanceDate 
			ORDER BY 1 DESC
		) AS [Highest Single Day Attendance]
FROM        sj_Sites 
INNER JOIN	sj_SiteRegistration 
ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
INNER JOIN	sj_ProgramRegistrations 
ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID 
GROUP BY sj_Sites.SiteName

Thanks!

Thanks Deepak!

The above query breaks down the RegID count individually instead of giving a total registration number. I removed the Where RegID part and got closer to what I need. Also the subquery does not give me a separate number of Single Day Attendances. It applies the same number for each SiteName. See the image and modified sql below.

SELECT     sj_Sites.SiteName,
                          (SELECT     TOP (1) COUNT(AttendanceID) AS [Daily Attendance]
                            FROM          sj_ProgramAttendance
                            WHERE      (AttendanceType = 'Present')
                            GROUP BY AttendanceDate
                            ORDER BY [Daily Attendance] DESC) AS [Highest Single Day Attendance], COUNT(sj_ProgramRegistrations.RegID) AS [Total Registrations]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
GROUP BY sj_Sites.SiteName

Answer 12

Please post DDL of the tables involved, some insert statements to populate the tables and desired output.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Try the below one:

SELECT	sj_Sites.SiteName, 
		COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], 
		(
			SELECT Top	1 COUNT(sj_ProgramAttendance.AttendanceID) AS [Daily Attendance]
			FROM    sj_ProgramAttendance 
			WHERE	sj_ProgramAttendance.RegID = sj_ProgramRegistrations.RegID
			AND		sj_ProgramAttendance.AttendanceType = 'Present'
			GROUP BY sj_ProgramAttendance.AttendanceDate 
			ORDER BY 1 DESC
		) AS [Highest Single Day Attendance]
FROM        sj_Sites 
INNER JOIN	sj_SiteRegistration 
ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
INNER JOIN	sj_ProgramRegistrations 
ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID 
GROUP BY sj_Sites.SiteName

Thanks!

Thanks Deepak!

The above query breaks down the RegID count individually instead of giving a total registration number. I removed the Where RegID part and got closer to what I need. Also the subquery does not give me a separate number of Single Day Attendances. It applies the same number for each SiteName. See the image and modified sql below.

SELECT     sj_Sites.SiteName,
                          (SELECT     TOP (1) COUNT(AttendanceID) AS [Daily Attendance]
                            FROM          sj_ProgramAttendance
                            WHERE      (AttendanceType = 'Present')
                            GROUP BY AttendanceDate
                            ORDER BY [Daily Attendance] DESC) AS [Highest Single Day Attendance], COUNT(sj_ProgramRegistrations.RegID) AS [Total Registrations]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
GROUP BY sj_Sites.SiteName

Try the updated one provided below:

;WITH SiteTotalRegistrations AS
(
	SELECT  sj_Sites.SiteID, sj_Sites.SiteName,
			COUNT(sj_ProgramRegistrations.RegID) AS [Total Registrations]
	FROM        sj_Sites 
	INNER JOIN	sj_SiteRegistration 
	ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
	INNER JOIN	sj_ProgramRegistrations 
	ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
	GROUP BY	sj_Sites.SiteID, sj_Sites.SiteName
),
SiteDayWiseAttendance AS
(
    SELECT  sj_Sites.SiteID, AttendanceDate, COUNT(AttendanceID) AS DailyAttendanceCount
    FROM    sj_ProgramAttendance
    JOIN	sj_ProgramRegistrations
    ON		sj_ProgramRegistrations.RegID = sj_ProgramAttendance.RegID
    AND		AttendanceType = 'Present'
    JOIN	sj_SiteRegistration 
    ON		sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
    JOIN	sj_Sites
	ON		sj_Sites.SiteID = sj_SiteRegistration.SiteID 
    GROUP BY sj_Sites.SiteID, AttendanceDate
) 
SELECT	STReg.SiteID, STReg.SiteName, STReg.[Total Registrations], Temp.[Highest Single Day Attendance]
FROM		SiteTotalRegistrations STReg
CROSS APPLY	(
				SELECT	MAX(DailyAttendanceCount) [Highest Single Day Attendance]
				FROM	SiteDayWiseAttendance SDWA
				WHERE	STReg.SiteID = SDWA.SiteID
			)  Temp

Thanks!

Answer 14

Please post DDL of the tables involved, some insert statements to populate the tables and desired output.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Here is the create table script. Forgive me if I have misunderstood the request. I don't know how to include the records I have created. I will work on inserts now.

/****** Object:  Table [dbo].[sj_Sites]    Script Date: 08/06/2012 09:26:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sj_Sites]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sj_Sites](
	[SiteID] [int] IDENTITY(1,1) NOT NULL,
	[SiteName] [nvarchar](100) NOT NULL,
	[Type] [nvarchar](50) NOT NULL,
	[Phone] [nvarchar](14) NULL,
	[Address] [nvarchar](50) NOT NULL,
	[City] [nvarchar](50) NOT NULL,
	[State] [nvarchar](2) NULL,
	[Zip] [nvarchar](11) NOT NULL,
	[NumberOfCourts] [int] NULL,
	[Notes] [nvarchar](1000) NULL,
 CONSTRAINT [PK_sj_Sites] PRIMARY KEY CLUSTERED 
(
	[SiteID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[sj_SiteRegistration]    Script Date: 08/06/2012 09:26:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sj_SiteRegistration]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sj_SiteRegistration](
	[SiteRegID] [int] IDENTITY(1,1) NOT NULL,
	[SiteID] [int] NOT NULL,
	[ProgramID] [int] NOT NULL,
	[SessionID] [int] NOT NULL,
	[DirectorID] [int] NOT NULL,
 CONSTRAINT [PK_sj_SiteRegistration] PRIMARY KEY CLUSTERED 
(
	[SiteRegID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[sj_ProgramAttendance]    Script Date: 08/06/2012 09:26:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sj_ProgramAttendance]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sj_ProgramAttendance](
	[AttendanceID] [int] IDENTITY(1,1) NOT NULL,
	[RegID] [int] NOT NULL,
	[AttendanceDate] [date] NOT NULL,
	[AttendanceType] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_sj_ProgramAttendance] PRIMARY KEY CLUSTERED 
(
	[AttendanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [sj_PreventDuplicateDateEntry] UNIQUE NONCLUSTERED 
(
	[RegID] ASC,
	[AttendanceDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[sj_ProgramRegistrations]    Script Date: 08/06/2012 09:26:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sj_ProgramRegistrations](
	[RegID] [int] IDENTITY(1,1) NOT NULL,
	[SessionID] [int] NOT NULL,
	[PlayerID] [int] NOT NULL,
	[ProgramID] [int] NOT NULL,
	[SiteRegID] [int] NOT NULL,
	[RegDate] [date] NOT NULL,
	[StartTime] [nvarchar](20) NOT NULL,
	[EndTime] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_sj_ProgramRegistrations] PRIMARY KEY CLUSTERED 
(
	[RegID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_sj_ProgramRegistrations] UNIQUE NONCLUSTERED 
(
	[PlayerID] ASC,
	[SessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Default [DF_sj_ProgramRegistrations_RegDate]    Script Date: 08/06/2012 09:26:42 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_sj_ProgramRegistrations_RegDate]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
Begin
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_sj_ProgramRegistrations_RegDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[sj_ProgramRegistrations] ADD  CONSTRAINT [DF_sj_ProgramRegistrations_RegDate]  DEFAULT (getdate()) FOR [RegDate]
END


End
GO
/****** Object:  Default [DF_sj_Sites_State]    Script Date: 08/06/2012 09:26:42 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_sj_Sites_State]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_Sites]'))
Begin
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_sj_Sites_State]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[sj_Sites] ADD  CONSTRAINT [DF_sj_Sites_State]  DEFAULT (N'TX') FOR [State]
END


End
GO
/****** Object:  Default [DF_sj_Sites_NumberOfCourts]    Script Date: 08/06/2012 09:26:42 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_sj_Sites_NumberOfCourts]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_Sites]'))
Begin
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_sj_Sites_NumberOfCourts]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[sj_Sites] ADD  CONSTRAINT [DF_sj_Sites_NumberOfCourts]  DEFAULT ((0)) FOR [NumberOfCourts]
END


End
GO
/****** Object:  ForeignKey [FK_sj_ProgramRegistrations_sj_Players]    Script Date: 08/06/2012 09:26:42 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_Players]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations]  WITH CHECK ADD  CONSTRAINT [FK_sj_ProgramRegistrations_sj_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[sj_Players] ([PlayerID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_Players]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations] CHECK CONSTRAINT [FK_sj_ProgramRegistrations_sj_Players]
GO
/****** Object:  ForeignKey [FK_sj_ProgramRegistrations_sj_ProgramNames]    Script Date: 08/06/2012 09:26:42 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_ProgramNames]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations]  WITH CHECK ADD  CONSTRAINT [FK_sj_ProgramRegistrations_sj_ProgramNames] FOREIGN KEY([ProgramID])
REFERENCES [dbo].[sj_ProgramNames] ([ProgramID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_ProgramNames]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations] CHECK CONSTRAINT [FK_sj_ProgramRegistrations_sj_ProgramNames]
GO
/****** Object:  ForeignKey [FK_sj_ProgramRegistrations_sj_ProgramSessions]    Script Date: 08/06/2012 09:26:42 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_ProgramSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations]  WITH CHECK ADD  CONSTRAINT [FK_sj_ProgramRegistrations_sj_ProgramSessions] FOREIGN KEY([SessionID])
REFERENCES [dbo].[sj_ProgramSessions] ([SessionID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_ProgramSessions]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations] CHECK CONSTRAINT [FK_sj_ProgramRegistrations_sj_ProgramSessions]
GO
/****** Object:  ForeignKey [FK_sj_ProgramRegistrations_sj_SiteRegistration]    Script Date: 08/06/2012 09:26:42 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_SiteRegistration]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations]  WITH NOCHECK ADD  CONSTRAINT [FK_sj_ProgramRegistrations_sj_SiteRegistration] FOREIGN KEY([SiteRegID])
REFERENCES [dbo].[sj_SiteRegistration] ([SiteRegID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_sj_ProgramRegistrations_sj_SiteRegistration]') AND parent_object_id = OBJECT_ID(N'[dbo].[sj_ProgramRegistrations]'))
ALTER TABLE [dbo].[sj_ProgramRegistrations] CHECK CONSTRAINT [FK_sj_ProgramRegistrations_sj_SiteRegistration]
GO

Answer 15

I got these errors while executing this script:

Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_Players' references invalid table 'dbo.sj_Players'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_ProgramNames' references invalid table 'dbo.sj_ProgramNames'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_ProgramSessions' references invalid table 'dbo.sj_ProgramSessions'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, I assume these tables are not relevant for the problem and therefore if the constraints are not created, it's not a big deal.

If you can get some data and show desired output, it will be great.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

Try the below one:

SELECT	sj_Sites.SiteName, 
		COUNT(sj_ProgramRegistrations.RegID) AS [Number Enrolled], 
		(
			SELECT Top	1 COUNT(sj_ProgramAttendance.AttendanceID) AS [Daily Attendance]
			FROM    sj_ProgramAttendance 
			WHERE	sj_ProgramAttendance.RegID = sj_ProgramRegistrations.RegID
			AND		sj_ProgramAttendance.AttendanceType = 'Present'
			GROUP BY sj_ProgramAttendance.AttendanceDate 
			ORDER BY 1 DESC
		) AS [Highest Single Day Attendance]
FROM        sj_Sites 
INNER JOIN	sj_SiteRegistration 
ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
INNER JOIN	sj_ProgramRegistrations 
ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID 
GROUP BY sj_Sites.SiteName

Thanks!

Thanks Deepak!

The above query breaks down the RegID count individually instead of giving a total registration number. I removed the Where RegID part and got closer to what I need. Also the subquery does not give me a separate number of Single Day Attendances. It applies the same number for each SiteName. See the image and modified sql below.

SELECT     sj_Sites.SiteName,
                          (SELECT     TOP (1) COUNT(AttendanceID) AS [Daily Attendance]
                            FROM          sj_ProgramAttendance
                            WHERE      (AttendanceType = 'Present')
                            GROUP BY AttendanceDate
                            ORDER BY [Daily Attendance] DESC) AS [Highest Single Day Attendance], COUNT(sj_ProgramRegistrations.RegID) AS [Total Registrations]
FROM         sj_Sites INNER JOIN
                      sj_SiteRegistration ON sj_Sites.SiteID = sj_SiteRegistration.SiteID INNER JOIN
                      sj_ProgramRegistrations ON sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
GROUP BY sj_Sites.SiteName

Try the updated one provided below:

;WITH SiteTotalRegistrations AS
(
	SELECT  sj_Sites.SiteID, sj_Sites.SiteName,
			COUNT(sj_ProgramRegistrations.RegID) AS [Total Registrations]
	FROM        sj_Sites 
	INNER JOIN	sj_SiteRegistration 
	ON			sj_Sites.SiteID = sj_SiteRegistration.SiteID 
	INNER JOIN	sj_ProgramRegistrations 
	ON			sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
	GROUP BY	sj_Sites.SiteID, sj_Sites.SiteName
),
SiteDayWiseAttendance AS
(
    SELECT  sj_Sites.SiteID, AttendanceDate, COUNT(AttendanceID) AS DailyAttendanceCount
    FROM    sj_ProgramAttendance
    JOIN	sj_ProgramRegistrations
    ON		sj_ProgramRegistrations.RegID = sj_ProgramAttendance.RegID
    AND		AttendanceType = 'Present'
    JOIN	sj_SiteRegistration 
    ON		sj_SiteRegistration.SiteRegID = sj_ProgramRegistrations.SiteRegID
    JOIN	sj_Sites
	ON		sj_Sites.SiteID = sj_SiteRegistration.SiteID 
    GROUP BY sj_Sites.SiteID, AttendanceDate
) 
SELECT	STReg.SiteID, STReg.SiteName, STReg.[Total Registrations], Temp.[Highest Single Day Attendance]
FROM		SiteTotalRegistrations STReg
CROSS APPLY	(
				SELECT	MAX(DailyAttendanceCount) [Highest Single Day Attendance]
				FROM	SiteDayWiseAttendance SDWA
				WHERE	STReg.SiteID = SDWA.SiteID
			)  Temp

Thanks!

This solution does the trick. I can now apply other filters such as date ranges and such, and this is holding up to those as well.

I Wish to thank everyone for the consideration and time. It is GREAT learning that these solutions are reachable!

Answer 17

I got these errors while executing this script:

Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_Players' references invalid table 'dbo.sj_Players'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_ProgramNames' references invalid table 'dbo.sj_ProgramNames'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 3
Foreign key 'FK_sj_ProgramRegistrations_sj_ProgramSessions' references invalid table 'dbo.sj_ProgramSessions'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, I assume these tables are not relevant for the problem and therefore if the constraints are not created, it's not a big deal.

If you can get some data and show desired output, it will be great.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Thanks for looking at this Naomi. I applied the solution offered in Deepak's reply successfully.