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

Откуда:
Сообщений: 141
Доброго времени суток всем!

Есть табличка, в которой хранятся купленные лицензии на некую услугу. По купленным лицензиям покупатель предоставляет клиентам доступ к услуге. Данные о том, что услуга была предоставлена, приходят от покупателя лицензий (ID покупателя, ID покупателя услуги, дата предоставления). Необходимо составить отчет по использованным лицензиям следующего вида:

Поставщик Остаток на начало периода Куплено лицензий Остаток на конец периода


Как делаю:

1. Заносим во временную таблицу сведения о лицензиях, купленных каждым поставщиком:

CREATE TABLE #tLicenses
(
   ProviderID uniqueidentifier,
   LicensesCount int,
   PurchaseDate date
)

INSERT INTO #tLicenses (ProviderID, LicensesCount, PurchaseDate)
SELECT userid, SUM([count]), date  FROM PurchasedLicenses INNER JOIN Purchase ON PurchasedLicenses.purchaseid=Purchase.id
WHERE (Purchase.userid IN (SELECT userid FROM aspnet_UsersInRoles where roleid IN (SELECT roleid FROM aspnet_roles where rolename='provider')))
GROUP BY userid, date

Получаем примерно следующую таблицу:

ProviderIDLicensesCountPurchaseDate
0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E7287 2011-09-01
0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E718 2011-10-01
0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E70 2011-11-01
B4CDB816-D486-4F8B-956C-4613EDCB66C53002011-09-01


2. Создаю временную таблицу, которая будет являться основой отчета

CREATE TABLE #tLicensesUsage
(
	UsageID int identity(1,1),
	RowNum int null,
	ProviderID uniqueidentifier,
	StartDate datetime,
	EndDate datetime,
	RestOnStartDate int null,
	LicensesCount int,
	RestOnEndDate int null,
	CustomersCount int null
)

3. Группируем данные по периодам и находим общее количество предоставленных услуг
INSERT INTO #tLicensesUsage (ProviderID, StartDate, EndDate, LicensesCount, CustomersCount)
SELECT tblLicensesUsage_provider_id, MIN([tblLicensesUsage_customer_enter_date]), MAX([tblLicensesUsage_customer_enter_date]), LicensesCount, count(*)
FROM tblLicensesUsage INNER JOIN #tLicenses ON tblLicensesUsage.tblLicensesUsage_provider_id=#tLicenses.ProviderID
GROUP BY [tblLicensesUsage_provider_id], MONTH(tblLicensesUsage_customer_enter_date), YEAR(tblLicensesUsage_customer_enter_date), LicensesCount

Получаем примерно такую таблицу:

UsageIDRowNumProviderIDStartDateEndDateRestOnStartDateLicensesCountRestOnEndDateCustomersCount
1NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-09-012011-09-03NULL287NULL15
2NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-10-012011-10-02NULL18NULL5
3NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-11-012011-11-02NULL0NULL6
4NULLB4CDB816-D486-4F8B-956C-4613EDCB66C52011-09-012011-09-01NULL300NULL10


4. Нумеруем строки, разбивая таблицу по покупателям
WITH CTE(RowID, NumRow) AS
(
   SELECT UsageID, ROW_NUMBER() OVER(PARTITION BY providerID ORDER BY StartDate, EndDate) AS NumRow FROM #tLicensesUsage
)

UPDATE #tLicensesUsage SET RowNum=(SELECT NumRow FROM CTE WHERE UsageID=RowID)

Получаем примерно такую таблицу:
UsageIDRowNumProviderIDStartDateEndDateRestOnStartDateLicensesCountRestOnEndDateCustomersCount
110B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-09-012011-09-03NULL287NULL15
220B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-10-012011-10-02NULL18NULL5
330B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-11-012011-11-02NULL0NULL6
41B4CDB816-D486-4F8B-956C-4613EDCB66C52011-09-012011-09-01NULL300NULL10


5. Теперь необходимо сделать следующие вычисления:
а) Остаток на начало периода=Остаток на конец предыдущего периода
б) Количество купленных лицензий=Остаток на начало периода+Количество еще купленных лицензий в этом периоде
в) Остаток на конец периода=Количество купленных лицензий-Количество использованных

Для приведенных данных это будет выглядеть так:
UsageIDRowNumProviderIDStartDateEndDateRestOnStartDateLicensesCountRestOnEndDateCustomersCount
1NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-09-012011-09-03028727215
2NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-10-012011-10-022722902855
3NULL0B57ADAB-7E89-4A3F-9C2F-272A8F56E5E72011-11-012011-11-022852852796
4NULLB4CDB816-D486-4F8B-956C-4613EDCB66C52011-09-012011-09-01030029010


Вопрос заключается в том, как написать Update, который брал бы остаток на конец периода в предыдущей строке, сохранял его как остаток на начало в текущей, количество купленных в текущей сохранял как остаток на начало в текущей строке+количество купленных в текущей строке и остаток на конец в текущей строке сохранял как количество купленных в текущей-количество использованных в текущей. Пробовал сделать нечто вроде такого:
UPDATE #tLicensesUsage SET
RestOnEndDate=LicensesCount-CustomersCount,
RestOnStartDate=ISNULL((SELECT TOP 1 t1.RestOnEndDate FROM
(SELECT * FROM #tLicensesUsage) t1,
(SELECT * FROM #tLicensesUsage) t2
WHERE t1.RowNum-1=t2.RowNum AND t1.ProviderID=t2.ProviderID), 0),
LicensesCount=RestOnEndDate+LicensesCount

но в столбце остаток на начало получаются нули, соответственно дальше вычисляется неправильно. суть ошибки почему так происходит я понимаю, но придумать как написать правильно, как-то не могу. Буду благодарен за подсказку

P.S. Понимаю, что придуманный вариант решения может быть и не самый лучший, но, к сожалению, основная работа у меня связана не с SQL, а с C#, поэтому если кто натолкнет на идею лучшей реализации, буду тоже очень благодарен.

Заранее спасибо за ответы
19 окт 11, 22:51    [11469593]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно составить запрос  [new]
aleks2
Guest
1. Охренеть.

2.
>>как написать Update, который брал бы остаток на конец периода

рекурсивное CTE.
20 окт 11, 06:43    [11470028]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно составить запрос  [new]
Yaroslav82
Member

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

1. Я понимаю, что охренеть, но ничего лучше в голову не пришло :(
2. Насчет рекурсивного CTE думал, но тоже что-то пока не особо получается
20 окт 11, 09:13    [11470242]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно составить запрос  [new]
aleks2
Guest
;with
  nums as (
select *, row_number() over(partition by ProviderID ORDER BY StartDate) as n FROM #tLicensesUsage T
)
 ,res as(
select UsageID, RowNum, ProviderID, StartDate, EndDate
 , 0 as RestOnStartDate
 , LicensesCount
 , LicensesCount-CustomersCount as RestOnEndDate  
 , CustomersCount 
 , n 
FROM nums where n=1
union all
select N.UsageID, N.RowNum, N.ProviderID, N.StartDate, N.EndDate
 , R.RestOnEndDate as RestOnStartDate
 , N.LicensesCount
 , R.RestOnEndDate+N.LicensesCount-N.CustomersCount as RestOnEndDate  
 , N.CustomersCount 
 , N.n 
FROM res R inner join nums N on R.ProviderID=N.ProviderID AND R.n+1=N.n
)
select * FROM res
UPDATE сами напишите.
20 окт 11, 10:29    [11470615]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно составить запрос  [new]
Yaroslav82
Member

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

Спасибо большое, все получилось, правда пришлось подправить в связи с немного изменившимся условиями (лицензии могут быть куплены раньше, чем их начали расходовать, тогда они идут в остаток на начало самого первого периода), но это уже было несложно :) спасибо еще раз за идею
20 окт 11, 19:16    [11475180]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить