Пара слов про конструкцию VALUES

 

SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent

FROM

(

VALUES

(1001,'20140930',300),

(1002,'20140331',500),

(1002,'20140630',500),

(1002,'20140930',500),

(1002,'20141230',500),

(1002,'20150331',500),

(1004,'20140831',200)

) indiv(EmployeeID,BonusDate,BonusAmount)

 

В случае необходимости, данную конструкцию можно заменить, аналогичным запросом, написанным через UNION ALL:

SELECT 1001 EmployeeID,'20140930' BonusDate,300 BonusAmount,3 BonusTypeID,NULL BonusPercent

UNION ALL

SELECT 1002,'20140331',500,3,NULL

UNION ALL

SELECT 1002,'20140630',500,3,NULL

UNION ALL

SELECT 1002,'20140930',500,3,NULL

UNION ALL

SELECT 1002,'20141230',500,3,NULL

UNION ALL

SELECT 1002,'20150331',500,3,NULL

UNION ALL

SELECT 1004,'20140831',200,3,NULL

 

Думаю, комментарии излишни и вам не составит большого труда разобраться с этим самостоятельно.

Так что, идем дальше.

INSERT + CTE-выражения

Совместно с INSERT можно применять CTE выражения. Для примера перепишем тот же запрос перенеся все подзапросы в блок WITH.

Для начала полностью очистим таблицу EmployeesBonus при помощи операции TRUNCATE TABLE:

TRUNCATE TABLE EmployeesBonus

 

Теперь перепишем запрос вынеся запросы в блок WITH:

WITH cteBonusType1 AS(

-- расчет ежемесячных бонусов

SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent BonusAmount,1 BonusTypeID,emp.BonusPercent

FROM EmployeesSalaryHistory hist

JOIN

(

VALUES -- весь период работы компании - последние дни месяцев

   ('20131130'),

   ('20131231'),

   ('20140131'),

   ('20140228'),

   ('20140331'),

   ('20140430'),

   ('20140531'),

   ('20140630'),

   ('20140731'),

   ('20140831'),

   ('20140930'),

   ('20141031'),

   ('20141130'),

   ('20141230'),

   ('20150131'),

   ('20150228'),

   ('20150331')

) bdate(BonusDate)

ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')

JOIN Employees emp ON hist.EmployeeID=emp.ID

WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0

AND NOT EXISTS(-- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период

           SELECT *

           FROM

             (

               VALUES

                 (1001,'20140115'),

                 (1001,'20140430'),

                 (1001,'20141031'),

                 (1001,'20141130'),

                 (1001,'20150228')

            ) exclude(EmployeeID,BonusDate)

           WHERE exclude.EmployeeID=emp.ID

             AND exclude.BonusDate=bdate.BonusDate

        )

),

cteBonusType2 AS(

-- годовой бонус за 2014 год - всем кто проработал больше полугода

SELECT

hist.EmployeeID,

'20141231' BonusDate,

hist.Salary/100*

 CASE DepartmentID

WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusAmount,

2 BonusTypeID,

CASE DepartmentID

WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusPercent

FROM EmployeesSalaryHistory hist

JOIN Employees emp ON hist.EmployeeID=emp.ID

WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')

AND emp.HireDate<='20140601'

),

cteBonusType3 AS(

-- индивидуальные бонусы

SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent

FROM

(

VALUES

   (1001,'20140930',300),

   (1002,'20140331',500),

   (1002,'20140630',500),

   (1002,'20140930',500),

   (1002,'20141230',500),

   (1002,'20150331',500),

   (1004,'20140831',200)

) indiv(EmployeeID,BonusDate,BonusAmount)

)

 

INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent)

SELECT *

FROM cteBonusType1

UNION ALL

SELECT *

FROM cteBonusType2

UNION ALL

SELECT *

FROM cteBonusType3

 

Как видим вынос больших подзапросов в блок WITH упростил основной запрос – сделал его более понятным.

UPDATE – обновление данных

Данный оператор в MS SQL имеет 2 формы:

1. UPDATE таблица SET … WHERE условие_выборки – обновлении строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут обновлены все строки. Это можно сказать классическая форма оператора UPDATE.

2. UPDATE псевдоним SET … FROM … – обновление данных таблицы участвующей в предложении FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.

 

Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 6 отдельных операций UPDATE:

-- приведем даты приема в порядок

UPDATE Employees SET HireDate='20131101' WHERE ID=1000

 

UPDATE Employees SET HireDate='20131101' WHERE ID=1001

 

UPDATE Employees SET HireDate='20140101' WHERE ID=1002

 

UPDATE Employees SET HireDate='20140601' WHERE ID=1003

 

UPDATE Employees SET HireDate='20140701' WHERE ID=1004

 

-- а здесь еще почистим поле FirstName

UPDATE Employees SET HireDate='20150101',FirstName=NULL WHERE ID=1005

 

Вторую форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:

UPDATE e

SET

PositionID=(SELECT ID FROM Positions WHERE Name=e.Position),

DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department)

FROM Employees e

 

Сейчас конечно данный и следующий запрос не сработают, т.к. поля Position и Department мы удалили из таблицы Employees. Вот так можно было бы представить этот запрос при помощи операций соединений:

UPDATE e

SET

PositionID=p.ID,

DepartmentID=d.ID

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department

 

Надеюсь суть обновления здесь понятна, тут обновляться будут строки таблицы Employees.

Сначала вы можете сделать выборку, чтобы посмотреть какие данные будут обновлены и на какие значения:

SELECT

e.ID,

e.PositionID,e.DepartmentID, -- старые значения

e.Position,e.Department,

p.ID,d.ID, -- новые значения

p.Name,d.Name

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department

 

А потом переписать это в UPDATE:

UPDATE e

SET

PositionID=p.ID,

DepartmentID=d.ID

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department