SQL Server 2012 Integrable Ticket Database
version beta 1.0 
diagram
/* *** Integrable Ticket Database (ITDb) *** */
/* ***                by Yılmaz DALKIRAN *** */

/* ************* (başlangıç) Veritabanı ve Tabloların Oluşturulması + İlişkilerin Kurulması (başlangıç) **************** */

USE MASTER

GO

CREATE DATABASE ITDb

GO

USE ITDb

GO

CREATE TABLE Members
(
	MemberID INT PRIMARY KEY IDENTITY,
	MemberName NVARCHAR(50) NOT NULL,
	MemberSurname NVARCHAR(50) NOT NULL,
	MemberEMail NVARCHAR(150) NOT NULL,
	MemberBirthDate DATE,
	MemberSignupDate DATETIME NOT NULL,
	MemberPhone CHAR(13),
	MemberAddress NVARCHAR(250)
)

GO

CREATE TABLE ActivityCategories
(
	ActivityCategoryID SMALLINT PRIMARY KEY IDENTITY,
	ActivityCategoryName NVARCHAR(50) NOT NULL
)

GO

CREATE TABLE Activities
(
	ActivityID INT PRIMARY KEY IDENTITY,
	ActivityName NVARCHAR(100) NOT NULL,
	ActivityDate SMALLDATETIME NOT NULL,
	ActivityPlace NVARCHAR(250) NOT NULL,
	ActivityCapacity INT NOT NULL CHECK (ActivityCapacity >= 0),
	ActivityCategoryID SMALLINT NOT NULL REFERENCES ActivityCategories(ActivityCategoryID),
	ActivityTicketPrice MONEY NOT NULL
)

GO

CREATE TABLE TicketOrders
(
	ActivityID INT REFERENCES Activities(ActivityID),
	MemberID INT REFERENCES Members(MemberID),
	TicketOrderDate DATETIME NOT NULL,
	TicketOrderQuantity SMALLINT NOT NULL,
	CONSTRAINT PK_TicketOrders PRIMARY KEY (ActivityID, MemberID)
)

/* ************* (son) Veritabanı ve Tabloların Oluşturulması + İlişkilerin Kurulması (son) **************** */

/* *********** (başlangıç) Ekleme, Güncelleme, Silme ve IDye Göre Getirme Prosedürleri (başlangıç) ************ */

GO

SET DATEFORMAT DMY

GO

/* *** Members Tablosu Prosedürleri *** */

CREATE PROCEDURE proc_InsertMember
(@MemberName NVARCHAR(50), @MemberSurname NVARCHAR(50), @MemberEMail NVARCHAR(150), @MemberBirthDate DATE, @MemberSignupDate DATETIME, @MemberPhone CHAR(13), @MemberAddress NVARCHAR(250))
AS
	INSERT INTO Members(MemberName, MemberSurname, MemberEMail, MemberBirthDate, MemberSignupDate, MemberPhone, MemberAddress)
	VALUES (@MemberName, @MemberSurname, @MemberEMail, @MemberBirthDate, @MemberSignupDate, @MemberPhone, @MemberAddress)

GO

CREATE PROCEDURE proc_UpdateMember
(@MemberID INT, @MemberName NVARCHAR(50), @MemberSurname NVARCHAR(50), @MemberEMail NVARCHAR(150), @MemberBirthDate DATE, @MemberSignupDate DATETIME, @MemberPhone CHAR(13), @MemberAddress NVARCHAR(250))
AS
	UPDATE Members
	SET
		MemberName = @MemberName,
		MemberSurname = @MemberSurname,
		MemberEMail = @MemberEMail,
		MemberBirthDate = @MemberBirthDate,
		MemberSignupDate = @MemberSignupDate,
		MemberPhone = @MemberPhone,
		MemberAddress = @MemberAddress
		WHERE MemberID = @MemberID

GO

CREATE PROCEDURE proc_DeleteMember
(@MemberID INT)
AS
	DELETE FROM Members WHERE @MemberID = MemberID

GO

CREATE PROC proc_SelectMember
(@MemberID INT)
AS
	SELECT * FROM Members WHERE @MemberID = MemberID

GO

