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

Откуда: Москва
Сообщений: 65
не нашел похожей темы, если найдете - киньте ссылкой
--есть таблица типов движения билетной продукции (справочник)
CREATE TABLE MovementType
(
	ID uniqueidentifier ROWGUIDCOL not null DEFAULT (newsequentialid()),
	[Name] nvarchar(3) not null,		--системное имя
	Caption nvarchar(100) not null,
)
GO
INSERT INTO MovementType (ID, [Name], Caption) VALUES ('01000000-0000-0000-0000-000000000000','reg','Регистрация')	--регистрируем в системе
INSERT INTO MovementType (ID, [Name], Caption) VALUES ('02000000-0000-0000-0000-000000000000','giv','Передача')		--передаем другому служащему
INSERT INTO MovementType (ID, [Name], Caption) VALUES ('03000000-0000-0000-0000-000000000000','del','Списание')		--удаляем из системы
INSERT INTO MovementType (ID, [Name], Caption) VALUES ('04000000-0000-0000-0000-000000000000','ret','Возврат')		--получаем от другого служащего
INSERT INTO MovementType (ID, [Name], Caption) VALUES ('05000000-0000-0000-0000-000000000000','sal','Продажа')		--продаем (так-же удаляем из системы)
GO
--таблица номиналов (справочник)
CREATE TABLE Nominal
(
	ID uniqueidentifier ROWGUIDCOL not null DEFAULT (newsequentialid()),
	Caption nvarchar(40) not null,
	Cost money not null DEFAULT (0), --стоимость номинала
)
GO
INSERT INTO Nominal (ID,Caption,Cost) VALUES ('01000000-0000-0000-0000-000000000000','1руб',1)
INSERT INTO Nominal (ID,Caption,Cost) VALUES ('02000000-0000-0000-0000-000000000000','10руб',10)
INSERT INTO Nominal (ID,Caption,Cost) VALUES ('03000000-0000-0000-0000-000000000000','100руб',100)
GO
--и таблица служащих (справочник)
CREATE TABLE Employer
(
	ID uniqueidentifier ROWGUIDCOL not null DEFAULT (newsequentialid()),
	Surname nvarchar(100) not null,
	[Name] nvarchar(100) not null,
	Patronymic nvarchar(100) null,
	Passport nvarchar(500) null,
	ID_Enterprise uniqueidentifier null,
)
GO
INSERT INTO Employer (ID,[Name],Surname) VALUES ('01000000-0000-0000-0000-000000000000','Стив','Джобс')
INSERT INTO Employer (ID,[Name],Surname) VALUES ('02000000-0000-0000-0000-000000000000','Билл','Гейтс')
INSERT INTO Employer (ID,[Name],Surname) VALUES ('03000000-0000-0000-0000-000000000000','Линус','Торвальдс')
GO
--таблица движения билетной продукции, которая нас интересует
CREATE TABLE Movement
(
	ID uniqueidentifier ROWGUIDCOL not null DEFAULT (newsequentialid()),
	ID_Nominal uniqueidentifier not null,	--Nominal.ID
	ID_Source uniqueidentifier null,		--Employer.ID (от кого пришло)
	ID_Dest uniqueidentifier null,			--Employer.ID (к кому пришло)
	ID_Type uniqueidentifier not null,		--MovementType.ID
	Date datetime not null,					--дата операции
	Series nvarchar(10) not null,			--серия продукции
	Number int not null,					--с какого номера рулон
	[Count] int not null,					--количество билетов в рулоне
)
GO
--стив получил 1000 билетов номиналом 1руб начиная с 1 номера
INSERT INTO Movement (ID_Nominal,ID_Source,ID_Dest,ID_Type,Date,Series,Number,[Count]) VALUES ('01000000-0000-0000-0000-000000000000',null,'01000000-0000-0000-0000-000000000000','01000000-0000-0000-0000-000000000000',GETDATE(),'01-012345',1,1000)
--стив передал биллу 500 билетов номиналом 1р начиная с 1 номера (у стива осталось 500 начиная с 501)
INSERT INTO Movement (ID_Nominal,ID_Source,ID_Dest,ID_Type,Date,Series,Number,[Count]) VALUES ('01000000-0000-0000-0000-000000000000','01000000-0000-0000-0000-000000000000','02000000-0000-0000-0000-000000000000','02000000-0000-0000-0000-000000000000',GETDATE(),'01-012345',1,500)
--билл получил 300 билетов номиналом 1р начиная с 1001
INSERT INTO Movement (ID_Nominal,ID_Source,ID_Dest,ID_Type,Date,Series,Number,[Count]) VALUES ('01000000-0000-0000-0000-000000000000',null,'02000000-0000-0000-0000-000000000000','01000000-0000-0000-0000-000000000000',GETDATE(),'01-012345',1001,300)
--билл передал линусу 400 билетов номиналом 1р начиная 1
INSERT INTO Movement (ID_Nominal,ID_Source,ID_Dest,ID_Type,Date,Series,Number,[Count]) VALUES ('01000000-0000-0000-0000-000000000000','02000000-0000-0000-0000-000000000000','03000000-0000-0000-0000-000000000000','02000000-0000-0000-0000-000000000000',GETDATE(),'01-012345',1,400)
--билл продал 150 билетов номиналом 1р начиная с 1101
INSERT INTO Movement (ID_Nominal,ID_Source,ID_Dest,ID_Type,Date,Series,Number,[Count]) VALUES ('01000000-0000-0000-0000-000000000000','02000000-0000-0000-0000-000000000000',null,'05000000-0000-0000-0000-000000000000',GETDATE(),'01-012345',1101,150)

внимание, вопрос =)
какие интервалы билеты серии '01-012345' номиналом 1руб в наличии у билла?
надо получить результат что-то типа:

Number [Count]
401100
1001100
115150
26 авг 11, 12:52    [11184337]     Ответить | Цитировать Сообщить модератору
 Re: Работа с интервалами  [new]
iljy
Member

Откуда:
Сообщений: 8711
VRafael,

как-то так:
declare @t table(Dir varchar(3), Lo int, Hi int)
insert @t(Dir, Lo, Hi) values
('in', 1,500),
('in', 1001,1300),
('out', 1,400),
('out', 1101, 1250)

select MAX(case Type when 'Lo' then Val end) Lo, MAX(case Type when 'Hi' then Val end) Hi
from(
	select *, ROW_NUMBER() over(partition by Type order by Val) rn
	from(
		select case Dir when 'in' then case x when 0 then 'Lo' else 'Hi' end
					else case x when 1 then 'Lo' else 'Hi' end end Type,
			   case Dir when 'in' then case x when 0 then Lo else Hi end
					else case x when 1 then Hi+1 else Lo-1 end end Val			
		from @t t cross join (select 0 union all select 1) tt(x)
	)t
)t
group by rn
having MAX(case Type when 'Lo' then Val end) < MAX(case Type when 'Hi' then Val end)
26 авг 11, 13:34    [11184799]     Ответить | Цитировать Сообщить модератору
 Re: Работа с интервалами  [new]
VRafael
Member

Откуда: Москва
Сообщений: 65
Спасибо за предложенное решение.
Сейчас попробую прикрутить
31 авг 11, 08:09    [11204073]     Ответить | Цитировать Сообщить модератору
 Re: Работа с интервалами  [new]
VRafael
Member

Откуда: Москва
Сообщений: 65
Спасибо, iljy!
Конечный вариант выглядит приблизительно так:
SET @ID_Employer='02000000-0000-0000-0000-000000000000'
SET @ID_Nominal='01000000-0000-0000-0000-000000000000'
SET @Series='01-012345'

SELECT MAX(CASE [Type] WHEN 'Lo' THEN [Val] END) Lo,
	MAX(CASE [Type] WHEN 'Hi' THEN [Val] END) Hi
FROM(
	SELECT *,
		ROW_NUMBER() OVER(PARTITION BY [Type] ORDER BY Val) rn
	FROM (SELECT
			CASE
				WHEN t.ID_Dest=@ID_Employer THEN
					CASE x
						WHEN 0 THEN 'Lo'
						ELSE 'Hi'
					END
				WHEN t.ID_Source=@ID_Employer THEN
					CASE x
						WHEN 1 THEN 'Lo'
						ELSE 'Hi'
					END
				END [Type],
			CASE
				WHEN t.ID_Dest=@ID_Employer THEN
					CASE x
						WHEN 0 THEN t.Number
						ELSE t.Number+t.[Count]-1
					END
				WHEN t.ID_Source=@ID_Employer THEN
					CASE x
						WHEN 1 THEN t.Number+t.[Count]/*-1+1*/
						ELSE t.Number-1
					END
				END [Val]			
		FROM Movement t
			CROSS JOIN (SELECT 0 UNION ALL SELECT 1) tt(x)
		WHERE (t.ID_Nominal=@ID_Nominal)
			and((t.ID_Source=@ID_Employer)or(t.ID_Dest=@ID_Employer))
			and(t.Series=@Series)
			--and((t.IsDeleted is null)or(t.IsDeleted=0))
	)t
)t
GROUP BY rn
having MAX(CASE [Type] WHEN 'Lo' THEN [Val] END)<MAX(CASE [Type] WHEN 'Hi' THEN [Val] END)
31 авг 11, 08:53    [11204156]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить