0
Голосовать

Нужна помощь с курсором

Создано: 3 июля 2009 – 11:59 bruto74
Метки записи:

Terrasoft CRM v2.8.11.1390

Строю в отчете таблицу "Список заказчиков изготовленной продукции" следующего содержания: Компания, Количество заказанных продуктов, Общая сумма заказов, % от суммы всех компаний по количеству, % суммы всех компаний по итоговой сумме (руб.). И все это берется за период, который задается в Террасофте при формировании отчета.

С запросом проблем нет (для простоты за определенную дату сделан):

SELECT     cm_Company.Name AS komp, SUM(cm_ProductInDocument.Quantity) AS kol, SUM(cm_ProductInDocument.Price) AS cenaP,
                      SUM(cm_ProductInDocument.Amount) AS summaP
FROM         cm_Task INNER JOIN
                      cm_Sale ON cm_Task.Sale_id = cm_Sale.ID INNER JOIN
                      cm_Document ON cm_Sale.ID = cm_Document.Sale_id INNER JOIN
                      cm_Company ON cm_Document.Company_id = cm_Company.ID INNER JOIN
                      cm_ProductInDocument ON cm_Document.ID = cm_ProductInDocument.Document_id INNER JOIN
                      cm_Product ON cm_ProductInDocument.Product_id = cm_Product.ID INNER JOIN
                      cm_ProductIntegration ON cm_Product.ID = cm_ProductIntegration.Product_id INNER JOIN
                      cm_DocumentInfo ON cm_Document.ID = cm_DocumentInfo.Subj_id INNER JOIN
                      cm_ProductPrice ON cm_ProductIntegration.EnteringProduct_id = cm_ProductPrice.Product_id
WHERE     (cm_Task.Type_id = 31) AND (cm_Task.StartDateTime > CONVERT(DATETIME, '2009-06-19 00:00:00', 102)) AND
                      (cm_Task.StartDateTime < CONVERT(DATETIME, '2009-06-19 23:59:59', 102)) AND (cm_Task.Result_id = 91) AND
                      (cm_ProductInDocument.Parent_id IS NULL) AND (cm_DocumentInfo.InfoType_id = 126) AND (cm_ProductIntegration.EnteringProduct_id <> 129) AND
                      (cm_ProductIntegration.EnteringProduct_id <> 141) AND (cm_ProductPrice.PriceType_id = 12)
GROUP BY cm_Company.Name

Но вот незадача. Нужно убрать всех мелких заказчиков и оставить только тех кто формирует 80% всех заказов (по сумме руб.). Естественно таблица сортируется по этому критерию от максимального процента к минимальному. Я понял что нужно курсором их выбрать, не могу никак решить такую задачу. Помогите пож!!! ))

Комментарии

Черных Руслан
#1 Черных Руслан 3 июля 2009 – 15:30

Насколько я понял нужно сделать еще один запрос с вычислением суммы всех заказов за этот период, а дальше в Фаст репорте, вычислять значение переменной на MasterData-band - Сумма процентов продаж по каждому контрагенту от общей суммы продаж
по каждой строке отчета, и как только значение больше 80% сделать свойство MasterData.visible = false.
Если я все правильно понял.
Это я описал для идеи, когда нужно оставить в отчете список компаний, которые дают 80% объема заказов. Естественно список должен быть отсортирован по сумме заказов от большего к меньшему.

Лабьяк Олег Игоревич
#2 Лабьяк Олег Игоревич 3 июля 2009 – 18:33

Добрый день.

Оба варианта можно объединить примерно в следующий алгоритм.

В источнике данных для отчёта сформировать:
1) курсор, содержащий Ваш запрос вместе с сортировкой по спадающей сумме заказов;
2) временную таблицу с полями, необходимыми для отчёта;
3) переменные для общей суммы заказов и суммы на каждом шаге курсора.

Во время прохождения по курсору проверяем отношение текущей суммы к общей, и пока оно меньше 80%, записываем текущую запись во временную таблицу. Когда оно становится больше, выходим из цикла и закрываем курсор. Дальше формируем выборку из временной таблицы. Результат можно использовать как источник данных для отчёта.

Примерный вид процедуры:

DECLARE @TotalAmount decimal(15, 2),
@CurrentAmount decimal(15, 2)
<Список остальных переменных для курсора>

DECLARE AmountCursor CURSOR FOR
<Ваш запрос>

CREATE TABLE #CustomerList(
<Список колонок таблицы>
)

SET @CurrentAmount = 0
SET @TotalAmount = (<Запрос для общей суммы>)

IF @TotalAmount <> 0
BEGIN

OPEN AmountCursor
WHILE (@CurrentAmount / @TotalAmount <= 0.8)
BEGIN
FETCH NEXT FROM AmountCursor INTO <Список переменных, в которые записываются данные из курсора>
INSERT INTO #CustomerList(<Список колонок таблицы>)
VALUES(<Список переменных, в которые записываются данные из курсора>)
END

CLOSE AmountCursor
DEALLOCATE AmountCursor
END

SELECT <Список колонок временной таблицы>
FROM #CustomerList

Надеюсь, данное краткое описание поможет Вам в решении проблемы.

С уважением,

Олег Лабьяк,
инженер-программист,
группа компаний Terrasoft.

bruto74
#3 bruto74 6 июля 2009 – 6:08

Руслан,

спасибо Вам. Такой вариант конечно возможен, но размер бэнда при этом остается равным полному запросу, а таблица не одна на листе. А если, скажем, взять период порядка недели, то невидимых записей будет листа 1,5 - 2.

Кризис - время для умных!

bruto74
#4 bruto74 6 июля 2009 – 6:55

Олег,

А вот вы написали как раз то, о чем я и думал. Только вот знаний не хватило. Спасибо Вам огромное. Буду работать дальше.

! )))

Кризис - время для умных!

bruto74
#5 bruto74 6 июля 2009 – 8:57

Вот что у меня получилось.

DECLARE @TotalAmount decimal(15, 2),
@CurrentAmount decimal(15, 2),
@Number character (15),
@Comp character (50),
@kol decimal(15, 2),
@sum decimal(15, 2)

DECLARE AmountCursor CURSOR FOR
SELECT     cm_Company.Name AS komp, SUM(cm_ProductInDocument.Quantity) AS kol, SUM(cm_ProductInDocument.Price) AS cenaP,
                      SUM(cm_ProductInDocument.Amount) AS summaP
FROM         cm_Task INNER JOIN
                      cm_Sale ON cm_Task.Sale_id = cm_Sale.ID INNER JOIN
                      cm_Document ON cm_Sale.ID = cm_Document.Sale_id INNER JOIN
                      cm_Company ON cm_Document.Company_id = cm_Company.ID INNER JOIN
                      cm_ProductInDocument ON cm_Document.ID = cm_ProductInDocument.Document_id INNER JOIN
                      cm_Product ON cm_ProductInDocument.Product_id = cm_Product.ID INNER JOIN
                      cm_ProductIntegration ON cm_Product.ID = cm_ProductIntegration.Product_id INNER JOIN
                      cm_DocumentInfo ON cm_Document.ID = cm_DocumentInfo.Subj_id INNER JOIN
                      cm_ProductPrice ON cm_ProductIntegration.EnteringProduct_id = cm_ProductPrice.Product_id
WHERE     (cm_Task.Type_id = 31) AND (cm_Task.StartDateTime > CONVERT(DATETIME, '2009-06-19 00:00:00', 102)) AND
                      (cm_Task.StartDateTime < CONVERT(DATETIME, '2009-06-19 23:59:59', 102)) AND (cm_Task.Result_id = 91) AND
                      (cm_ProductInDocument.Parent_id IS NULL) AND (cm_DocumentInfo.InfoType_id = 126) AND (cm_ProductIntegration.EnteringProduct_id <> 129) AND
                      (cm_ProductIntegration.EnteringProduct_id <> 141) AND (cm_ProductPrice.PriceType_id = 12)
GROUP BY cm_Company.Name
ORDER BY SUM(cm_ProductInDocument.Amount) DESC


CREATE TABLE #CustomerList(tNum char (15), tComp char (50), tKol decimal(15, 2), tSum decimal(15, 2))

SET @CurrentAmount = 0
SET @TotalAmount =
(SELECT     SUM(cm_ProductInDocument.Amount) AS summaP
FROM         cm_Task INNER JOIN
                      cm_Sale ON cm_Task.Sale_id = cm_Sale.ID INNER JOIN
                      cm_Document ON cm_Sale.ID = cm_Document.Sale_id INNER JOIN
                      cm_Company ON cm_Document.Company_id = cm_Company.ID INNER JOIN
                      cm_ProductInDocument ON cm_Document.ID = cm_ProductInDocument.Document_id INNER JOIN
                      cm_Product ON cm_ProductInDocument.Product_id = cm_Product.ID INNER JOIN
                      cm_ProductIntegration ON cm_Product.ID = cm_ProductIntegration.Product_id INNER JOIN
                      cm_DocumentInfo ON cm_Document.ID = cm_DocumentInfo.Subj_id INNER JOIN
                      cm_ProductPrice ON cm_ProductIntegration.EnteringProduct_id = cm_ProductPrice.Product_id
WHERE     (cm_Task.Type_id = 31) AND (cm_Task.StartDateTime > CONVERT(DATETIME, '2009-06-19 00:00:00', 102)) AND
                      (cm_Task.StartDateTime < CONVERT(DATETIME, '2009-06-19 23:59:59', 102)) AND (cm_Task.Result_id = 91) AND
                      (cm_ProductInDocument.Parent_id IS NULL) AND (cm_DocumentInfo.InfoType_id = 126) AND (cm_ProductIntegration.EnteringProduct_id <> 129) AND
                      (cm_ProductIntegration.EnteringProduct_id <> 141) AND (cm_ProductPrice.PriceType_id = 12)
)
IF @TotalAmount <> 0
BEGIN

OPEN AmountCursor
WHILE (@CurrentAmount / @TotalAmount <= 0.8)
BEGIN
FETCH NEXT FROM AmountCursor INTO @Number, @Comp, @kol, @sum
INSERT INTO #CustomerList(tNum, tComp, tKol, tSum)
VALUES(@Number, @Comp, @kol, @sum)
END

CLOSE AmountCursor
DEALLOCATE AmountCursor
END

SELECT *
FROM #CustomerList
DROP TABLE #CustomerList

Только вот выполняется процедура очень долго. Ждал порядка 15 минут, потом отменил - явно что-то не так... А вот что не могу разобраться...

Кризис - время для умных!

Лабьяк Олег Игоревич
#6 Лабьяк Олег Игоревич 6 июля 2009 – 10:41

Добрый день.

Я не заметил в Вашем скрипте изменения переменной @CurrentAmount. Возможно, в этом проблема. Прошу прощения, в своём предыдущем ответе я не акцентировал на этом внимания. Необходимо в цикл (например, после вставки значений во временную таблицу: Insert into #CustomerList...) добавить строчку:

SET @CurrentAmount = @CurrentAmount + ISNULL(@sum, 0)

С уважением,

Олег Лабьяк,
инженер-программист,
группа компаний Terrasoft.

bruto74
#7 bruto74 6 июля 2009 – 11:57

Да, спасибо)) Сейчас пока обедал понял, что процедура зацикливается, потому что условие всегда верно, и переменная нигде не переприсваивается!

Кризис - время для умных!

Кризис - время для умных!

Лабьяк Олег Игоревич
#8 Лабьяк Олег Игоревич 6 июля 2009 – 12:00

Рад был помочь :)

С уважением,

Олег Лабьяк,
инженер-программист,
группа компаний Terrasoft.

© 2002-2012 Terrasoft
Дизайн: ISD Group