Расчет себестоимости проданных товаров

Для расчета себестоимости проданных товаров необходимо создать в датасете BigQuery:

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

Для всех приведенных примеров необходимо в коде изменить project и dataset на ваши реальные название проекта и датасета в BigQuery.

Представление с движением товара

Ниже находится код представления, которое содержит данные о движении товара. При необходимости необходимо добавить в код данные по используемым вами типам документов. Представление необходимо сохранить в базе под именем rpt_stock. Для каждого вида документов нужно корректно добавить тип движения (поле TranCode):

IN - приход
OUT - расход
RET - возврат

-- приход
-- приемки
SELECT CAST(s.moment as datetime) TranDate, sp.assortment ArticleID, cast(sp.quantity as float64) Items,
		(`project.dataset.brf_get_sum`(sp.price, sp.quantity, sp.discount, sp.vat, sp.vatEnabled) 
		+ `project.dataset.brf_get_sum_convet`(sp.overhead))	/ cast(sp.quantity as float64)  Price,
		'IN' TranCode, sp.href StockID
		FROM `project.dataset.brv_supply_positions` sp
		join `project.dataset.brv_supply` s on s.entity_id = sp.entity_id 
		where s.applicable = 'true'

union all

-- оприходывания
SELECT CAST(s.moment as datetime) TranDate, sp.assortment ArticleID, cast(sp.quantity as float64) Items,
`project.dataset.brf_get_sum_convet`(sp.price)  + `project.dataset.brf_get_sum_convet`(sp.overhead)/ cast(sp.quantity as float64)  Price,
'IN' TranCode, sp.href StockID
FROM `project.dataset.brv_enter_positions` sp
join `project.dataset.brv_enter` s on s.entity_id = sp.entity_id 
where s.applicable = 'true'

union all

-- возвраты
SELECT CAST(s.moment as datetime) TranDate, sp.assortment ArticleID, cast(sp.quantity as float64) Items,
`project.dataset.brf_get_sum`(sp.price, sp.quantity, sp.discount, sp.vat, sp.vatEnabled) / cast(sp.quantity as float64)  Price,
'RET' TranCode, sp.href StockID
FROM `project.dataset.brv_retailsalesreturn_positions` sp
join `project.dataset.brv_retailsalesreturn` s on s.entity_id = sp.entity_id 
where s.applicable = 'true'

union all

-- расход
-- отгрузки
SELECT CAST(s.moment as datetime) TranDate, sp.assortment ArticleID, cast(sp.quantity as float64) Items,
`project.dataset.brf_get_sum`(sp.price, sp.quantity, sp.discount, sp.vat, sp.vatEnabled) / cast(sp.quantity as float64)  Price,
'OUT' TranCode, sp.href StockID
FROM `project.dataset.brv_retaildemand_positions` sp
join `project.dataset.brv_retaildemand` s on s.entity_id = sp.entity_id 
where s.applicable = 'true'

union all

-- списания
SELECT CAST(s.moment as datetime) TranDate, sp.assortment ArticleID, cast(sp.quantity as float64) Items,
`project.dataset.brf_get_sum`(sp.price, sp.quantity, null, null, null) / cast(sp.quantity as float64)  Price,
'OUT' TranCode, sp.href StockID
FROM `project.dataset.brv_loss_positions` sp
join `project.dataset.brv_loss` s on s.entity_id = sp.entity_id 
where s.applicable = 'true'

SQL

Функция для расчета себестоимости проданных за период товаров

Ниже находится код функции, которая возвращает таблицу Товар, себестоимость за единицу. Функция принимает на вход аргументы: даты начала и конца периода.

CREATE OR REPLACE TABLE FUNCTION `project.dataset.brf_cost_on_date`(date_s DATETIME, date_f DATETIME) AS (
(

WITH Stock
		AS (select *  from `project.dataset.rpt_stock`
			where  TranDate < DATE_ADD(date_f , INTERVAL 1 DAY)
			),

Stock2
		AS (select *  from `project.dataset.rpt_stock`
			where  TranDate < DATE_ADD(date_f , INTERVAL 1 DAY)
			and not (TranDate >= date_s and TranCode = 'OUT')
			),

ItemEndTotal
	AS (SELECT  ArticleID ,
				SUM(CASE WHEN TranCode ='OUT' THEN - Items
						ELSE Items
					END)AS FinalCount
		FROM    Stock
		GROUP BY ArticleID
	),


ItemEndTotal2
	AS (SELECT  ArticleID ,
				SUM(CASE WHEN TranCode ='OUT' THEN - Items
						ELSE Items
					END)AS FinalCount
		FROM    Stock2
		GROUP BY ArticleID
	),

ReverseRunningTotal
	AS (SELECT  StockID ,
				ArticleID ,
				TranCode ,
				TranDate ,
				Items ,
				Price ,
				SUM(Items) OVER (PARTITION BY ArticleID ORDER BY TranDate
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ReverseTotal
		FROM    Stock
		WHERE   TranCode IN( 'IN','RET' )
	),


	ReverseRunningTotal2
	AS (SELECT  StockID ,
				ArticleID ,
				TranCode ,
				TranDate ,
				Items ,
				Price ,
				SUM(Items) OVER (PARTITION BY ArticleID ORDER BY TranDate
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ReverseTotal
		FROM    Stock2
		WHERE   TranCode IN( 'IN','RET' )
	),

FindDate
	AS (SELECT DISTINCT
				T.ArticleID,
				FinalCount ,
				LAST_VALUE(TranDate)OVER (PARTITION BY P.ArticleID ORDER BY TranDate
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )AS TheDate
		FROM    ItemEndTotal AS T
				JOIN ReverseRunningTotal AS P
				ON T.ArticleID= P.ArticleID
					AND P.ReverseTotal>= T.FinalCount
	),

FindDate2
	AS (SELECT DISTINCT
				T.ArticleID,
				FinalCount ,
				LAST_VALUE(TranDate)OVER (PARTITION BY P.ArticleID ORDER BY TranDate
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )AS TheDate
		FROM    ItemEndTotal2 AS T
				JOIN ReverseRunningTotal2 AS P
				ON T.ArticleID= P.ArticleID
					AND P.ReverseTotal>= T.FinalCount
	),

Cost as (
SELECT ArticleID, FinalCount , SUM(Value1) Value
FROM
(SELECT RRT.ArticleID,
		FinalCount ,
		(CASE WHEN TheDate = RRT.TranDate
				THEN FinalCount-( ReverseTotal - Items )
				ELSE Items
			END * PurchasePrice) AS Value1
FROM   ReverseRunningTotal RRT
		JOIN FindDate
		ON RRT.ArticleID= FindDate.ArticleID
		left join 
		(SELECT     Price AS PurchasePrice, TranDate, ArticleID
						FROM     ReverseRunningTotal AS R
						WHERE    TranCode= 'IN'		                                   
					) as P on RRT.ArticleID = P.ArticleID AND P.TranDate<= RRT.TranDate

WHERE  RRT.TranDate>= TheDate
qualify row_number() over (partition by RRT.StockID order by P.TranDate desc)  = 1) T1
GROUP BY ArticleID , FinalCount),

Cost2 as (
SELECT ArticleID, FinalCount , SUM(Value1) Value
FROM
(SELECT RRT.ArticleID,
		FinalCount ,
		(CASE WHEN TheDate = RRT.TranDate
				THEN FinalCount-( ReverseTotal - Items )
				ELSE Items
			END * PurchasePrice) AS Value1
FROM   ReverseRunningTotal2 RRT
		JOIN FindDate2
		ON RRT.ArticleID= FindDate2.ArticleID
		left join 
		(SELECT     Price AS PurchasePrice, TranDate, ArticleID
						FROM     ReverseRunningTotal2 AS R
						WHERE    TranCode= 'IN'		                                   
					) as P on RRT.ArticleID = P.ArticleID AND P.TranDate<= RRT.TranDate

WHERE  RRT.TranDate>= TheDate
qualify row_number() over (partition by RRT.StockID order by P.TranDate desc)  = 1) T1
GROUP BY ArticleID , FinalCount)


select ArticleID, if(out_FinalCount=0, 0, out_Value/out_FinalCount) cost_price
	from(    		
		select ArticleID,  sum(Value2) - sum(Value) out_Value, sum(out_FinalCount) out_FinalCount
		from (    
		
			select 	ArticleID,  0 Value, Value Value2, 0 out_FinalCount
			from Cost2

			union all     
			
			select 	ArticleID, Value, 0 Value2, 0 out_FinalCount
			from Cost

			union all

			SELECT ArticleID, 0, 0, Items out_FinalCount
			FROM `project.dataset.rpt_stock` where TranCode in ('OUT') and
			TranDate >= date_s and TranDate < DATE_ADD(date_f , INTERVAL 1 DAY)
			
		) t1
	group by ArticleID) t9

)
);

SQL