Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
livesid
Member

Откуда:
Сообщений: 5
Добрый день. Подскажите как выполнить запрос.

Есть документ
_Document6395 - Документ.ТоварнаяНоменклатураСборки
У него есть табличная часть, где указаны номенклатуры и план (количество) по каждой позиции. !!!Цена и итог не указано!!!
_Document6395_VT6404 - Документ.ТоварнаяНоменклатураСборки.НоменклатураТЧ

задача вывести список или в моем случае общую сумму: количество, ценаДействующая, ценаСтабильная. (ценаДействующая и ценаСтабильная - это виды цен в таблице _InfoRg3676 - РегистрСведений.ЦеныНоменклатуры). Где цена документа указана на момент даты документа Документ.ТоварнаяНоменклатураСборки.

Смог только реализовать, сумму: количество, ценаДействующая, ценаСтабильная по цене на текущий день.
Как брать цену в регистре сведений - ЦеныНоменклатуры на момент даты документа Документ.ТоварнаяНоменклатураСборки?


--План с документа - Документ.ТоварнаяНоменклатураСборки
DECLARE @period date;
SET @period='01.01.4019';

WITH tm_price(id_nomen, nomenclature, id_type, type_price, cost)
	as(
		SELECT T1._nomen as id_nomen, nomen._Description as nomenclature, T1._type as id_type, typePrice._Description as type_price, T1.cost as cost
		FROM (
			SELECT	T4._period as _period,
					T4._Fld3677RRef as _nomen,
					T4._Fld3678RRef as _type,
					T4._Fld3680 as cost
			FROM (
				SELECT  T3._Fld3677RRef as _nomen, T3._Fld3678RRef as _type,
						max(T3._period) as max_period
				FROM _InfoRg3676 as T3
				GROUP BY T3._Fld3677RRef, T3._Fld3678RRef
				) as T2
			INNER JOIN _InfoRg3676 as T4 on T2._nomen = T4._Fld3677RRef and T2._type = T4._Fld3678RRef and T2.max_period = T4._Period) T1
			LEFT JOIN _Reference25 as nomen on T1._nomen = nomen._IDRRef --Справочник.Номенклатура
			LEFT JOIN _Reference56 as typePrice on T1._type = typePrice._IDRRef
		WHERE typePrice._Description in ('Действующая', 'Стабильная')
	)


SELECT doc_plan._Fld6397 as date_plan, doc_plan._Fld6398 as date_plan_month,
	sum(tp_doc_plan._Fld6407) as amount_plan, sum(tp_doc_plan._Fld6407*priceCurrent.cost) as sum_current_plan, sum(tp_doc_plan._Fld6407*priceStable.cost) as sum_stable_plan
FROM _Document6395 as doc_plan --Документ.ТоварнаяНоменклатураСборки
	LEFT JOIN _Document6395_VT6404 as tp_doc_plan on doc_plan._IDRRef = tp_doc_plan._Document6395_IDRRef --Документ.ТоварнаяНоменклатураСборки.НоменклатураТЧ
	LEFT JOIN _Reference25 as nomen on tp_doc_plan._Fld6406RRef = nomen._IDRRef --Справочник.Номенклатура
	LEFT JOIN _Reference25 as nomen_parent on nomen_parent._IDRRef = nomen._ParentIDRRef --Справочник.Номенклатура.Родитель
	LEFT JOIN tm_price as priceCurrent on priceCurrent.id_nomen = tp_doc_plan._Fld6406RRef and priceCurrent.type_price = 'Действующая'
	LEFT JOIN tm_price as priceStable on priceStable.id_nomen = tp_doc_plan._Fld6406RRef and priceStable.type_price = 'Стабильная'
WHERE CONVERT(varchar(max), doc_plan._marked, 2) = '00' and CONVERT(varchar(max), doc_plan._posted, 2) = '01'
	and CAST(doc_plan._date_time as date) >= @period
	and nomen_parent._Code NOT IN (27764, 29491)
GROUP BY doc_plan._Fld6397, doc_plan._Fld6398
19 ноя 19, 08:25    [22019302]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 744
livesid,

Конечно, не видя данных перед глазами комплексно советы давать - но сходу - а почему бы не делать inner join к РС [ЦеныНоменклатуры] по периоду через BETWEEN - к справочнику цен, у которого есть ДатаНачалаСрокаДействияЦены и ДатаКонцаСрокаДействияЦены?
Джойним [Документ.ТоварнаяНоменклатураСборки] и [Справочник.Номенклатура] - по ID номенклатуры. Из [Документ.ТоварнаяНоменклатураСборки] получаем фактическую дату и ведем ее в [ЦеныНоменклатуры] используя BETWEEN между границами?
Если Даты окончания периода нет - можно предварительно этот регистр обработать в CTE, искусственно создав для него крайнюю границу как максимальное ДатаВремя
Получатся такие островки, куда можно будет попадать извне, как бы

Если я правильно понял задачу, естественно

Сообщение было отредактировано: 19 ноя 19, 09:18
19 ноя 19, 09:17    [22019321]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 744
PsyMisha
livesid,

Конечно, не видя данных перед глазами комплексно советы давать - но сходу - а почему бы не делать inner join к РС [ЦеныНоменклатуры] по периоду через BETWEEN - к справочнику цен, у которого есть ДатаНачалаСрокаДействияЦены и ДатаКонцаСрокаДействияЦены?
Джойним [Документ.ТоварнаяНоменклатураСборки] и [Справочник.Номенклатура] - по ID номенклатуры. Из [Документ.ТоварнаяНоменклатураСборки] получаем фактическую дату и ведем ее в [ЦеныНоменклатуры] используя BETWEEN между границами?
Если Даты окончания периода нет - можно предварительно этот регистр обработать в CTE, искусственно создав для него крайнюю границу как максимальное ДатаВремя
Получатся такие островки, куда можно будет попадать извне, как бы

Если я правильно понял задачу, естественно
19 ноя 19, 09:17    [22019325]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
livesid
Member

Откуда:
Сообщений: 5
Попытался накидать в ексель.

BETWEEN - хорошая идея, если правильно понял, создаем виртуальную таблицу Price, где будут указаны периоды начала и конца действия цены для номенклатуры?

К сообщению приложен файл. Размер - 60Kb
19 ноя 19, 09:38    [22019339]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 744
livesid,

Ну как-то так, да

Вообще, по идее - такие сущности, как ЦеныНоменклатуры должны изначально, с уровня источника данных - идти с периодами, даже в 1С они есть. Под рукой кода сейчас нет, - пороюсь в архивах - если найду - то продемонстрирую

А насчет паттерна соваться в справочник к его датам через BETWEEN с даты, идущей с факта - да, это вполне рациональное рабочее решение
19 ноя 19, 09:51    [22019350]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
1C Developer
Member

Откуда:
Сообщений: 65
livesid
Как брать цену в регистре сведений - ЦеныНоменклатуры на момент даты документа Документ.ТоварнаяНоменклатураСборки?


РегистрСведений.ЦеныНоменклатуры - периодический, Вам нужен срез последних на дату документа, т.е. по интересующим Вас номенклатурам нужно найти значения цен с максимальной датой меньшей либо равной дате документа.

P.S. А почему бы Вам не решить данную задачу на стороне 1С?

P.P.S. Для t-sql есть универсальный способ представления даты - YYYYMMDD - лучше всегда пользоваться им, а не

SET @period='01.01.4019';
19 ноя 19, 10:05    [22019357]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
livesid
Member

Откуда:
Сообщений: 5
Сделал так. Буду связывать через between
+2000 год, это так храниться в бд

WITH tm_price
	as (
		SELECT TOP 1000  ROW_NUMBER() OVER(PARTITION BY nomen._Description, typePrice._Description ORDER BY price._Period ASC) AS RowNumber,
			price._Period as date_price, nomen._Description as nomen, typePrice._Description as type_price, price._Fld3680 as cost
		FROM _InfoRg3676 as price
			LEFT JOIN _Reference25 as nomen on price._Fld3677RRef = nomen._IDRRef --Справочник.Номенклатура
			LEFT JOIN _Reference56 as typePrice on price._Fld3678RRef = typePrice._IDRRef
		WHERE typePrice._Description in ('Действующая', 'Стабильная')	
		)

SELECT tm1.RowNumber, tm1.nomen, tm1.type_price, tm1.cost, 
	tm1.date_price, ISNULL(tm2.date_price, DATEADD(year, +2000, GETDATE())) as date_price2
FROM tm_price as tm1
	LEFT JOIN tm_price as tm2 on tm1.nomen = tm2.nomen and tm1.type_price = tm2.type_price and tm1.RowNumber = tm2.RowNumber-1
19 ноя 19, 10:26    [22019378]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
livesid
Member

Откуда:
Сообщений: 5
1C Developer,
к сожалению на стороне 1с, никто делать не будет
19 ноя 19, 10:27    [22019382]     Ответить | Цитировать Сообщить модератору
 Re: Запрос к ms sql с базой данных 1с. Прайс-лист  [new]
livesid
Member

Откуда:
Сообщений: 5
1C Developer,

SET @period='40190101'; - так имеется в виду?
19 ноя 19, 10:29    [22019385]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить