SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- Создание таблицы сотрудников 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

Stuck with a problem? Got Error? Ask ChatGPT!

Copy Clear