VPF::Сумма нарастающим итогом в запросе

FINANSIST

Дата 19.3.2009, 21:29 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 387
Друзей: 0; Групп: 0
Регистрация: 11.4.2008
Где: Москва

Репутация: 1
Всего: 13

Есть таблица

Код


Дата    Товар    Отгрузка
02.01.2008    F    4
03.01.2008    B    6
04.01.2008    E    7
05.01.2008    C    4
06.01.2008    B    6
07.01.2008    C    4
08.01.2008    A    8
09.01.2008    E    9
10.01.2008    E    7
11.01.2008    F    2
12.01.2008    A    8
13.01.2008    B    6
14.01.2008    A    9
15.01.2008    C    4

Хочу составить товарный рейтинг отгрузки, отсортированный по убыванию, с долей отгрузок по каждой группе товаров
т.е. следующий запрос (назовём его "Запрос2")

Код


SELECT Т1.Товар, Sum(Т1.Отгрузка) AS [Sum-Отгрузка], Sum(Т1!Отгрузка/DSum("[Отгрузка]","Т1")) AS Доля
FROM Т1
GROUP BY Т1.Товар
ORDER BY Sum(Т1.Отгрузка) DESC;

Оформил отчётом
user posted image

Вот только проблема в том, что нужен рейтинг только первых 80% smile

(т.е. отсекаем "С" и "F")
Как отсортировать в отчёте поле "Доля нарастающим итогом до 80%" я не знаю, потому решил вводить в запрос пользовательскую функцию, рассчитывающую нарастающий итог по отгрузке и воткнуть по этой функции (преобразовав нар.итог в долю) условие where

Код


Public Static Function growsum(vl As Double)
Dim starttime
Dim memsum As Double
If Time - starttime > 0 Then ' обнуляю статики если входящий пакет значений закончился по тайм-ауту
memsum = 0
End If
memsum = memsum + vl
starttime = Time
growsum = memsum
End Function

Соответственно , что бы промежутоные итоги не посчитались в процессе групировки 2 и более раз, подключаю пользовательскую функцию в запрос не от исходной таблицы а от запроса с уже сгруппированными данными (Запрос2)

Код


SELECT Запрос2.Товар, Запрос2.[Sum-Отгрузка], Запрос2.Доля, growsum([Запрос2]![Доля]) AS 'Доля нарастающим итогом'
FROM Запрос2;

Рабоатет нормально
user posted image

Пока не накладываю ограничение WHERE (((growsum([Запрос2]![Доля]))<=0.8));
user posted image

Заметил, что функция выдаёт долю в текстовом формате (выравнивание в столбце - по левому краю): Домножил в запросе функцию на единицу ( преобразовалась в число), однако проблема не решилась
Нуждаюсь в совете, как быть
ps глупости вроде "попробуй select top 80 percent " не предлагать

Это сообщение отредактировал(а) FINANSIST - 19.3.2009, 22:54

--------------------

“...Брали корову рыжую одну, отдавать будем корову рыжую одну, чтобы не нарушать отчетности”
Эдуард Успенский, “Каникулы в Простоквашино”
Мой 10-дневный весенний сплав по Угре

bopoha

Дата 19.3.2009, 23:14 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1052
Друзей: 0; Групп: 0
Регистрация: 10.5.2006
Где: Беларусь, Минск

Репутация: 14
Всего: 14

1. Записать результат запроса во временную таблицу. Фильтровать уже по ней.
2. В отчете на событии Print скрывать область данных, если доля > 80 процентов.

Цитата(FINANSIST @ 19.3.2009, 21:29

)

ps глупости вроде "попробуй select top 80 percent " не предлагать

Почему глупости? Не работает? Это же самый простой вариант решения данной задачи.

--------------------

5 строк кода

Akina (Online)

Дата 19.3.2009, 23:32 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 13300
Друзей: 0; Групп: 0
Регистрация: 8.4.2004
Где: Зеленоград, Москв а, Россия

Репутация: 25
Всего: 238

Цитата(bopoha @ 20.3.2009, 00:14

)

Почему глупости? Не работает? Это же самый простой вариант решения данной задачи.

Это не решает задачу, ибо отбирает 80% записей, а не записи со значением поля менее/более 80%

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

FINANSIST

Дата 22.3.2009, 16:08 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 387
Друзей: 0; Групп: 0
Регистрация: 11.4.2008
Где: Москва

Репутация: 1
Всего: 13

Цитата(bopoha @ 19.3.2009, 23:14

)

1. Записать результат запроса во временную таблицу. Фильтровать уже по ней.

Код


INSERT INTO TempTable ( Товар, Отгрузка, Доля, [Доля нарастающим итогом] )
SELECT Запрос2.Товар, Запрос2.[Sum-Отгрузка], Запрос2.Доля, growsum([Запрос2]![Доля])*1 AS Выражение1
FROM Запрос2;

С временной таблицей то всё работает, но это кривое решение.
Так и не понял почему, ограничение where .... <=0,8 при обращении напрямую к запросу 2

Код


SELECT Запрос2.Товар, Запрос2.[Sum-Отгрузка], Запрос2.Доля, growsum([Запрос2]![Доля]) AS 'Доля нарастающим итогом'
FROM Запрос2 WHERE growsum([Запрос2]![Доля]))<=0.8;

Выдаёт хрень
Самое простое решение, это фильтровать записи уже в отчёте, но я не знаю как
Базу прикреплю на всякий случай

Это сообщение отредактировал(а) FINANSIST - 22.3.2009, 16:09

Присоединённый файл ( Кол-во скачиваний: 4 )

QWE________.rar 18,05 Kb

--------------------

bopoha

Дата 22.3.2009, 17:33 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

FINANSIST, есть подозрения, что хрень выдает из-за повторяющегося вызова growsum. Попробуйте убрать вызов в предложении SELECT.

Цитата(FINANSIST @ 22.3.2009, 16:08

)

С временной таблицей то всё работает, но это кривое решение.

Данное высказывание требует аргументации. Я так не думаю, читать тут.

P.S. Спасибо за тему.

--------------------

5 строк кода

Google

Дата 9.2.2010, 09:55 (ссылка)

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MS Access \| Следующая тема »