индувидуальная задача Саша

USE Sasha

CREATE TABLE mountains
(
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30) NOT NULL,
    height INT NOT NULL,
    country VARCHAR(30) NOT NULL,
    region VARCHAR(30) NOT NULL
)
GO

CREATE TABLE climbers
(
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30) NOT NULL,
    addr VARCHAR(30) NOT NULL
)
GO

CREATE TABLE regs
(
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    startdate DATE NOT NULL,
    enddate DATE NOT NULL,
    mountain_id INT NOT NULL FOREIGN KEY REFERENCES mountains(id),
    climber_id INT NOT NULL FOREIGN KEY REFERENCES climbers(id),
    group_id INT NOT NULL
)
GO

INSERT INTO mountains (name, height, country, region)
VALUES
('Эверест', 8848, 'Америка', 'Нью Йорк'),
('Шунут', 2454, 'Россия', 'Урал'),
('Ежовая', 1800, 'Россия', 'Краснодарский край')
GO

INSERT INTO climbers (name, addr)
VALUES
('Антон', 'Шаумяна 19-33'),
('Джон', 'Уолл стрит 12-44'),
('Алексей', 'Посадская 14-88'),
('Глеб', 'Малышева 22-16')
GO

INSERT INTO regs (startdate, enddate, mountain_id, climber_id, group_id)
VALUES
('2011-11-10', '2011-11-11', 1, 2, 1),
('2011-11-10', '2011-11-11', 1, 3, 1),
('2012-11-16', '2012-11-19', 2, 4, 2),
('2012-06-19', '2012-06-19', 2, 1, 3),
('2012-06-19', '2012-06-19', 2, 4, 3)
GO

------------------------------------------------------------------------------

CREATE TRIGGER climber_on_one_mountain
ON regs
FOR INSERT
AS
BEGIN
    DECLARE @COUNT INT = (SELECT COUNT(r.id) FROM inserted AS i, regs AS r WHERE
        i.id != r.id AND i.climber_id = r.climber_id AND
        ((DATEDIFF(DAY, r.startdate, i.startdate) >= 0 AND DATEDIFF(DAY, i.enddate, r.startdate) >= 0) OR
        (DATEDIFF(DAY, i.startdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, i.startdate) >= 0) OR
        (DATEDIFF(DAY, i.enddate, r.enddate) >= 0 AND DATEDIFF(DAY, r.enddate, i.startdate) >= 0) OR
        (DATEDIFF(DAY, r.enddate, i.enddate) >= 0 AND DATEDIFF(DAY, i.enddate, r.startdate) >= 0)));
    IF (@COUNT > 0)
    BEGIN
        print 'Альпинист уже забирается на другую гору!'
        ROLLBACK TRANSACTION
    END
END
GO

INSERT INTO regs (startdate, enddate, mountain_id, climber_id, group_id)
VALUES ('2011-11-10', '2011-11-10', 1, 2, 1)

DROP TRIGGER climber_on_one_mountain
GO

-----------------------------------------------------------------------------------

DROP FUNCTION get_mountains
DROP FUNCTION get_groups
GO

CREATE FUNCTION get_mountains(@sdate DATE, @edate DATE)
RETURNS @mountains TABLE
(
    name VARCHAR(30) NOT NULL,
    date_of_last_reg DATE
)
AS
BEGIN
    INSERT @mountains
    SELECT m.name, MIN(r.startdate) FROM regs AS r JOIN mountains AS m ON r.mountain_id = m.id WHERE
        DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0
    GROUP BY m.name
    RETURN;
END;
GO

CREATE FUNCTION get_groups(@sdate DATE, @edate DATE, @mount_name VARCHAR(30))
RETURNS @groups TABLE
(
    group_number VARCHAR(30) NOT NULL,
    startdate DATE
)
AS
BEGIN
    INSERT @groups
    SELECT DISTINCT r.group_id, r.startdate FROM regs AS r
        WHERE r.mountain_id IN (SELECT id FROM mountains WHERE name=@mount_name) AND
        (DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0)
    RETURN;
END;
GO


DECLARE @sdate DATE = '2000-01-01'
DECLARE @edate DATE = '2015-01-01'

------

DECLARE @Mountains_cursor CURSOR
SET @Mountains_cursor = CURSOR
FOR
    SELECT * FROM get_mountains(@sdate, @edate)
OPEN @Mountains_cursor

DECLARE @mount_name VARCHAR(30),
        @date_ofLast_reg DATE,
        @group_number VARCHAR(30),
        @startdate DATE

FETCH @Mountains_cursor INTO @mount_name, @date_ofLast_reg
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '-----------------------'
    print @mount_name + ' ' + CAST(@date_ofLast_reg AS VARCHAR(30))

        DECLARE @Groups_cursor CURSOR
        SET @Groups_cursor = CURSOR
        FOR
            SELECT * FROM get_groups(@sdate, @edate, @mount_name) ORDER BY startdate
        OPEN @Groups_cursor
        fetch @Groups_cursor INTO @group_number, @startdate
        IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT '         ' + CAST(@group_number AS VARCHAR(10)) + ' ' + CAST(@startdate AS VARCHAR(12))
            fetch @Groups_cursor INTO @group_number, @startdate
        END
        close @Groups_cursor

    print ' '
    FETCH @Mountains_cursor INTO @mount_name, @date_ofLast_reg
