Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 И снова покрытие заявок...(Условие немного изменилось)  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Есть таблица заявка:

create table  #Zayvka (Nomer INTEGER, orders Integer,Artikul NVARCHAR(10), shtuk INTEGER,Mesyac integer)
go
INSERT #Zayvka VALUES(1,3033, N'01Б0101', 33,8)
INSERT #Zayvka VALUES(2,3035, N'01Б0101', 20,9)
INSERT #Zayvka VALUES(3,3040, N'01Б0101', 50,10)
INSERT #Zayvka VALUES(1,3033, N'01Б0102', 5,8)
INSERT #Zayvka VALUES(2,3040, N'01Б0102', 7,10)

Есть таблица приход на эти заявки:

create table #Prihod (nomer integer, doc INTEGER, Artikul NVARCHAR(10), mesyac INTEGER, shtuk integer)
go
INSERT #Prihod VALUES(1,122,N'01Б0101',9,14)
INSERT #Prihod VALUES(2,123,N'01Б0101',9,15)
INSERT #Prihod VALUES(3,124,N'01Б0101',9,16)
INSERT #Prihod VALUES(4,125,N'01Б0101',10,5)
INSERT #Prihod VALUES(5,126,N'01Б0101',10,2)
INSERT #Prihod VALUES(6,127,N'01Б0101',11,15)
INSERT #Prihod VALUES(1,123,N'01Б0102',9,10)
INSERT #Prihod VALUES(2,125,N'01Б0102',10,7)

Нужно покрыть заявки, соответствующими приходами. В итоге получить таблицу:
nomerordersArtikulmesshtuk_zayvkashtuk_prihodraznicaflag_pokritia
1303301Б010183314+15+16=45121
2303501Б010192012+5+2=19-10
3304001Б0101105015-350
1303301Б0102851051
2304001Б0102107701


Т.е. рассматриваем первую строку из таблицы заявка:
INSERT #Zayvka VALUES(1,3033, N'01Б0101', 33,8)
откуда мы видим, что по заявке на август месяц (mesyac=8), заявлено 33 штуки данного артикула. Дальше из таблицы приход мы должны покрыть данную заявку, по следующему принципу: Ищем в таблице приход строку с этим же артикулом и чтобы месяц прихода был равен или больше на единицу месяца заявки. Т.е. первой строке из таблицы заявки, соответствует 3 строки из таблицы прихода:
INSERT #Prihod VALUES(1,122,N'01Б0101',9,14)
INSERT #Prihod VALUES(2,123,N'01Б0101',9,15)
INSERT #Prihod VALUES(3,124,N'01Б0101',9,16)
и начинаем соотносить: 14 штук артикула, из таблицы приход, первой строки, полностью ложатся на первую строку таблицы заявки, вторая строка из таблицы приход также полностью ложится на первую строку таблицы заявка, третья строка таблицы приход не полностью легла на первую строку таблицы заявки (только 4 штуки т.к. 14+15+4=33), осталось 12 штук из третей строки. Отсюда следует что первая строка таблицы заявки, покрыта полностью, записали флаг=1


Начинаем рассматривать вторую строку:
INSERT #Zayvka VALUES(2,3035, N'01Б0101', 20,9)
, ей удовлетворяет первые 5 строк таблицы приход:
INSERT #Prihod VALUES(1,122,N'01Б0101',9,14)
INSERT #Prihod VALUES(2,123,N'01Б0101',9,15)
INSERT #Prihod VALUES(3,124,N'01Б0101',9,16)
INSERT #Prihod VALUES(4,125,N'01Б0101',10,5)
INSERT #Prihod VALUES(5,126,N'01Б0101',10,2)
Но т.к. первые две строки из таблицы приход раскиданы полностью, начинаем рассматривать с третей строки, где осталось 12 штук данного артикула и начинаем соотносить по тому же принципу что и в первой строке.

И так рассматриваем каждую строку таблицы заявки, если мы не находим артикула в таблице приход, переходим на следующую строку.

Важно: Приходы раскидываются строго, чтобы месяц совпадал с месяцем заявки или был больше на один

в итоге получим таблицу, которую я указал выше.Вычисления которые происходят в столбце shtuk_prihod, производить не надо, нужен только итог, я их произвёл для наглядности.

Пожалуйста помогите, я уже больше месяца бьюсь над этим вопросом, делал рекурсией, но что-то не вышло..
16 окт 09, 16:10    [7797998]     Ответить | Цитировать Сообщить модератору
 Re: И снова покрытие заявок...(Условие немного изменилось)  [new]
Edkonst2008
Member

Откуда:
Сообщений: 503
minya13_85,

Может быть немного "маньячный подход", но все же:
1) Открываешь курсор в превой таблице
2) Считыешь все что надо и заносишь запись во временную таблицу
3) Из подчиненной таблицы (вернее, ее "зеркала") удаляешь обработанные записи
4) Переходишь к следующей записи и до конца.

Хотя может быть есть и другое решение...
16 окт 09, 16:26    [7798110]     Ответить | Цитировать Сообщить модератору
 Re: И снова покрытие заявок...(Условие немного изменилось)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
minya13_85,
За такое количество идентичный топиков вас забанят, если вы чуть меняете условия задачи, продолжайте в том же топике.

Первичные данные у вас ужасны, приведите таблицы к нормальной форме. Никто не будет помогать с таким уродством.

Топик перенасыщен императивом до мозгов костей, процедурномыслие это огромная ваша проблема, нельзя описывать суть задачи через КАК. Описание и как несовместимы. Как - это уже реализация, пусть оптимизатор планов запросов об этом побеспокоится, лишь потом вы ему поможете, если вдруг он споткнётся.

Если вы опишите нормальным человеческим языком что надо, то практически это уже и будет решением задачи. Останется лишь перевести с русского языка на язык структурированных запросов.

Описание через как в добавок не описывает задачу полностью, потому что вы плохо представляете суть задачи. У вас есть много не оговоренных "нюансов".

Вот вам
+ описание на SQL
DECLARE	@Product TABLE (
	 ID	Int	IDENTITY PRIMARY KEY
	,Code	SysName		NOT NULL
)
DECLARE	@Order TABLE (
	 ID	Int	PRIMARY KEY
	,Date	DateTime	NOT NULL
	,UNIQUE( Date
		,ID	-- Просто Date жестоко
	)
	,[Month] AS (Year(Date) * 100 + Month(Date))
	,UNIQUE( [Month]
		,ID
	)
)
DECLARE	@OrderProduct TABLE (
	 [Order]	Int
	,Product	Int
	,Quantity	Int	NOT NULL
	,PRIMARY KEY (
		 [Order]
		,Product
	)
)
DECLARE	@Delivery TABLE (
	 ID	Int	PRIMARY KEY
	,Date	DateTime	NOT NULL
	,UNIQUE( Date
		,ID	-- Просто Date жестоко
	)
	,[Month] AS (Year(Date) * 100 + Month(Date))
	,UNIQUE( [Month]
		,ID
	)
)
DECLARE	@DeliveryProduct TABLE (
	 Delivery	Int
	,Product	Int
	,Quantity	Int	NOT NULL
	,PRIMARY KEY (
		 Delivery
		,Product
	)
)
------------------------------------------------------
INSERT	@Product		SELECT '01Б0101'
UNION ALL			SELECT '01Б0102'

INSERT	@Order			SELECT 3033,'20090801'
UNION ALL			SELECT 3035,'20090902'
UNION ALL			SELECT 3040,'20091003'

INSERT	@OrderProduct		SELECT 3033,1,33
UNION ALL			SELECT 3033,2, 5
UNION ALL			SELECT 3035,1,20
UNION ALL			SELECT 3040,1,50
UNION ALL			SELECT 3040,2, 7

INSERT	@Delivery		SELECT 122,'20090901'
UNION ALL			SELECT 123,'20090902'
UNION ALL			SELECT 124,'20090903'
UNION ALL			SELECT 125,'20091001'
UNION ALL			SELECT 126,'20091002'
UNION ALL			SELECT 127,'20091101'

INSERT	@DeliveryProduct	SELECT 122,1,14
UNION ALL			SELECT 123,1,15
UNION ALL			SELECT 123,2,10
UNION ALL			SELECT 124,1,16
UNION ALL			SELECT 125,1, 5
UNION ALL			SELECT 125,2, 7
UNION ALL			SELECT 126,1, 2
UNION ALL			SELECT 127,1,15
/*------------------------------------------------------
SELECT	 Row_Number()OVER(PARTITION BY M.Product ORDER BY O.Date, O.ID)
	,M.[Order]
	,P.Code
	,O.[Month]
	,M.Quantity
FROM	     @OrderProduct	M
	JOIN @Order		O ON O.ID = M.[Order]
	JOIN @Product		P ON P.ID = M.Product
ORDER BY M.Product
SELECT	 Row_Number()OVER(PARTITION BY M.Product ORDER BY D.Date, D.ID)
	,M.Delivery
	,P.Code
	,D.[Month]
	,M.Quantity
FROM	     @DeliveryProduct	M
	JOIN @Delivery		D ON D.ID = M.Delivery
	JOIN @Product		P ON P.ID = M.Product
ORDER BY M.Product
------------------------------------------------------*/
;WITH OrderQuantity AS (
	SELECT	 O.[Month]
		,M.Product
		,Sum(M.Quantity)	AS Quantity
	FROM	          @Order	O
		LEFT JOIN @OrderProduct	M ON M.[Order] = O.ID
	GROUP BY O.[Month]
		,M.Product
), DeliveryQuantity AS (
	SELECT	 D.[Month]
		,M.Product
		,Sum(M.Quantity)	AS Quantity
	FROM	          @Delivery		D
		LEFT JOIN @DeliveryProduct	M ON M.Delivery = D.ID
	GROUP BY D.[Month]
		,M.Product
), MonthQuantity AS (
	SELECT	 M.[Month]		AS [Month]
		,P.ID			AS Product
		,IsNull(O.Quantity,0)	AS [Order]
		,IsNull(C.Quantity,0)	AS Delivery
		,IsNull(D.Quantity,0)	AS NextDelivery
		,N.[Month]		AS NextMonth
	FROM	(	SELECT	[Month]
			FROM	@Order
			GROUP BY [Month])	M
		CROSS JOIN @Product		P
		 LEFT JOIN OrderQuantity	O ON O.[Month] = M.[Month]
						 AND O.Product = P.ID
		 LEFT JOIN DeliveryQuantity	C ON C.[Month] = M.[Month]
						 AND C.Product = P.ID
		 LEFT JOIN DeliveryQuantity	D ON D.[Month] = M.[Month] + 1
						 AND D.Product = P.ID
		OUTER APPLY (
			SELECT	Top(1) N.[Month]
			FROM	@Order N
			WHERE	N.[Month] > M.[Month]
			ORDER BY O.[Month])	N
			
), Provision AS (
	SELECT	Top(1) WITH TIES
		 M.[Month]
		,M.Product
		,M.[Order]
		,M.Delivery + M.NextDelivery		AS Delivery
		,M.Delivery + M.NextDelivery - M.[Order]AS Diff
		,N.NextDelivery
		,M.NextMonth
	FROM	          MonthQuantity		M
		CROSS APPLY (
			SELECT	CASE	WHEN M.Delivery                  >= M.[Order]	THEN M.NextDelivery
					WHEN M.Delivery + M.NextDelivery <= M.[Order]	THEN 0
					ELSE M.Delivery + M.NextDelivery -  M.[Order]
				END	AS NextDelivery	) N
	ORDER BY M.[Month]
UNION ALL
	SELECT	 M.[Month]
		,M.Product
		,M.[Order]
		,C.Delivery + M.NextDelivery		AS Delivery
		,C.Delivery + M.NextDelivery - M.[Order]AS Diff
		,N.NextDelivery
		,M.NextMonth
	FROM	     Provision		P
		JOIN MonthQuantity	M ON M.[Month] = P.NextMonth
					 AND M.Product = P.Product
		CROSS APPLY (
			SELECT	CASE	WHEN P.NextMonth = P.[Month] + 1
					THEN P.NextDelivery
					ELSE M.Delivery
				END	AS Delivery	) C
		CROSS APPLY (
			SELECT	CASE	WHEN C.Delivery                  >= M.[Order]	THEN M.NextDelivery
					WHEN C.Delivery + M.NextDelivery <= M.[Order]	THEN 0
					ELSE C.Delivery + M.NextDelivery -  M.[Order]
				END	AS NextDelivery	) N
)
/*	SELECT	*
	FROM	Provision
	ORDER BY [Month]
		,Product
*/
	SELECT	 Row_Number()OVER(PARTITION BY R.Product ORDER BY O.Date, O.ID)
		,R.[Order]
		,O.[Month]	AS OrderMonth
		,R.Product
		,P.Code		AS ProductCode
		,R.Quantity	AS OrderQuantity
		,D.Diff		AS MonthDiff
		,CASE	WHEN D.Delivery >= R.Quantity + IsNull(C.Quantity,0)
			THEN Convert(Bit,1)
			ELSE Convert(Bit,0)
			END	AS Provision
	FROM	     @OrderProduct	R
		JOIN @Order		O ON O.ID = R.[Order]
		JOIN @Product		P ON P.ID = R.Product
		JOIN Provision		D ON D.[Month] = O.[Month]
					 AND D.Product = R.Product
		OUTER APPLY (
			SELECT	Sum(M.Quantity)	AS Quantity
			FROM	     @Order		C
				JOIN @OrderProduct	M ON M.[Order] = C.ID
			WHERE	    C.Date <= O.Date
				AND C.Date >= Convert(DateTime,Convert(VarChar,O.[Month]) + '01')
				AND(C.Date <  O.Date
				 OR C.ID   <  O.ID)
				AND M.Product = R.Product
				)	C
А вот решение на основе этого описания:
OrderOrderMonthProductProductCodeOrderQuantityMonthDiffProvision
13033200908101Б010133121
23035200909101Б010120-10
33040200910101Б010150-350
13033200908201Б0102551
23040200910201Б0102701
А как, т.е. план вам покажет оптимизатор запросов.

Если вы переведёте описание на русский язык, вы поймёте и суть задачи.
Докручивание до вашей системы и вашей версии сервера это уже ваша проблема.

PS: Если конечно я всё правильно понял.
PS2: И молитесь на ORDER BY for aggregates.
17 окт 09, 18:40    [7800529]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить