Как избавиться от подзапросов? Выборка sql

Задание: Добавить новый заголовок документа (одной командой insert); -Номер документа = последний номер документа+1; -Дата=текущая дата; -Тип=расход,если документов типа "приход "больше ,чем документов типа "расход".В противном случае тип=приход. -Учесть,что в таблице DMZ изначально может не быть ни одной строки.
Таблица DMZ - документ.DDM - дата, NDM- номер документа,PR - приход/расход (1-приход, 2-расход)товара. Сам запрос есть :
INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE() DDM, (isnull((SELECT MAX(NDM) FROM DMZ), 0) + 1) NDM, CASE WHEN isnull((SELECT COUNT(*) FROM DMZ WHERE PR = 1), 0) > isnull((SELECT COUNT(*) FROM DMZ WHERE PR = 2), 0) THEN 2 ELSE 1 END

Запрос работает , но нужно его оптимизировать,заменив два запроса в операторе CASE на один.Как это сделать?
Вставляю диаграмму бд , если чем-то поможет.

Ответ

нужно его оптимизировать,заменив два запроса в операторе CASE на один. Как это сделать?
Можно получить COUNT для PR=1 и PR=2 одновременно с помощью PIVOT
;WITH DMZ1 AS (SELECT PR FROM DMZ) SELECT [1], [2] FROM DMZ1 PIVOT (COUNT(PR) FOR PR IN ([1], [2])) P;
Соответственно, исходный запрос может быть преобразован, например, в:
;WITH DMZ1 AS (SELECT PR FROM DMZ) INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE() DDM, (isnull((SELECT MAX(NDM) FROM DMZ), 0) + 1) NDM, CASE WHEN EXISTS( SELECT 1 FROM DMZ1 PIVOT (COUNT(PR) FOR PR IN ([1], [2])) P WHERE [1] > [2] ) THEN 2 ELSE 1 END;
Обычную группировку c CASE тоже можно использовать:
SELECT [1] = COUNT(CASE WHEN PR=1 THEN 1 END), [2] = COUNT(CASE WHEN PR=2 THEN 1 END) FROM DMZ;
Использование PIVOT иногда даёт более лаконичную запись, но CASE более гибок (PIVOT хотя и представляет собой синтаксический сахар, однако в SqlServer синтаксис PIVOT развит не настолько хорошо как, например, в Oracle).
Обычная группировка с CASE здесь будет даже предпочтительнее, поскольку позволит и максимальный номер документа достать в одном запросе:
SELECT MAXNDM = ISNULL(MAX(NDM), 0), [1] = COUNT(CASE WHEN PR=1 THEN 1 END), [2] = COUNT(CASE WHEN PR=2 THEN 1 END) FROM DMZ;
Соответственно, исходный запрос может быть преобразован в такой:
INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE(), ISNULL(MAX(NDM), 0) + 1, CASE WHEN COUNT(CASE WHEN PR=1 THEN 1 END) > COUNT(CASE WHEN PR=2 THEN 1 END) THEN 2 ELSE 1 END FROM DMZ;