/* *** Activities Tablosu Prosedürleri *** */

CREATE PROCEDURE proc_InsertActivity
(@ActivityName NVARCHAR(100), @ActivityDate SMALLDATETIME, @ActivityPlace NVARCHAR(250), @ActivityCapacity INT, @ActivityCategoryID SMALLINT, @ActivityTicketPrice MONEY)
AS
	INSERT INTO Activities(ActivityName, ActivityDate, ActivityPlace, ActivityCapacity, ActivityCategoryID, ActivityTicketPrice)
	VALUES (@ActivityName, @ActivityDate, @ActivityPlace, @ActivityCapacity, @ActivityCategoryID, @ActivityTicketPrice)

GO

CREATE PROCEDURE proc_UpdateActivity
(@ActivityID INT, @ActivityName NVARCHAR(100), @ActivityDate SMALLDATETIME, @ActivityPlace NVARCHAR(250), @ActivityCapacity INT, @ActivityCategoryID SMALLINT, @ActivityTicketPrice MONEY)
AS
	UPDATE Activities
	SET
		ActivityName = @ActivityName,
		ActivityDate = @ActivityDate,
		ActivityPlace = @ActivityPlace,
		ActivityCapacity = @ActivityCapacity,
		ActivityCategoryID = @ActivityCategoryID,
		ActivityTicketPrice = @ActivityTicketPrice
		WHERE ActivityID = @ActivityID

GO

CREATE PROCEDURE proc_DeleteActivity
(@ActivityID INT)
AS
	DELETE FROM Activities WHERE @ActivityID = ActivityID

GO

CREATE PROC proc_SelectActivity
(@ActivityID INT)
AS
	SELECT * FROM Activities WHERE @ActivityID = ActivityID

GO

/* *** ActivityCategories Tablosu Prosedürleri *** */

CREATE PROCEDURE proc_InsertActivityCategory
(@ActivityCategoryName NVARCHAR(50))
AS
	INSERT INTO ActivityCategories(ActivityCategoryName)
	VALUES (@ActivityCategoryName)

GO

CREATE PROCEDURE proc_UpdateActivityCategory
(@ActivityCategoryID SMALLINT, @ActivityCategoryName NVARCHAR(50))
AS
	UPDATE ActivityCategories
	SET
		ActivityCategoryName = @ActivityCategoryName
		WHERE ActivityCategoryID = @ActivityCategoryID

GO

CREATE PROCEDURE proc_DeleteActivityCategory
(@ActivityCategoryID SMALLINT)
AS
	DELETE FROM ActivityCategories WHERE ActivityCategoryID = @ActivityCategoryID

GO

CREATE PROC proc_SelectActivityCategory
(@ActivityCategoryID SMALLINT)
AS
	SELECT * FROM ActivityCategories WHERE @ActivityCategoryID = ActivityCategoryID

GO

/* *** TicketOrders Tablosu Prosedürleri *** */

CREATE PROCEDURE proc_InsertTicketOrder
(@ActivityID INT, @MemberID INT, @TicketOrderDate DATETIME, @TicketOrderQuantity SMALLINT)
AS
	INSERT INTO TicketOrders(ActivityID, MemberID, TicketOrderDate, TicketOrderQuantity)
	VALUES (@ActivityID, @MemberID, @TicketOrderDate, @TicketOrderQuantity)

GO

CREATE PROCEDURE proc_UpdateTicketOrder
(@ActivityID INT, @MemberID INT, @TicketOrderDate DATETIME, @TicketOrderQuantity SMALLINT)
AS
	UPDATE TicketOrders
	SET
		TicketOrderDate = @TicketOrderDate,
		TicketOrderQuantity = @TicketOrderQuantity
		WHERE @ActivityID = ActivityID and @MemberID = MemberID

GO

CREATE PROCEDURE proc_DeleteTicketOrder
(@ActivityID INT, @MemberID INT)
AS
	DELETE FROM TicketOrders
	WHERE @ActivityID = ActivityID and @MemberID = MemberID

GO

CREATE PROC proc_SelectTicketOrder
(@ActivityID INT, @MemberID INT)
AS
	SELECT * FROM TicketOrders
	WHERE @ActivityID = ActivityID and @MemberID = MemberID

GO

/* **************************** (son) Ekleme, Güncelleme, Silme ve IDye Göre Getirme Prosedürleri (son) *************************** */

/* ******************************* (başlangıç) !!! Triggerlar + Transactionlar !!! (başlangıç)********************************** */

/* Sipariş geldiğinde etkinlik kapasitesi bilet miktarına göre yeniden düzenlenecek + Sipariş miktarı güncel kapasiteye eşit ya da  güncel kapasiteden büyükse transaction hatası verilecek. */

CREATE TRIGGER ActivityCapacityRegulation_insert
ON TicketOrders
FOR INSERT
AS
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @TicketOrderQuantity SMALLINT, @ActivityID INT
			SELECT @TicketOrderQuantity = TicketOrderQuantity, @ActivityID = ActivityID FROM INSERTED
			UPDATE Activities SET ActivityCapacity -= @TicketOrderQuantity WHERE ActivityID = @ActivityID
		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		ROLLBACK TRANSACTION
		PRINT 'Etkinlik kapasitesi güncellenirken transaction hatası oluştu ve bilet siparişi yapılamadı. Bu hata sipariş edilen bilet miktarının güncel etkinlik kapasitesini aşmasından kaynaklanıyor olabilir.'
	END CATCH

GO

CREATE TRIGGER ActivityCapacityRegulation_update
ON TicketOrders
AFTER UPDATE
AS
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @PreviousQuantity SMALLINT, @UpdatedQuantity SMALLINT, @ActivityID INT
			SELECT @PreviousQuantity = TicketOrderQuantity, @ActivityID = ActivityID FROM DELETED
			UPDATE Activities SET ActivityCapacity += @PreviousQuantity WHERE ActivityID = @ActivityID
			SELECT @UpdatedQuantity = TicketOrderQuantity, @ActivityID = ActivityID FROM INSERTED
			UPDATE Activities SET ActivityCapacity -= @UpdatedQuantity WHERE ActivityID = @ActivityID
		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		ROLLBACK TRANSACTION
		PRINT 'Etkinlik kapasitesi güncellenirken transaction hatası oluştu ve bilet siparişi yapılamadı. Bu hata sipariş edilen bilet miktarının güncel etkinlik kapasitesini aşmasından kaynaklanıyor olabilir.'
	END CATCH
	
GO

CREATE TRIGGER ActivityCapacityRegulation_delete
ON TicketOrders
AFTER DELETE

AS
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @DeletedQuantity SMALLINT, @ActivityID INT
			SELECT @DeletedQuantity = TicketOrderQuantity, @ActivityID = ActivityID FROM DELETED
			UPDATE Activities SET ActivityCapacity += @DeletedQuantity WHERE ActivityID = @ActivityID
		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		ROLLBACK TRANSACTION
		PRINT 'Etkinlik kapasitesi güncellenirken transaction hatası oluştu ve bilet siparişi yapılamadı. Bu hata sipariş edilen bilet miktarının güncel etkinlik kapasitesini aşmasından kaynaklanıyor olabilir.'
	END CATCH
	
GO

/* ******************************* (son) !!! Triggerlar + Transactionlar !!! (son)********************************** */


/* ******************************* (başlangıç) Veri Ekleme, Güncelleme, Silme, IDye Göre Getirme (başlangıç) ****** */

GO

EXECUTE proc_InsertMember 'Mehmet', 'SOYUGÜZEL', 'loool1@gmail.com', '19.05.1982', '30.01.2013 14:23:56.513', '555 304 55 96', 'deneme adres'
EXECUTE proc_InsertMember 'Sinm', 'YÜKSEL', 'loool2@gmail.com', '22.03.1988', '01.02.2013 14:23:56.978', '', 'deneme adres'
EXECUTE proc_InsertMember 'Abdurrahman', 'CANICİĞER', 'loool3@gmail.com', '11.12.1990', '02.02.2013 21:14:13.271', '', 'deneme adres'
EXECUTE proc_InsertMember 'Berna', 'BAL', 'loool4@gmail.com', '11.07.1986', '03.02.2013 15:10:10.665', '555 271 14 45', 'deneme adres'
EXECUTE proc_InsertMember 'Haluk', 'AHMEDOV', 'loool5@gmail.com', '13.12.1988', '03.02.2013 17:18:21.997', '', 'deneme adres'
EXECUTE proc_InsertMember 'Gözde', 'EFE', 'loool6@gmail.com', '14.11.1987', '04.02.2013 09:12:48.002', '', 'deneme adres'
EXECUTE proc_InsertMember 'Soner', 'ALAT', 'loool7@gmail.com', '07.03.1991', '05.02.2013 03:51:32.473', '', 'deneme adres'
EXECUTE proc_InsertMember 'Aydın', 'BÖLÜKBAŞI', 'loool8@gmail.com', '04.04.1986', '05.02.2013 19:15:15.439', '533 412 12 14', 'deneme adres'
EXECUTE proc_InsertMember 'Damla', 'AYAZ', 'loool9@gmail.com', '', '05.02.2013 20:18:41.541', '', ''
EXECUTE proc_InsertMember 'Habibe', 'GÜL', 'loool10@gmail.com', '15.07.1990', '05.02.2013 21:00:14.348', '', 'deneme adres'
EXECUTE proc_InsertMember 'Pelin', 'KARAHAN', 'loool11@gmail.com', '18.03.1991', '05.02.2013 21:46:12.870', '', 'deneme adres'
EXECUTE proc_InsertMember 'Ceyda', 'IŞILAK', 'loool12@gmail.com', '02.10.1982', '06.02.2013 08:12:21.909', '', 'deneme adres'
EXECUTE proc_InsertMember 'Ayşe', 'ÇAPOĞLU', 'loool13@gmail.com', '29.09.1985', '06.02.2013 11:13:06.271', '', 'deneme adres'
EXECUTE proc_InsertMember 'Serhan', 'YILMAZ', 'loool14@gmail.com', '11.02.1990', '06.02.2013 14:23:56.967', '', 'deneme adres'
EXECUTE proc_InsertMember 'Merve', 'KUYUCU', 'loool15@gmail.com', '14.05.1987', '06.02.2013 16:41:27.561', '540 714 56 32', 'deneme adres'
EXECUTE proc_InsertMember 'Yiğit', 'FIÇICI', 'loool16@gmail.com', '29.04.1986', '07.02.2013 18:19:41.652', '530 650 14 15', 'deneme adres'
EXECUTE proc_UpdateMember 2, 'Sinem', 'YÜKSEL', 'loool2@gmail.com', '22.03.1988', '01.02.2013 14:23:56.978', '505 807 64 83', 'deneme adres'
EXECUTE proc_DeleteMember 3

GO

EXECUTE proc_InsertActivityCategory 'Konser'
EXECUTE proc_InsertActivityCategory 'Spor'
EXECUTE proc_InsertActivityCategory 'Sinema'
EXECUTE proc_InsertActivityCategory 'Sanat'
EXECUTE proc_InsertActivityCategory 'Belirsiz'
EXECUTE proc_UpdateActivityCategory 5, 'Kategorisiz'
EXECUTE proc_InsertActivityCategory 'Silinecek Kategori'
EXECUTE proc_DeleteActivityCategory @@identity

GO

EXECUTE proc_InsertActivity 'Neş''e Erdok Resim Sergisi', '02.05.2013 10:00', 'Evin Sanat Galerisi, Büyük Bebek Deresi Sokak No: 13, Bebek / İstanbul', 700, 4, 42
EXECUTE proc_InsertActivity 'Bir Queen ve Ben Elton Müzikali', '12.05.2013 19:00', 'Ülker Sports Arena, Barbaros Mah. Ihlamur Sok. Batı Ataşehir / İSTANBUL', 15000, 1, 21
EXECUTE proc_InsertActivity 'Fenerbahçe - Galatasaray Maçı', '12.05.2013 19:00', 'Şükrü Saraçoğlu Stadı Kadıköy / İSTANBUL', 40000, 2, 65
EXECUTE proc_InsertActivity 'Pera Piyano Festivali', '12.05.2013 20:00', 'Pera Güzel Sanatlar Sıraselviler Cad. Billurcu Sok. Billurcu Çıkmazı No:10 Taksim / İSTANBUL', 300, 4, 48
EXECUTE proc_InsertActivity '21 And Over Filmi', '17.05.2013 21:00', 'Cinemaximum Sineması Salon 2 Şişli / İSTANBUL', 150, 3, 13
EXECUTE proc_InsertActivity '[fill in the blanks] Paraşütü Yaparken Balık Tutma', '04.04.2013 14:00', 'Kaf Dağı', 50, 5, 120 
EXECUTE proc_UpdateActivity 6, 'Yamaç Paraşütü Yaparken Balık Tutma', '04.04.2013 14:00', 'Kaf Dağı', 50, 5, 120 
EXECUTE proc_InsertActivity 'silinecek etkinlik', '14.04.2013 14:00', 'lool', 99999999, 5, 987456.123
EXECUTE proc_DeleteActivity @@identity

GO

EXECUTE proc_InsertTicketOrder 1, 1, '08.02.2013 17:15:43.574', 1
EXECUTE proc_InsertTicketOrder 1, 4, '08.02.2013 18:16:12.324', 2
EXECUTE proc_InsertTicketOrder 3, 2, '08.02.2013 19:14:33.274', 2
EXECUTE proc_InsertTicketOrder 3, 8, '08.02.2013 19:14:12.275', 1
EXECUTE proc_InsertTicketOrder 3, 14, '08.02.2013 19:42:38.012', 1
EXECUTE proc_InsertTicketOrder 3, 16, '08.02.2013 20:23:14.971', 1
EXECUTE proc_InsertTicketOrder 3, 7, '09.02.2013 00:29:57.395', 1
EXECUTE proc_InsertTicketOrder 2, 9, '10.02.2013 14:15:51.442', 1
EXECUTE proc_InsertTicketOrder 2, 5, '10.02.2013 14:16:52.413', 1
EXECUTE proc_InsertTicketOrder 2, 6, '10.02.2013 14:16:53.667', 1
EXECUTE proc_InsertTicketOrder 2, 10, '10.02.2013 15:11:21.120', 1
EXECUTE proc_InsertTicketOrder 2, 14, '10.02.2013 15:18:25.471', 3
EXECUTE proc_InsertTicketOrder 2, 15, '10.02.2013 17:31:41.093', 1
EXECUTE proc_InsertTicketOrder 2, 13, '10.02.2013 17:32:24.125', 1
EXECUTE proc_InsertTicketOrder 2, 12, '10.02.2013 23:27:44.819', 2
EXECUTE proc_InsertTicketOrder 5, 11, '11.02.2013 19:14:12.000', 2
EXECUTE proc_InsertTicketOrder 5, 7, '11.02.2013 20:16:13.431', 1
EXECUTE proc_InsertTicketOrder 5, 9, '11.02.2013 21:17:16.314', 1
EXECUTE proc_InsertTicketOrder 5, 4, '11.02.2013 22:22:02.671', 4
EXECUTE proc_InsertTicketOrder 5, 14, '11.02.2013 23:45:16.199', 1

GO

EXECUTE proc_InsertTicketOrder 1, 5, '17.02.2013 15:14:21.012', 1
EXECUTE proc_InsertTicketOrder 3, 5, '17.02.2013 16:41:13.365', 12
EXECUTE proc_InsertTicketOrder 4, 2, '17.02.2013 17:36:15.400', 10 
EXECUTE proc_DeleteTicketOrder 4, 2                                   -- Delete Trigger'ı doğru çalışıyor. Kapasite tekrar 300

GO

EXECUTE proc_InsertTicketOrder 4, 1, '18.02.2013 10:14:41.358', 50
EXECUTE proc_UpdateTicketOrder 4, 1, '18.02.2013 10:15:58.411', 100   -- Update Trigger test aşaması... Bir önceki işlem silineceği için güncel kapasite 200 olmalı (300 - 100). Doğru çalışıyor

GO

EXECUTE proc_InsertTicketOrder 4, 8, '19.02.2013 21:20:36.300', 10   -- Insert Trigger test aşaması... Güncel Kapasite 190 olmalı... Doğru çalışıyor
EXECUTE proc_UpdateTicketOrder 4, 8, '19.02.2013 21:23:41.682', 15   -- Update Trigger test aşaması... Bir önceki işlem silineceği için güncel kapasite 185 olmalı (200 - 15).  Doğru çalışıyor
EXECUTE proc_UpdateTicketOrder 4, 8, '19.02.2013 21:25:42.791', 20   -- Update Trigger test aşaması... Bir önceki işlem silineceği için güncel kapasite 180 olmalı (200 - 20). Doğru çalışıyor

SELECT * FROM Activities

GO

EXECUTE proc_InsertTicketOrder 6, 4, '20.02.2013 03:12:45.274', 51    -- Insert Trigger test aşaması... Kapasitesi 50 olan bir etkinlikten 51 tane bilet sipariş edilmeye çalışıldı. Check constraint ile işlem kısıtlandı ve transaction ile yapılan işlem geri alındı. Transaction doğru çalışıyor

GO

EXECUTE proc_InsertTicketOrder 6, 9, '20.02.2013 03:13:18.018', 50	  -- Insert Trigger test aşaması... Kapasitesi 50 olan bir etkinlikten 50 tane bilet sipariş edildi. 6 numaralı etkinliğin güncel kapasitesi 0 olmalı. Trigger doğru çalışıyor

GO

EXECUTE proc_InsertTicketOrder 3, 10, '01.03.2013 00:00:00.001', 300  -- Mart ayı için en çok sipariş miktarına sahip etkinliği görebilmek adına execute edildi.
EXECUTE proc_InsertTicketOrder 1, 2, '01.03.2013 00:00:00.002', 250 -- Mart ayı için en çok sipariş miktarına sahip etkinliği görebilmek adına execute edildi.
EXECUTE proc_InsertTicketOrder 2, 7, '01.03.2013 00:00:00.003', 150 -- Mart ayı için en çok sipariş miktarına sahip etkinliği görebilmek adına execute edildi.

GO

EXEC proc_SelectMember 8
EXEC proc_SelectActivity 4
EXEC proc_SelectActivityCategory 4
EXEC proc_SelectTicketOrder 4, 8

GO

SELECT * FROM Activities

GO

/* ******************************* (son) Veri Ekleme, Güncelleme, Silme, IDye Göre Getirme (son) ******** */

/* ******************************  (başlangıç) Spesifik Prosedürler (başlangıç) ************************* */

-- IDsi verilen üye hangi etkinliklere katılmış?

CREATE PROCEDURE proc_SelectActivitiesoftheMember
(@MemberID INT)
AS
	SELECT a.ActivityName [Activities of the Member]
	FROM TicketOrders t
	JOIN Members m ON m.MemberID = t.MemberID
	JOIN Activities a ON a.ActivityID = t.ActivityID
	WHERE t.MemberID = @MemberID

GO

EXECUTE proc_SelectActivitiesoftheMember 2            -- Test aşaması... Doğru çalışıyor

GO

-- IDsi verilen etkinliğe hangi üyeler katılmış?

CREATE PROCEDURE proc_SelectMembersoftheActivity
(@ActivityID INT)
AS
	SELECT m.MemberName+' '+m.MemberSurname [Members of the Activity]
	FROM TicketOrders t
	JOIN Members m ON m.MemberID = t.MemberID
	JOIN Activities a ON a.ActivityID = t.ActivityID
	WHERE t.ActivityID = @ActivityID

GO

EXECUTE proc_SelectMembersoftheActivity 3            -- Test aşaması... Doğru çalışıyor

GO

-- Ayın en fazla para kazandıran etkinliğini getirin.

CREATE PROCEDURE proc_SelectMaxIncomeofActivities
AS
	SELECT TOP 1 t.ActivityID, a.ActivityName, SUM(t.TicketOrderQuantity * a.ActivityTicketPrice) [Income of the Activity]
	FROM TicketOrders t
	FULL JOIN Activities a ON  a.ActivityID = t.ActivityID
	GROUP BY t.ActivityID, a.ActivityName
	ORDER BY 3 DESC

GO

EXECUTE proc_SelectMaxIncomeofActivities             -- Test aşaması... Doğru çalışıyor

GO

-- Ayın en çok katılım alan etkinliği getirin 

CREATE PROCEDURE proc_SelectActivityfromMaxOrderQuantityofMonth --istenilen ay'a göre
(@QueryDate DATE)
AS
	SELECT TOP 1 t.ActivityID, a.ActivityName, SUM(t.TicketOrderQuantity) 'Quantity of Orders'
	FROM TicketOrders t
	INNER JOIN Activities a ON a.ActivityID = t.ActivityID
	WHERE MONTH(TicketOrderDate) = MONTH(@QueryDate)
	GROUP BY t.ActivityID, a.ActivityName
	ORDER BY 3 DESC

GO

EXECUTE proc_SelectActivityfromMaxOrderQuantityofMonth '01.02.2013' -- Test aşaması... Doğru Çalışıyor
EXECUTE proc_SelectActivityfromMaxOrderQuantityofMonth '01.03.2013' -- Test aşaması... Doğru Çalışıyor

GO

CREATE PROCEDURE proc_SelectActivityfromMaxOrderQuantityofCurrentMonth -- güncel ay'a göre
AS
	SELECT TOP 1 t.ActivityID, a.ActivityName, SUM(t.TicketOrderQuantity) 'Quantity of Orders'
	FROM TicketOrders t
	INNER JOIN Activities a ON a.ActivityID = t.ActivityID
	WHERE MONTH(TicketOrderDate) = MONTH(GETDATE())
	GROUP BY t.ActivityID, a.ActivityName
	ORDER BY 3 DESC

GO

EXECUTE proc_SelectActivityfromMaxOrderQuantityofCurrentMonth

GO

-- Hangi kategoride toplam kaç etkinlik olduğunu ve kategori bazlı toplam katılımcı sayısını getirin.

CREATE PROCEDURE proc_SelectActivityandMemberQuantityofActivityCategories
(@ActivityCategory SMALLINT)
AS
	SELECT ac.ActivityCategoryID, ac.ActivityCategoryName, COUNT(a.ActivityID) [Quantity of Activities]
	FROM ActivityCategories ac
	JOIN Activities a ON a.ActivityCategoryID = ac.ActivityCategoryID
	WHERE @ActivityCategory = ac.ActivityCategoryID
	GROUP BY ac.ActivityCategoryID, ac.ActivityCategoryName

	SELECT ac.ActivityCategoryID, ac.ActivityCategoryName, COUNT(m.MemberID) [Quantity of Members]
	FROM Activities a
	JOIN TicketOrders t ON t.ActivityID = a.ActivityID
	JOIN ActivityCategories ac ON ac.ActivityCategoryID = a.ActivityCategoryID
	JOIN Members m ON m.MemberID = t.MemberID
	WHERE @ActivityCategory = ac.ActivityCategoryID
	GROUP BY ac.ActivityCategoryID, ac.ActivityCategoryName

GO

EXECUTE proc_SelectActivityandMemberQuantityofActivityCategories 4  -- Test aşaması... Doğru Çalışıyor

GO

-- Günlük tahsilat raporu oluşturun.

CREATE VIEW DailyActivityandIncome
AS
	SELECT a.ActivityName, t.TicketOrderDate, SUM(t.TicketOrderQuantity * a.ActivityTicketPrice) 'Daily Income'
	FROM Activities a
	JOIN TicketOrders t ON t.ActivityID = a.ActivityID
	WHERE DAY(t.TicketOrderDate) = DAY(GETDATE())
	GROUP BY a.ActivityName, t.TicketOrderDate

GO

CREATE PROCEDURE proc_DailyIncomeReport
AS	
	SELECT *
	FROM DailyActivityandIncome

	SELECT SUM([Daily Income]) 'Daily Total Income'
	FROM DailyActivityandIncome

GO

EXECUTE proc_DailyIncomeReport -- Test aşaması... Doğru çalışıyor

/* ******************************  (son) Spesifik Prosedürler (son) ************************************* */



/* *** Thanks *** */
-- Gaffar SALKIM / http://www.gaffarsalkim.com/
-- Murat ÇİFTÇİ
-- Internet / http://www.google.ca