END
close @Mountains_cursor
GO

------------------------------------------------------------------------

DROP FUNCTION get_climbers
DROP FUNCTION get_climber_mountains
GO

CREATE FUNCTION get_climbers(@sdate DATE, @edate DATE)
RETURNS @climbers TABLE
(
    name VARCHAR(30) NOT NULL
)
AS
BEGIN
    INSERT @climbers
    SELECT c.name FROM regs AS r JOIN climbers AS c ON c.id = r.climber_id WHERE
        DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0
    GROUP BY c.name
    RETURN;
END;
GO

CREATE FUNCTION get_climber_mountains(@sdate DATE, @edate DATE, @climber_name VARCHAR(30))
RETURNS @groups_and_mountains TABLE
(
    mountain_name VARCHAR(30) NOT NULL,
    group_number VARCHAR(30) NOT NULL,
    startdate DATE
)
AS
BEGIN
    INSERT @groups_and_mountains
    SELECT DISTINCT m.name, r.group_id, r.startdate FROM regs AS r JOIN mountains AS m ON m.id = r.mountain_id
        WHERE r.climber_id IN (SELECT id FROM climbers WHERE name=@climber_name) AND
        (DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0)
    RETURN;
END;
GO

---

DECLARE @sdate DATE = '2000-01-01'
DECLARE @edate DATE = '2015-01-01'

------

DECLARE @Climbers_cursor CURSOR
SET @Climbers_cursor = CURSOR
FOR
    SELECT * FROM get_climbers(@sdate, @edate)
OPEN @Climbers_cursor

DECLARE @climber_name VARCHAR(30),
        @mount_number VARCHAR(30),
        @group_number VARCHAR(30),
        @startdate DATE

FETCH @Climbers_cursor INTO @climber_name
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '-----------------------'
    print @climber_name

        DECLARE @Groups_cursor CURSOR
        SET @Groups_cursor = CURSOR
        FOR
            SELECT * FROM get_climber_mountains(@sdate, @edate, @climber_name) ORDER BY startdate
        OPEN @Groups_cursor
        fetch @Groups_cursor INTO @mount_number, @group_number, @startdate
        IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT '         ' + @mount_number + ' ' + CAST(@group_number AS VARCHAR(10)) + ' ' + CAST(@startdate AS VARCHAR(12))
            fetch @Groups_cursor INTO @mount_number, @group_number, @startdate
        END
        close @Groups_cursor

    print ' '
    FETCH @Climbers_cursor INTO @climber_name
END
close @Climbers_cursor
GO

-------------------------------------------------------------------------------

SELECT COUNT(*) AS coun, c.name, m.name FROM climbers AS c JOIN regs AS r ON r.climber_id = c.id JOIN mountains AS m ON m.id = r.mountain_id
GROUP BY c.name, m.name
ORDER BY coun DESC
GO

-------------------------------------------------------------------------------

DROP FUNCTION get_groups
DROP FUNCTION get_group_p
GO

CREATE FUNCTION get_groups(@sdate DATE, @edate DATE)
RETURNS @groups TABLE
(
    group_id INT NOT NULL
)
AS
BEGIN
    INSERT @groups
    SELECT DISTINCT r.group_id FROM regs AS r WHERE
        DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0
    RETURN;
END;
GO

CREATE FUNCTION get_group_p(@sdate DATE, @edate DATE, @group_id INT)
RETURNS @paticipants TABLE
(
    name VARCHAR(30)
)
AS
BEGIN
    INSERT @paticipants
    SELECT c.name FROM regs AS r JOIN climbers AS c ON c.id = r.climber_id
        WHERE r.group_id = @group_id AND
        (DATEDIFF(DAY, @sdate, r.startdate) >= 0 AND DATEDIFF(DAY, r.enddate, @edate) >= 0)
    RETURN;
END;
GO

---

DECLARE @sdate DATE = '2000-01-01'
DECLARE @edate DATE = '2015-01-01'

------

DECLARE @Group_cursor CURSOR
SET @Group_cursor = CURSOR
FOR
    SELECT * FROM get_groups(@sdate, @edate)
OPEN @Group_cursor

DECLARE @group_id INT,
        @climber_name VARCHAR(30)

FETCH @Group_cursor INTO @group_id
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '-----------------------'
    print @group_id

        DECLARE @Pat_cursor CURSOR
        SET @Pat_cursor = CURSOR
        FOR
            SELECT * FROM get_group_p(@sdate, @edate, @group_id)
        OPEN @Pat_cursor
        fetch @Pat_cursor INTO @climber_name
        IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT '         ' + @climber_name
            fetch @Pat_cursor INTO @climber_name
        END
        close @Pat_cursor

    print ' '
    FETCH @Group_cursor INTO @group_id
END
close @Group_cursor
GO

--------------------------------------------------------------------------------------------

SELECT m.name, COUNT(r.climber_id) FROM regs AS r JOIN mountains AS m ON m.id = r.mountain_id
GROUP BY m.name
ORDER BY m.name
GO

----------------------------------------------------------------------------------------------

DROP TABLE regs
DROP TABLE mountain
DROP TABLE climber