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

Откуда:
Сообщений: 55
Добрый день всем!
Есть две таблицы (сфера - аренда машин). Пример упрощен.

Пробег a/m
a/m Дата Показание(км)
a/m101.08.2012230100
a/m105.08.2012231100
a/m107.08.2012231150
a/m113.08.2012231500
a/m119.08.2012231900
a/m120.08.2012232000
a/m112.08.2012232500
a/m125.08.2012233000
a/m301.09.201250100
a/m330.09.201252100
a/m401.09.201214000
a/m415.09.201215000
a/m419.09.201215500
a/m401.10.201216500


Аренда a/m
Клиент a/m Период(от) Период(до)
client1a/m101.08.201207.08.2012
client1a/m401.09.201201.10.2012
client2a/m113.08.201225.08.2012
client2a/m301.09.201230.09.2012



Необходимо получить вот такой результат для отчета:
Клиентa/mПробегКол-во дней арендыКол-во показаний пробега
client1a/m1105073
client1a/m42500314
client2a/m11500135
client2a/m32000302


Раньше был код на VBA, теперь необходимо эту логику на SQL перенести.
Какие-нибудь идеи есть?
16 окт 12, 16:18    [13327885]     Ответить | Цитировать Сообщить модератору
 Re: Нужна идея для написания хп  [new]
mottura
Member

Откуда:
Сообщений: 55
версия сервера Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)
16 окт 12, 16:27    [13327965]     Ответить | Цитировать Сообщить модератору
 Re: Нужна идея для написания хп  [new]
Glory
Member

Откуда:
Сообщений: 104751
mottura
Какие-нибудь идеи есть?

INNER JOIN для соединения таблиц
MIN и MAX для получения начального и конечного значения
GROU BY для группировки
16 окт 12, 16:28    [13327975]     Ответить | Цитировать Сообщить модератору
 Re: Нужна идея для написания хп  [new]
mottura
Member

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

А можно чуть подробнее? Особенно о MIN и MAX.

USE tempdb


IF EXISTS (SELECT * FROM sys.objects
           WHERE  object_id = Object_id(N'dbo.[milage]')
              AND TYPE IN ( N'U' ))
  DROP TABLE [milage]

CREATE TABLE [milage]
  (
     [am] [VARCHAR](4) NOT NULL,
     [date] [DATETIME] NOT NULL,
     [km]  [INT] NOT NULL
  )

INSERT INTO milage
SELECT 'a/m1','20120801',230100
UNION SELECT 'a/m1','20120805',231100
UNION SELECT 'a/m1','20120807',231150
UNION SELECT 'a/m1','20120813',231500
UNION SELECT 'a/m1','20120819',231900
UNION SELECT 'a/m1','20120820',232000
UNION SELECT 'a/m1','20120812',232500
UNION SELECT 'a/m1','20120825',233000
UNION SELECT 'a/m3','20120901',50100
UNION SELECT 'a/m3','20120930',52100
UNION SELECT 'a/m4','20120901',14000
UNION SELECT 'a/m4','20120915',15000
UNION SELECT 'a/m4','20120919',15500
UNION SELECT 'a/m4','20121001',16500 

IF EXISTS (SELECT * FROM sys.objects
           WHERE  object_id = Object_id(N'dbo.[rent]')
              AND TYPE IN ( N'U' ))
  DROP TABLE [rent]

CREATE TABLE [rent]
  (
     [client] [VARCHAR](7) NOT NULL,
     [am] [VARCHAR](4) NOT NULL,
     [date_from] [DATETIME] NOT NULL,
     [date_to]  [DATETIME] NOT NULL
  )

INSERT INTO rent
SELECT 'client1','a/m1','20120801','20120807'
UNION SELECT 'client1','a/m4','20120901','20121001'
UNION SELECT 'client2','a/m1','20120813','20120825'
UNION SELECT 'client2','a/m3','20120901','20120930'

SELECT r.client, r.am, m2.km - m.km, m.date, m2.date
FROM rent r INNER JOIN milage m ON r.am=m.am  
			INNER JOIN milage m2 ON r.am=m2.am 
WHERE r.date_from <= m.date and r.date_to >= m.date
GROUP BY r.client, r.am, m2.km - m.km, m.date, m2.date
16 окт 12, 17:23    [13328434]     Ответить | Цитировать Сообщить модератору
 Re: Нужна идея для написания хп  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
+ Код
USE tempdb
GO
CREATE TABLE [dbo].[milage] (
	 [am]	VarChar(4)	NOT NULL
	,[date]	DateTime	NOT NULL
	,CONSTRAINT [PK_milage]	PRIMARY KEY (
		 [am]
		,[date]
	)
	,[km]	Int		NOT NULL
)
CREATE TABLE [dbo].[rent] (
	 [client]	VarChar(7) NOT NULL
	,[am]		VarChar(4) NOT NULL
	,[date_from]	DateTime NOT NULL
	,[date_to] 	DateTime NOT NULL
	,CONSTRAINT [PK_rent]	PRIMARY KEY (
		 [client]
		,[date_from]
	)
	,CONSTRAINT [UQ_milage]	UNIQUE (
		 [am]
		,[date_from]
	)
)
GO -------------------------------------
INSERT dbo.milage VALUES
 ('a/m1','20120801',230100)
,('a/m1','20120805',231100)
,('a/m1','20120807',231150)
,('a/m1','20120813',231500)
,('a/m1','20120819',231900)
,('a/m1','20120820',232000)
,('a/m1','20120822',232500)
,('a/m1','20120825',233000)
,('a/m3','20120901',50100)
,('a/m3','20120930',52100)
,('a/m4','20120901',14000)
,('a/m4','20120915',15000)
,('a/m4','20120919',15500)
,('a/m4','20121001',16500)

INSERT	dbo.rent VALUES
 ('client1','a/m1','20120801','20120807')
,('client1','a/m4','20120901','20121001')
,('client2','a/m1','20120813','20120825')
,('client2','a/m3','20120901','20120930')
GO -------------------------------------
SELECT	 r.client
	,r.am
	,Max(m.km) - Min(m.km)
	,DateDiff(Day,r.date_from,r.date_to)
	,Count(*)
FROM	dbo.rent	r
	JOIN dbo.milage	m ON m.am = r.am
			 AND m.date >= r.date_from
			 AND m.date <= r.date_to
GROUP BY r.client
	,r.am
	,r.date_from
	,r.date_to
GO -------------------------------------
DROP TABLE dbo.milage
DROP TABLE dbo.rent

1. Не забывайте писать схемы у объектов (dbo)
2. Очень не удобно использовать схему день включительно. Время непрерывно. Поэтому диаазое должен быть слева включительно, справа исключительно.
Клиент a/m Период(от) Период(до)
client1a/m101.08.201208.08.2012
Т.е. 08.08.2012 в 00:00:00 уже все, аренда закончена.
А так получается у вас базе висит "07.08.2012 00:00:00", хотя в "07.08.2012 15:00:00" машиной ещё пользуют.
16 окт 12, 18:24    [13328940]     Ответить | Цитировать Сообщить модератору
 Re: Нужна идея для написания хп  [new]
mottura
Member

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

Благодарю вас.
Подозревал, что код не сложный, но не хватает практики в sql.

Это вымышленный пример (для быстрого понимания проблемы).
Но ваши советы пригодятся, а код тем более.
16 окт 12, 18:41    [13329041]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить