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

Откуда: Moscow Square
Сообщений: 635
Имеется
CREATE TABLE a(
DetailId int,
PriceId int,
Date datetime,
Quantity int)

INSERT INTO a VALUES(1,1,'20111201',5)
INSERT INTO a VALUES(1,1,'20111202',4)
INSERT INTO a VALUES(1,1,'20111203',3)
INSERT INTO a VALUES(2,1,'20111201',6)
INSERT INTO a VALUES(2,1,'20111202',3)
INSERT INTO a VALUES(3,1,'20111205',4)
INSERT INTO a VALUES(3,1,'20111206',2)

CREATE TABLE b(
DetailId int,
Priceid int,
QuantitySale int)

INSERT INTO b VALUES(1,1,10)
INSERT INTO b VALUES(2,1,8)
INSERT INTO b VALUES(3,1,7)


Задача провести списание в таблице a по полю Quantity значениями QuantitySale из таблицы b по связке DetailId, PriceId, причем списание сначала производится с более "старых" записей. В идеале, конечно, чтобы, если списать всё не получилось, то это ещё куда-нибудь выводить, но сейчас не суть, главное, что если на списание больше чем есть, то списать хотя бы имеющееся.

То есть в примере должно получиться:
1,1,'20111201',0
1,1,'20111202',0
1,1,'20111203',2
2,1,'20111201',0
2,1,'20111202',1
3,1,'20111205',0
3,1,'20111206',0


Я представляю как всё это сделать в цикле, но в цикле очень не хочется, а вот как без цикла обойтись не могу сообразить :(

Версия сиквела SQL2005 SP2
26 дек 11, 19:59    [11829409]     Ответить | Цитировать Сообщить модератору
 Re: Головоломный запрос  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Oblom, допилите под себя самостоятельно. Идея: накопительный итог и вычисление разницы.

SELECT  *
       ,CASE WHEN t.a2Quantity-b.QuantitySale<0 THEN 0
             ELSE t.a2Quantity-b.QuantitySale
        END result
FROM    (
         SELECT a1.DetailId
               ,a1.PriceId
               ,a1.Date
               ,a1.Quantity
               ,SUM(a2.Quantity) a2Quantity
         FROM   a a1
         LEFT JOIN a a2 ON a1.DetailId=a2.DetailId
                           AND a1.PriceId=a2.PriceId
                           AND a1.Date>=a2.Date
         GROUP BY a1.DetailId
               ,a1.PriceId
               ,a1.Date
               ,a1.Quantity
        ) t
JOIN    b ON b.DetailId=t.DetailId
             AND b.PriceId=t.PriceId
26 дек 11, 20:25    [11829542]     Ответить | Цитировать Сообщить модератору
 Re: Головоломный запрос  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
kDnZP,

спасибо!
26 дек 11, 20:33    [11829579]     Ответить | Цитировать Сообщить модератору
 Re: Головоломный запрос  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
kDnZP, ваш запрос, к сожалению, списывает не по порядку, сначала с первого всё, потом со второго и так далее, а с каждого.
Вот тестовые данные приближенные к боевым:
CREATE TABLE a(
Date datetime,
DetailId int,
PriceId int,
Quantity int)

INSERT INTO a VALUES('20111030',	55654253,	75,	15)
INSERT INTO a VALUES('20111218',	57100653,	75,	3)
INSERT INTO a VALUES('20111219',	57100653,	75,	3)
INSERT INTO a VALUES('20111114',	61121788,	75,	1)
INSERT INTO a VALUES('20111030',	62900271,	75,	1)
INSERT INTO a VALUES('20111126',	63068506,	75,	5)
INSERT INTO a VALUES('20111211',	63068506,	75,	5)
INSERT INTO a VALUES('20111212',	63068506,	75,	5)
INSERT INTO a VALUES('20111218',	63068506,	75,	6)
INSERT INTO a VALUES('20111119',	64490397,	75,	6)
INSERT INTO a VALUES('20111120',	69722702,	75,	7)
INSERT INTO a VALUES('20111126',	76028058,	75,	1)
INSERT INTO a VALUES('20111127',	76028058,	75,	1)
INSERT INTO a VALUES('20090828',	82315014,	75,	8)

CREATE TABLE b(
DetailId int,
PriceId int,
QuantitySale int)

INSERT INTO b VALUES(55654253,	75,	1)
INSERT INTO b VALUES(57100653,	75,	1)
INSERT INTO b VALUES(61121788,	75,	1)
INSERT INTO b VALUES(62900271,	75,	1)
INSERT INTO b VALUES(63068506,	75,	1)
INSERT INTO b VALUES(64490397,	75,	1)
INSERT INTO b VALUES(69722702,	75,	2)
INSERT INTO b VALUES(76028058,	75,	2)
INSERT INTO b VALUES(82315014,	75,	4)

SELECT  *
       ,CASE WHEN t.a2Quantity-b.QuantitySale<0 THEN 0
             ELSE t.a2Quantity-b.QuantitySale
        END result
FROM    (
         SELECT a1.DetailId
               ,a1.PriceId
               ,a1.Date
               ,a1.Quantity
               ,SUM(a2.Quantity) a2Quantity
         FROM   a a1
         LEFT JOIN a a2 ON a1.DetailId=a2.DetailId
                           AND a1.PriceId=a2.PriceId
                           AND a1.Date>=a2.Date
         GROUP BY a1.DetailId
               ,a1.PriceId
               ,a1.Date
               ,a1.Quantity
        ) t
JOIN    b ON b.DetailId=t.DetailId
             AND b.PriceId=t.PriceId
             
DROP TABLE a
DROP TABLE b

На них видно, что с детали 63068506 списывается 1 четыре раза. А для меня главная трудность как раз списывать поочередно без цикла...
27 дек 11, 09:58    [11830979]     Ответить | Цитировать Сообщить модератору
 Re: Головоломный запрос  [new]
zsefvhu
Guest
Oblom,

он дал вам идею. вам нужно было запрос немножко переделать, примерно как то так:
;with cte as (
select
	a1.DetailId, a1.PriceId, a1.Date, a1.Quantity, sum(coalesce(a2.Quantity,0)) sq
from 
	a a1
	left join a a2 on a1.DetailId = a2.DetailId and a1.PriceId = a2.PriceId and a1.Date > a2.Date
group by a1.DetailId, a1.PriceId, a1.Date, a1.Quantity
)

select
	*, case
		when sq >= QuantitySale then Quantity
		else 
			case
				when Quantity + sq <= QuantitySale then 0
				else Quantity + sq - QuantitySale
			end
	end ostat
	, case 
		when max(Quantity + sq - QuantitySale) over (partition by detailid, priceid) >= 0 then 0
		else abs(max(Quantity + sq - QuantitySale) over (partition by detailid, priceid))
	end nespis
from (
select
	a.*, b.QuantitySale
from
	cte a
	left join b on a.DetailId = b.DetailId and a.PriceId = b.Priceid
) a
27 дек 11, 11:40    [11831775]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить