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

Откуда: Москва
Сообщений: 598
Версия:
Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64) 
	Jul 11 2014 16:11:50 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Есть таблица таблица дат, ее формирует пользователь, задавая период, который ему нужен, в данном случае он задал период с 1.05.2017 по 30.06.2017 :
set dateformat dmy
DECLARE @date TABLE([ID] INT,data date)

INSERT INTO @date([ID],data)

SELECT 0,'01.05.2017' UNION ALL
SELECT 1,'02.05.2017' UNION ALL
SELECT 2,'03.05.2017' UNION ALL
SELECT 3,'04.05.2017' UNION ALL
SELECT 4,'05.05.2017' UNION ALL
SELECT 5,'06.05.2017' UNION ALL
SELECT 6,'07.05.2017' UNION ALL
SELECT 7,'08.05.2017' UNION ALL
SELECT 8,'09.05.2017' UNION ALL
SELECT 9,'10.05.2017' UNION ALL
SELECT 60,'30.06.2017'


Есть таблица товара которую также формирует пользователь, задавая какой товар ему нужно про анализировать, где поле Artikul- это артикул товара оно не уникально, Identifier - Штрихкод товара поле уникально, Ves - вес товара, DateF - Дата появления товара на складе.

DECLARE @izd TABLE([ID] INT,Artikul nvarchar(50), ves float,identifier nvarchar(50),DateF date)

INSERT INTO @izd([ID],Artikul,ves,identifier,DateF)

SELECT 0,'01Б',1.56,'01001','05.05.2017' UNION ALL
SELECT 1,'01Б',1.82,'01002','03.05.2017' UNION ALL
SELECT 2,'01C',1.15,'01003','06.06.2017' 


Есть таблица движения товара, показывает как двигался товар по складам внутри компании или вообще покинул компанию. Поле Data - Это дата движения, Ost - Это битовое поле 0 и 1, где 0 -означает покинул компанию, 1- на остатке в компании на каком-то складе

DECLARE @d TABLE([ID] INT,identifier nvarchar(50),dataO date,ost int)

INSERT INTO @d([ID],identifier,dataO,ost)

SELECT 0,'01001','05.05.2017',1 UNION ALL
SELECT 0,'01001','07.05.2017',1 UNION ALL
SELECT 0,'01001','09.05.2017',0 UNION ALL
SELECT 1,'01002','03.05.2017',1 UNION ALL
SELECT 1,'01002','05.05.2017',0 UNION ALL
SELECT 1,'01002','09.05.2017',1 UNION ALL
SELECT 2,'01003','06.06.2017',1


Мне нужно сделать некий анализ на основе этих данных, получить таблицу вида:
DataArtikulvesidentifierDataOost
01.05.201701Б1.56010010
02.05.201701Б1.56010010
03.05.201701Б1.56010010
04.05.201701Б1.56010010
05.05.201701Б1.560100105.05.20171
06.05.201701Б1.56010011
07.05.201701Б1.560100107.05.20171
08.05.201701Б1.56010011
09.05.201701Б1.560100109.05.20170
10.05.201701Б1.56010010
30.06.201701Б1.56010010
01.05.201701Б1.82010020
02.05.201701Б1.82010020
03.05.201701Б1.820100203.05.20171
04.05.201701Б1.82010021
05.05.201701Б1.820100205.05.20170
06.05.201701Б1.82010020
07.05.201701Б1.82010020
08.05.201701Б1.82010020
09.05.201701Б1.820100209.05.20171
10.05.201701Б1.82010021
30.06.201701Б1.82010021
01.05.201701C1.15010030
02.05.201701C1.15010030
03.05.201701C1.15010030
04.05.201701C1.15010030
06.05.201701C1.15010030
07.05.201701C1.15010030
08.05.201701C1.15010030
09.05.201701C1.15010030
10.05.201701C1.15010030
06.06.201701C1.150100306.06.20171
30.06.201701C1.15010031


Сложность в том, когда даты не соединяются, как мне проставлять 0 и 1 на след строки, в зависимости от того какая была прошлая строка?
30 июн 17, 12:29    [20602484]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Я это написал, как мне смотреть на предыдущую строку и проставлять нужный бит
SELECT a.data,a.Artikul,a.ves,a.identifier,d.dataO,d.ost,case when a.data<a.DateF then 0 else 1 end as ost
FROM
(
select d.data,i.Artikul,i.ves,i.identifier,i.DateF from @izd as i cross join @date as d
) as a
left join @d as d on a.identifier=d.identifier and a.data=d.dataO
Order by identifier,a.data
30 июн 17, 12:49    [20602549]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
aleks2
Guest
minya13_85
Я это написал, как мне смотреть на предыдущую строку и проставлять нужный бит
SELECT a.data,a.Artikul,a.ves,a.identifier,d.dataO,d.ost,case when a.data<a.DateF then 0 else 1 end as ost
FROM
(
select d.data,i.Artikul,i.ves,i.identifier,i.DateF from @izd as i cross join @date as d
) as a
left join @d as d on a.identifier=d.identifier and a.data=d.dataO
Order by identifier,a.data

Вариантов, как минимум, два:
1. Разучить row_number() over(...).
2. Разучить outer apply и top(1).

ЗЫ. Я за apply.
30 июн 17, 12:56    [20602588]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
aleks2
minya13_85
Я это написал, как мне смотреть на предыдущую строку и проставлять нужный бит
SELECT a.data,a.Artikul,a.ves,a.identifier,d.dataO,d.ost,case when a.data<a.DateF then 0 else 1 end as ost
FROM
(
select d.data,i.Artikul,i.ves,i.identifier,i.DateF from @izd as i cross join @date as d
) as a
left join @d as d on a.identifier=d.identifier and a.data=d.dataO
Order by identifier,a.data

Вариантов, как минимум, два:
1. Разучить row_number() over(...).
2. Разучить outer apply и top(1).

ЗЫ. Я за apply.


Я понял соединить результирующий набор самой к себе, предварительно от ранжировав. Но я получу значение предыдущей строки, а если предыдущая была также пустая?
30 июн 17, 14:01    [20602917]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
aleks2
Guest
minya13_85
aleks2
пропущено...

Вариантов, как минимум, два:
1. Разучить row_number() over(...).
2. Разучить outer apply и top(1).

ЗЫ. Я за apply.


Я понял соединить результирующий набор самой к себе, предварительно от ранжировав. Но я получу значение предыдущей строки, а если предыдущая была также пустая?


Тут нужно волеизъявление: а чего надо, если предыдущей строки НЕТ?
30 июн 17, 14:34    [20603101]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
aleks2
Guest
minya13_85
aleks2
пропущено...

Вариантов, как минимум, два:
1. Разучить row_number() over(...).
2. Разучить outer apply и top(1).

ЗЫ. Я за apply.


Я понял соединить результирующий набор самой к себе, предварительно от ранжировав. Но я получу значение предыдущей строки, а если предыдущая была также пустая?

Прочитал еще раз и понял, что тредстартер так и ничего не понял.

Учи, страдалец, outer apply и top(1).
30 июн 17, 14:38    [20603121]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
aleks2, Я так и не понял как применить тут outer apply, может подскажешь?
я сделал немного по-другому, соединил таблицу движения товара саму к себе, предварительно отранжировав как мне надо, и каждой строчке записал период. Но что-то мне кажется это не оптимальное решение этой задачи:
SELECT a.id,a.data,a.Artikul,a.ves,a.identifier,d.Fdate as dateO,isnull(d.ost,0) as ost 
FROM
(
select d.ID,d.data,i.Artikul,i.ves,i.identifier,i.DateF from @izd as i cross join @date as d
) as a
LEFT JOIN 
(
SELECT a.identifier,a.ost,a.dataO as Fdate,cast(coalesce(b.dataO,dateadd(d,1,getdate())) as date) as Ldate
FROM
(select identifier,dataO,ost,ROW_NUMBER() OVER(PARTITION BY identifier ORDER BY dataO) as r from @d) as a
LEFT JOIN (select identifier,dataO,ost,ROW_NUMBER() OVER(PARTITION BY identifier ORDER BY dataO) as r from @d) as b on a.identifier=b.identifier and a.r=b.r-1
) as d on d.identifier=a.identifier and a.data between d.Fdate  and dateadd(d,-1,d.Ldate)
Order by a.identifier,a.data
30 июн 17, 16:39    [20603715]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
dies irae
Member

Откуда:
Сообщений: 78
select dt.id,dt.data,i.Artikul,i.ves,i.identifier,d.dataO as dateO, isnull(d.ost,0) as ost 
from @date as dt
	cross join @izd as i
	outer apply (
		select top(1) d.*
		from @d as d
		where d.identifier = i.identifier
			and d.dataO <= dt.data
		order by d.dataO desc
	) as d
order by i.identifier, dt.data


PS я бы в добавок к совету изучить конструкции типа outer apply добавил бы совет поучиться код форматировать. для начала использовать отступы и забыть о "вмещу-ка я весь подзапрос в одну строку"
30 июн 17, 17:16    [20603839]     Ответить | Цитировать Сообщить модератору
 Re: просчитать пролеживание на остатке  [new]
лолл
Member

Откуда:
Сообщений: 450
dies irae
добавок к совету изучить конструкции типа outer apply добавил бы совет поучиться код форматировать. для начала использовать отступы и забыть о "вмещу-ка я весь подзапрос в одну строку"


мне кажется, что уже поздно - 10 лет на форуме :)
30 июн 17, 18:33    [20604133]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить