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

Откуда:
Сообщений: 2
Есть таблица Товары с полями:
+
КодТовара, Товар, КодВалюты
Первичный ключ - КодТовара
Ни одно из полей не может содержать значений null.
Товар - название товара
КодВалюты - код валюты, по которой этот товар покупается


Таблица Цены с полями:
+
КодТовара, Дата, Цена
Первичный ключ - (КодТовара, Дата)
Ни одно из полей не может содержать значений null.
Дата - дата, с которой действует данная цена товара
Цена - цена единицы товара в валюте товара


Одновременно действующих в один и тот же день цен на один и тот же товар нет
Цена товара актуальна с указанной даты включительно (поле Дата)
до даты следующего изменения (из другой ближайшей по времени записи по этому товару, уже не включая эту дату)
Например, для товара с кодом "1" история изменений цен может быть записана так:

+
КодТовара Дата Цена
1 2016-01-01 100
1 2016-02-15 150
1 2017-01-01 300


Это означает, что с начала 2016-го года для товара с кодом "1" была цена "100" (в валюте товара 1).
Цена "100" действовала до 2016-02-14 включительно. Со следующего дня и до конца 2016-го года - цена "150".
C 1-го января 2017-го года цена - "300".

Таблица Остатки с полями:
+
КодТовара, ДатаС, ДатаПо, Остаток
Первичный ключ - (КодТовара, ДатаС)
Ни одно из полей не может содержать значений null.
В таблице хранится история изменения складских остатков товаров
Остаток действителен с ДатыС по ДатуПо (и там и там - включительно).
Записи по одному и тому же товару по времени не пересекаются.


Например, остатки для товара с кодом "1" могу храниться так:

+
КодТовара ДатаС ДатаПо Остаток
1 2016-01-01 2016-01-25 10
1 2016-01-26 2016-03-05 8
1 2016-03-06 9999-01-01 5


Это означает, что остаток "10" по товару с кодом "1" был с 2016-01-01 по 2016-01-25 (включительно)
с 2016-01-26 по 2016-03-05 был остаток "8"
а с 2016-03-06 по текущий момент времени (в незакрытые периоды ДатаПо = '9999-01-01') остаток стал "5".


Таблица Курсы с курсами валют к рублю:
+
Дата, КодВалюты, Курс
Первичный ключ - (Дата, КодВалюты)
Ни одно из полей не может содержать значений null.
Курс - курс валюты к рублю. Для пересчёта в рубли достаточно умножить значение в валюте на этот курс.
Курсы каждой из валют есть на каждую дату.


Задача:
Написать запрос для расчёта общей стоимости в рублёвом выражении всех товаров имеющихся на остатках на определённую дату.
Эту дату можно объявить как параметр или указывать явную константу во всех местах, где это потребуется.
Рублёвая стоимость товара вычисляется как произведение складского остатка товара, цены товара и курса валюты.
Соответственно, для корректного решения задачи нужно определить актуальные на требуемую дату значения остатка товара, цены товара в валюте и курса валюты к рублю.


Собственно, это был текст задачи, я сделал вот такой код:

SELECT Товара,Курсы.Дата,(Остаток*Цена*Курс) AS ОбщаяЦенаЗаВесьОстаток,(Цена*Курс) AS ЦенаОстаткаЗаШтуку
FROM Товары, Цены, Остатки, Курсы
WHERE ('2016-01-16' BETWEEN ДатаС AND ДатаПо) 
AND Курсы.Дата='2016-01-16' 
AND (Цены.Дата IN
(
SELECT Дата
FROM
(SELECT MAX(Дата) AS Дата,КодТовара
FROM Цены
WHERE Дата<='2016-01-16'
GROUP BY КодТовара) t1
)
)
AND Остаток.КодТовара=Цены.КодТовара 
AND Товары.КодТовара=Цены.КодТовара
AND Товары.КодВалюты=Курсы.КодВалюты


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

Скорее всего эта работа мне и не светит, но ответ на задачку получить хочу.
Мое предположение - добавить group by Товар или что-то похожее что выдаст мне по 1 строке для каждого товара, а не много.
P.S. SQL не могу на своем ПК установить из-за некоторых проблем и поэтому писал весь код "вслепую".
12 окт 19, 12:25    [21992711]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решите задачу от работодателя, бьюсь 4й день  [new]
aleks222
Member

Откуда:
Сообщений: 760
Как-то так.
Перемножить остатки на цену и курс сами должны смочь.

declare @date date = '20191011';

with t as ( select * from Товары )
   , c as ( select * from Цены )
   , o as ( select * from Остатки where @date between ДатаС and ДатаПо )
   , k as ( select * from Курсы )

   select t.Товар
        , c.Цена
		, k.Курс
        , o.Остаток
		, o.КодТовара
     from o 
	    left outer join t on t.КодТовара = o.КодТовара
	    outer apply ( select top(1) * from c where c.КодТовара = o.КодТовара and c.Дата <= @date order by c.Дата desc ) as c
	    outer apply ( select top(1) * from k where k.КодВалюты = t.КодВалюты and k.Дата <= @date order by c.Дата desc ) as k
12 окт 19, 17:19    [21992784]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решите задачу от работодателя, бьюсь 4й день  [new]
glory.owner
Member

Откуда:
Сообщений: 2
aleks222,
спасибо большое, надеюсь, все будет правильно

можно ли на этом форуме как-то отблагодарить человека?
13 окт 19, 00:18    [21992947]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решите задачу от работодателя, бьюсь 4й день  [new]
982183
Member

Откуда: VL
Сообщений: 3217
Я бы посоветовал вашему работодателю перейти с учета по продажным ценам на учет по закупочным ценам, если такое возможно.
13 окт 19, 05:20    [21992967]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить