Вычисление стоимости продаж по массовой вставке SQL

Используя следующую базу данных в качестве источника данных:

CREATE DATABASE [COS Source] GO USE [COS Source] GO CREATE TABLE [Products]( [INVENTORY_CODE] [nvarchar](50), [PRODCAT_CODE] [nvarchar](50), [LAST_COST] [int] NULL, [STOCK_IND] [nvarchar](255) NULL, CONSTRAINT piv PRIMARY KEY ([INVENTORY_CODE]) ) GO CREATE TABLE [Sales Header]( [DOC_NUMBER] [nvarchar](50), [TRANSTYPE_CODE] [int] null , [REP_CODE] [nvarchar](255) NULL, [CUSTOMER_NUMBER] [nvarchar](50) , [TRANS_DATE] [datetime] NULL, [FIN_PERIOD] [datetime] NULL, CONSTRAINT shdc PRIMARY KEY ([DOC_NUMBER]) ) GO CREATE TABLE [Sales Line]( [DOC_NUMBER] [nvarchar](50) FOREIGN KEY REFERENCES [Sales Header]([DOC_NUMBER]), [INVENTORY_CODE] [nvarchar](50) FOREIGN KEY REFERENCES Products([INVENTORY_CODE]), [QUANTITY] [float] NULL, [UNIT_SELL_PRICE] [int] NULL, [TOTAL_LINE_PRICE] [float] NULL, [LAST_COST] [int] NULL, CONSTRAINT sldcic PRIMARY KEY ([DOC_NUMBER],[INVENTORY_CODE]) ) GO CREATE TABLE [Purchases Headers]( [SUPPLIER_CODE] [nvarchar](255) NULL, [PURCH_DOC_NO] [nvarchar](50), [PURCH_DATE] [datetime] NULL, CONSTRAINT phpdn PRIMARY KEY ([PURCH_DOC_NO]) ) GO CREATE TABLE [Purchases Lines]( [PURCH_DOC_NO] [nvarchar](50) FOREIGN KEY REFERENCES [Purchases Headers]([PURCH_DOC_NO]), [INVENTORY_CODE] [nvarchar](50) FOREIGN KEY REFERENCES [Products]([INVENTORY_CODE]), [QUANTITY] [float] NULL, [UNIT_COST_PRICE] [float] NULL, [TOTAL_LINE_COST] [float] NULL, CONSTRAINT plpdc PRIMARY KEY ([PURCH_DOC_NO],[INVENTORY_CODE]) ) GO 

И используя следующую базу данных в качестве места назначения вставки:

 CREATE DATABASE [COS Target] GO USE [COS Target] GO CREATE TABLE PRODUCT_DIM ( Product_Key int IDENTITY(1,1), Product_Old_Key varchar(255), Unit_Sale_Price decimal, CONSTRAINT pk_PRODUCT_DIM PRIMARY KEY (Product_Key) ) GO CREATE TABLE CUSTOMER_SALES_FACT ( Cust_Sales_Fact_Key int IDENTITY(1,1), Product_Key int, Cost_Of_Sales decimal, CONSTRAINT pk_CUSTOMER_SALES_FACT PRIMARY KEY (Cust_Sales_Fact_Key, Product_Key), CONSTRAINT Customer_Sale_Fact_Product_Key FOREIGN KEY (Product_Key) REFERENCES PRODUCT_DIM(Product_Key) ON DELETE CASCADE, ) GO 

Я хочу рассчитать стоимость продаж (COS) для каждого [COS Source].dbo.[Sales Line] и вставить это значение в [COS Target].dbo.[CUSTOMER_SALES_FACT].Cost_Of_Sales . Основная формула для расчета себестоимости продаж по каждой торговой линии: [COS Source].dbo.[Purchases Lines].[QUANTITY] * [COS Source].dbo.[Purchases Lines].[UNIT_COST_PRICE] или, альтернативно, просто [COS Source].dbo.[Purchases Lines].[TOTAL_LINE_COST] .

Таблица [Product_Dim] заполняется следующим запросом:

 Insert into dbo.PRODUCT_DIM ( Product_Old_Key ,[Unit_Sale_Price] ) Select distinct p.INVENTORY_CODE ,p.LAST_COST from [COS Source].dbo.Products as p Join [COS Source].dbo.[Sales Line] as sl on p.INVENTORY_CODE = sl.INVENTORY_CODE Join [COS Source].dbo.[Sales Header] sh on sl.DOC_NUMBER = sh.DOC_NUMBER GO 

Пока что запрос вставки, который я должен заполнить [COS Target].dbo.[CUSTOMER_SALES_FACT] , но еще не завершен, приведен ниже:

 Insert into dbo.CUSTOMER_SALES_FACT ( [Product_Key] ,[Cost_Of_Sales] ) Select Distinct targetProduct.Product_Key, null /*insert COS here*/from [COS Source].dbo.[Sales Line] as sl Join [COS Source].dbo.[Sales Header] as sh on sl.DOC_NUMBER = sh.DOC_NUMBER Join [COS Source].dbo.Products as p on p.INVENTORY_CODE = sl.INVENTORY_CODE Join dbo.PRODUCT_DIM as targetProduct on [COS Target].Product_Old_Key = p.INVENTORY_CODE Join [COS Source].dbo.[Purchases Lines] pl on p.INVENTORY_CODE = pl.INVENTORY_CODE Join [COS Source].dbo.[Purchases Headers] as ph on pl.PURCH_DOC_NO = ph.PURCH_DOC_NO where ph.PURCH_DATE <= sh.TRANS_DATE GO 

В идеале я хотел бы, чтобы вычисленная COS была помещена в оператор insert, используемый для заполнения [COS Target].dbo.[CUSTOMER_SALES_FACT] , однако проблема возникает там, где для каждого продукта существует более одной торговой линии. Таким образом, чтобы создать, по крайней мере, частично точный COS, я должен выбрать линию покупки, которая произошла до линии продаж. Это означает, что условие

 WHERE [COS Source].dbo.[Sales Header].[TRANS_DATE] >= [COS Source].dbo.[Purchases Header].[PURCH_DATE] 

выполняется.

Мне не удалось вычислить и вставить COS.

Я использую SQL Server 2008. Таблицы также имеют денежные переводы.

Любая помощь будет принята с благодарностью!

Отсутствующая часть информации здесь: для данного INVENTORY_CODE в данной продаже (DOC_NUMBER), когда была самая последняя покупка, содержащая этот INVENTORY_CODE ранее для продажи?

Один из вариантов заключается в создании вспомогательного представления:

 CREATE VIEW dbo.[Most Recent Purchase] AS SELECT sl.DOC_NUMBER, sl.INVENTORY_CODE, MAX(PURCH_DATE) LATEST_PURCH_DATE FROM [COS Source].dbo.[Purchases Lines] pl JOIN [COS Source].dbo.[Purchases Headers] ph on pl.PURCH_DOC_NO=pl.PURCH_DOC_NO JOIN [COS Source].dbo.[Sales Line] sl ON pl.INVENTORY_CODE = sl.INVENTORY_CODE JOIN [COS Source].dbo.[Sales Header] sh ON sh.DOC_NUMBER=sl.DOC_NUMBER WHERE PURCH_DATE < sh.TRANS_DATE GROUP BY sl.DOC_NUMBER, sl.INVENTORY_CODE 

Это представление теперь скажет вам, например:

DOC_NUMBER INVENTORY_CODE LATEST_PURCH_DATE

1 123 2013-04-15

Продажа 1 ([Заголовок сбыта]. DOC_NUMBER = 1) содержит линию продаж с INVENTORY_ID = 123. Самая недавняя покупка INVENTORY_ID = 123 ранее для TRANS_DATE этой продажи была в 2013-04-15 годах.

Теперь попробуйте добавить это представление к своему соединению и оттуда оттуда.

  • Предотвращение дублирования значений в LEFT JOIN
  • внутреннее соединение vs где clause subquery sql server
  • Расшифровка SQL-запроса
  • Проблема с псевдонимом в членском членстве
  • SQL - группа с левым соединением
  • Проблемы с INNER JOIN и LEFT / RIGHT OUTER JOIN
  • SQL это эквивалентно LEFT JoIn?
  • Еще один LEFT OUTER JOIN не работает в SQL Server 2012 Express
  • выберите топ-10 с самым высоким средним счетом
  • Как найти неспаренные строки в TSQL?
  • Левое соединение и подстановочный знак не возвращают результаты?
  • Interesting Posts

    Функция SQL за последние 12 месяцев

    Ошибка XML-отчетов служб Reporting Services

    Форматирование datagrid на основе инструкции SQL

    несколько cte в одиночном заявлении выбора, где ctes могут ссылаться друг на друга

    Ошибка TSQL «Недопустимое имя столбца» на значение параметра sproc

    Соединения с базами данных работают нормально, когда приложение запускается с localhost. Ошибка входа из сервера dev

    Проблема с SQL Access 2007 SQL Query

    Проверка даты T-SQL между родительскими и дочерними таблицами с использованием триггеров

    NHibernate: Настроить () в каждом запросе страницы?

    Извлечение нескольких числовых значений из строки с помощью T-SQL

    Цикл через месяц и год до даты при использовании оператора слияния

    Как обрабатывать ADO-запрос с результатами или запросом без результатов?

    Объявление Databasename в sql-запросе

    Добавить цитаты и запятые для создания в статье для SQL

    Учебник по интеграции Integration Services для SQL Server AdventureWorks> AdventureWorksDW export

    Давайте будем гением компьютера.