Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 А может можно проще?  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Есть таблица движений товаров. Необходимо одним запросом вытащить информацию о начальном остатке на начало периода, приходе, расходе и конечном остатке.
Уже есть 3 работающих небольших запросика:

Начальный остаток:

SELECT product_id, storage_id, SUM(quantityNO) quantityNO
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE -[4wd].dbo.RegProducts.quantity END quantityNO
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK)
ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date < CONVERT(datetime, '03.06.2003 00:00:00', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityNO) <> 0


Приход, расход:

SELECT product_id, storage_id, SUM(quantityPrih) quantityPrih, SUM(quantityRash) quantityRash
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE 0 END quantityPrih
, CASE WHEN DEBKRED = 2 THEN [4wd].dbo.RegProducts.quantity ELSE 0 END quantityRash
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK) ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date >= CONVERT(datetime, '03.06.2003 00:00:00', 104)
AND [4wd].dbo.Docs.doc_date <= CONVERT(datetime, '03.06.2003 23:59:59', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityPrih) <> 0 or SUM(quantityRash) <> 0


Конечный остаток:

SELECT product_id, storage_id, SUM(quantityKO) quantityKO
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE -[4wd].dbo.RegProducts.quantity END quantityKO
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK) ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date <= CONVERT(datetime, '03.06.2003 23:59:59', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityKO) <> 0


Когда все их объединяю JOIN-ами, всё работает, но запрос получается большой и неприятный на ощупь.

SELECT [NO].product_id product_id,
[NO].storage_id storage_id,
[NO].quantityNO quantityNO,
[Moves].quantityPrih quantityPrih,
[Moves].quantityRash quantityRash,
[KO].quantityKO quantityKO
FROM
(
-- НачОст

SELECT product_id, storage_id, SUM(quantityNO) quantityNO
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE -[4wd].dbo.RegProducts.quantity END quantityNO
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK)
ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date < CONVERT(datetime, '03.06.2003 00:00:00', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityNO) <> 0
) as [NO]
JOIN
(
-- Приход, Расход

SELECT product_id, storage_id, SUM(quantityPrih) quantityPrih, SUM(quantityRash) quantityRash
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE 0 END quantityPrih
, CASE WHEN DEBKRED = 2 THEN [4wd].dbo.RegProducts.quantity ELSE 0 END quantityRash
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK) ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date >= CONVERT(datetime, '03.06.2003 00:00:00', 104)
AND [4wd].dbo.Docs.doc_date <= CONVERT(datetime, '03.06.2003 23:59:59', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityPrih) <> 0 or SUM(quantityRash) <> 0
) as [Moves]
ON ([NO].product_id = [Moves].product_id and [NO].storage_id = [Moves].storage_id)
JOIN
(
-- КонОст

SELECT product_id, storage_id, SUM(quantityKO) quantityKO
FROM (
SELECT [4wd].dbo.RegProducts.product_id product_id, [4wd].dbo.RegProducts.storage_id storage_id
, CASE WHEN DEBKRED = 1 THEN [4wd].dbo.RegProducts.quantity ELSE -[4wd].dbo.RegProducts.quantity END quantityKO
FROM [4wd].dbo.Docs(NOLOCK) LEFT OUTER JOIN
[4wd].dbo.RegProducts(NOLOCK) ON [4wd].dbo.Docs.Docs_id = [4wd].dbo.RegProducts.doc_id
WHERE [4wd].dbo.Docs.RegProducts = 1
AND [4wd].dbo.Docs.doc_date <= CONVERT(datetime, '03.06.2003 23:59:59', 104)
AND [4wd].dbo.Docs.accepted = 1 AND [4wd].dbo.RegProducts.storage_id = 35
AND product_id in(34447148, 34447149, 34447150)
) AS tmp
GROUP BY storage_id, product_id
HAVING SUM(quantityKO) <> 0
) as [KO]
ON ([NO].product_id = [KO].product_id and [NO].storage_id = [KO].storage_id)

Чувствую, что можно сделать красивше.. Намекните.
4 июн 03, 11:39    [221146]     Ответить | Цитировать Сообщить модератору
 Re: А может можно проще?  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Для начала избавьтесь от конструкции SELECT ... FROM (SELECT
И не бойтесь использовать временные таблицы
4 июн 03, 12:12    [221214]     Ответить | Цитировать Сообщить модератору
 Re: А может можно проще?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Есть таблица движений товаров. Необходимо одним запросом вытащить информацию о начальном остатке на начало периода, приходе, расходе и конечном остатке.

Ну вроде вчера тока на эту тему базарили. Создай таблицу с нужной тебе структурой и туда триггерами предрасчитывай данные, чтоб после каждой операции информация об остатке на начало периода, приходе, расходе и конечном остатке была актуальной.
4 июн 03, 13:17    [221348]     Ответить | Цитировать Сообщить модератору
 Re: А может можно проще?  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
2 pkarklin
Не, понятно, я просто набирал необходимую теоритическую базу относительно сноса остатков, у меня это пока еще не реализовано, так что пока приходится запрос делать именно по такой структуре. Именно по такой структуре всё нормально? или можно оптимизировать, как думаешь?
4 июн 03, 13:53    [221429]     Ответить | Цитировать Сообщить модератору
 Re: А может можно проще?  [new]
Дамир
Guest
2ale-805:
Ничё, если псевдокодом кину ( с твоим кодом влом разбираться).
Выбирается все 1 запросом.
Основной прием - проверка даты проводки внутри case ... end


select
A, B, C,
Входящеесальдо = SUM(
case
when ТД.ДатаПроводки < ДатаНачПериода then ТД.Количество
else 0.00
end
),
Приход = SUM(
case
when ТД.Количество > 0 and ТД.ДатаПроводки between ДатаНачПериода and ДатаКонПериода then ТД.Количество
else 0.00
end
),
Расход = - SUM(
case
when ТД.Количество < 0 and ТД.ДатаПроводки between ДатаНачПериода and ДатаКонПериода then ТД.Количество
else 0.00
end
)
.....
from ТаблицаДвижений ТД
where ТД.ДатаПроводки <= ДатаКонПериода
group by A, B, C
4 июн 03, 14:10    [221475]     Ответить | Цитировать Сообщить модератору
 Re: А может можно проще?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Именно по такой структуре всё нормально? или можно оптимизировать, как думаешь?

Ну могу тока поддержать SergSuper, я б тоже стал считать в хп со временными таблицами. Тогда хоть запросы можно было разбить на меньшие куски. Хоть вьехать в них легче будет.
4 июн 03, 14:11    [221481]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить