-- Создание таблицы сотрудников
CREATE TABLE users (
user_id INT PRIMARY KEY IDENTITY(1,1),
surname NVARCHAR(50) NOT NULL,
name NVARCHAR(50) NOT NULL,
hire_date DATE NOT NULL,
dismissal_date DATE NULL
);
-- Создание таблицы расчетных периодов
CREATE TABLE periods (
period_id INT PRIMARY KEY IDENTITY(1,1),
start_date DATE NOT NULL,
end_date DATE NOT NULL
);
-- Создание таблицы статусов выплат
CREATE TABLE payment_state (
state_id INT PRIMARY KEY IDENTITY(1,1),
state_name NVARCHAR(50) NOT NULL
);
-- Создание таблицы статей затрат
CREATE TABLE cost_item (
cost_item_id INT PRIMARY KEY IDENTITY(1,1),
cost_item_name NVARCHAR(100) NOT NULL,
not_balance BIT NOT NULL DEFAULT 0
);
-- Создание таблицы ежемесячных начислений
CREATE TABLE monthly_accrual (
accrual_id INT PRIMARY KEY IDENTITY(1,1),
user_id INT NOT NULL,
period_id INT NOT NULL,
amount DECIMAL(16,2) NOT NULL,
state_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (period_id) REFERENCES periods(period_id),
FOREIGN KEY (state_id) REFERENCES payment_state(state_id)
);
-- Создание таблицы дополнительных выплат
CREATE TABLE additional_payment (
payment_id INT PRIMARY KEY IDENTITY(1,1),
user_id INT NOT NULL,
period_id INT NOT NULL,
cost_item_id INT NOT NULL,
amount DECIMAL(16,2) NOT NULL,
state_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (period_id) REFERENCES periods(period_id),
FOREIGN KEY (cost_item_id) REFERENCES cost_item(cost_item_id),
FOREIGN KEY (state_id) REFERENCES payment_state(state_id)
);
-- Заполнение таблицы сотрудников
INSERT INTO users (surname, name, hire_date, dismissal_date)
VALUES
('Иванов', 'Иван', '2009-11-23', NULL),
('Петрова', 'Елена', '2011-10-01', '2020-07-26'),
('Крыжовников', 'Евгений', '2022-01-15', NULL),
('Иванова', 'Александра', '2024-06-07', NULL),
('Сидоров', 'Матвей', '2015-12-02', '2024-09-21'),
('Петрова', 'Елена', '2024-04-29', NULL);
-- Заполнение таблицы расчетных периодов
INSERT INTO periods (start_date, end_date)
VALUES
('2024-06-01', '2024-06-30'),
('2024-07-01', '2024-07-31'),
('2024-08-01', '2024-08-31'),
('2024-09-01', '2024-09-30'),
('2024-10-01', '2024-10-31');
-- Заполнение таблицы статусов выплат
INSERT INTO payment_state (state_name)
VALUES
('Новая'),
('На проверке'),
('Получено'),
('Отклонено');
-- Заполнение таблицы статей затрат
INSERT INTO cost_item (cost_item_name, not_balance)
VALUES
('Премия', 0),
('Материальные расходы', 0),
('Маркетинг и реклама', 1),
('Аренда и обслуживание', 0),
('Прочие расходы', 1);
-- Заполнение таблицы ежемесячных начислений
INSERT INTO monthly_accrual (user_id, period_id, amount, state_id)
VALUES
(1, 3, 60000, 3), -- Иванов Иван, Август 2024
(3, 3, 75000, 3), -- Крыжовников Евгений, Август 2024
(4, 3, 140000, 3), -- Иванова Александра, Август 2024
(5, 3, 80000, 3), -- Сидоров Матвей, Август 2024
(6, 3, 100000, 3), -- Петрова Елена, Август 2024
(1, 4, 60000, 3), -- Иванов Иван, Сентябрь 2024
(3, 4, 75000, 3), -- Крыжовников Евгений, Сентябрь 2024
(4, 4, 140000, 3), -- Иванова Александра, Сентябрь 2024
(5, 4, 80000, 3), -- Сидоров Матвей, Сентябрь 2024
(6, 4, 100000, 3), -- Петрова Елена, Сентябрь 2024
(1, 5, 60000, 2), -- Иванов Иван, Октябрь 2024
(3, 5, 75000, 2), -- Крыжовников Евгений, Октябрь 2024
(4, 5, 140000, 2), -- Иванова Александра, Октябрь 2024
(6, 5, 100000, 2); -- Петрова Елена, Октябрь 2024
-- Заполнение таблицы дополнительных выплат
INSERT INTO additional_payment (user_id, period_id, cost_item_id, amount, state_id)
VALUES
(1, 3, 2, 4500, 4), -- Иванов Иван, Август 2024, Материальные расходы, Отклонено
(6, 3, 1, 10000, 3), -- Петрова Елена, Август 2024, Премия, Получено
(6, 4, 1, 15000, 3), -- Петрова Елена, Сентябрь 2024, Премия, Получено
(1, 5, 5, 5000, 2), -- Иванов Иван, Октябрь 2024, Прочие расходы, На проверке
(3, 5, 1, 10000, 1), -- Крыжовников Евгений, Октябрь 2024, Премия, Новая
(6, 3, 1, 2500, 2); -- Петрова Елена, Август 2024, Премия, На проверке
CREATE PROCEDURE balans_details
@period_id INT, -- Параметр для выбора расчетного периода
@user_id INT = NULL -- Параметр для выбора конкретного сотрудника (опционально)
AS
BEGIN
SELECT
emp.name + ' ' + emp.surname AS [Сотрудник],
SUM(ISNULL(dop.amount, 0)) + SUM(ISNULL(nach.amount, 0)) AS [Начислено],
SUM(ISNULL(poluch_dop.amount, 0)) + SUM(ISNULL(poluch_nach.amount, 0)) AS [Получено]
FROM users emp
-- Исключаем уволенную Петрову Елену
LEFT JOIN users prev_emp
ON emp.surname = prev_emp.surname
AND emp.name = prev_emp.name
AND prev_emp.dismissal_date IS NOT NULL
-- Дополнительные выплаты
LEFT JOIN (
SELECT
user_id,
SUM(amount) AS amount
FROM additional_payment dop
JOIN cost_item ci
ON dop.cost_item_id = ci.cost_item_id
AND ci.not_balance = 0
WHERE
dop.period_id = @period_id
AND dop.state_id IN (1, 2, 3) -- Только актуальные статусы
GROUP BY user_id
) dop ON emp.user_id = dop.user_id
-- Начисления
LEFT JOIN (
SELECT
user_id,
SUM(amount) AS amount
FROM monthly_accrual nach
WHERE
nach.period_id = @period_id
AND nach.state_id IN (1, 2, 3) -- Только актуальные статусы
GROUP BY user_id
) nach ON emp.user_id = nach.user_id
-- Получение доп. выплат
LEFT JOIN (
SELECT
user_id,
SUM(amount) AS amount
FROM additional_payment poluch_dop
JOIN cost_item ci
ON poluch_dop.cost_item_id = ci.cost_item_id
AND ci.not_balance = 0
WHERE
poluch_dop.state_id = 3 -- Только полученные выплаты
AND poluch_dop.period_id = @period_id
GROUP BY user_id
) poluch_dop ON emp.user_id = poluch_dop.user_id
-- Получение начислений
LEFT JOIN (
SELECT
user_id,
SUM(amount) AS amount
FROM monthly_accrual poluch_nach
WHERE
poluch_nach.state_id = 3 -- Только полученные начисления
AND poluch_nach.period_id = @period_id
GROUP BY user_id
) poluch_nach ON emp.user_id = poluch_nach.user_id
WHERE
prev_emp.user_id IS NULL -- Исключаем уволенную Петрову Елену
AND (@user_id IS NULL OR emp.user_id = @user_id) -- Фильтр по сотруднику
GROUP BY emp.name, emp.surname -- Группируем по имени и фамилии
ORDER BY emp.surname; -- Сортируем по фамилии
END
Telegram: DB developers channel