Запрос по месяцам и кварталам

← →
normandia (2005-04-25 16:12) [0]

Есть таблица вида

фамилия дата_оплаты сумма
Иванов 01.01.2004 1500
Иванов 01.03.2004 2700
Петров 01.03.2004 2800
Сидоров 01.05.2004 3200
Петров 01.05.2004 1260

Нужен запрос с результатом с суммами по месяцам и итогами по кварталам и с группировкой по фамилиям вида

фамилия январь февраль март 1квартал апрель май июнь 2квартал
Иванов 25000 16000
Петров 15000 и т.д.
Сидоров 25000

Помогите, люди добрые.

← →
Val © (2005-04-25 16:25) [1]

Хорошо бы хранимой процедурой.

← →
Johnmen © (2005-04-25 16:28) [2]

Чтоюы так сделать, придётся иметь не менее 15 вложенных запросов к основному. Тормоза...

← →
raidan © (2005-04-25 16:29) [3]

>Val © (25.04.05 16:25) [1]
А иначе будет тупейший селект с выбором значений 16 раз (если не использовать with as, конечно) :)

← →
stud © (2005-04-25 17:49) [4]

а если хп, одним основным запросом, а с помощью переменных/условий раскидывать по месяцам?

← →
stud © (2005-04-25 17:53) [5]

группировать запрос по клиенту и (месяцу и году).
кол-во выходных параметров постоянно, наверное не так уж и сложно

← →
Val © (2005-04-25 17:54) [6]

>[4] stud © (25.04.05 17:49)
передаем id чела и период - получаем сумму, потом комбинируем при выводе как хотим.

← →
Johnmen © (2005-04-25 17:57) [7]

>stud © (25.04.05 17:49) [4]

Так и надо (Val уже сказал про ХП).

← →
Fay © (2005-04-25 19:25) [8]

Какой движок?!

← →
Johnmen © (2005-04-25 21:59) [9]

>Fay © (25.04.05 19:25) [8]
>Какой движок?!

1.6 литра, 16 клапанов, 110 лошадей

← →
normandia (2005-04-26 08:26) [10]

После перерыва я снова в инете.

Спасибо за внимание к вопросу.
Я насчет ХП сразу подумал, под Interbase без проблем.

Но попросили подобное сделать в MS Access (причем прямо в самой программе Access, без спец. программы на Delphi), а я с ним не очень хорошо знаком.
Как там насчет ХП?
Сейчас и сам тоже поковыряю его на этот предмет.
Времени немного есть.

← →
Anatoly Podgoretsky © (2005-04-26 09:07) [11]

Типичный куб

← →
msguns © (2005-04-26 09:27) [12]

Для начала я все ж таки определился бы с формой журнала - приведенный в сабже вид не существует в официальной отчетности и, видимо, придуман тетей машей.
Для обычных журналов промежуточные итоги по кварталам не нужны.

В любом случае для получения такого журнала (для отчетов в пенсионный фонд, видимо) надо создать временную таблицу, куда выборками запихивать подсчитанное из БД. Таблица нужна хотя бы по двум причинам:
1. Ее, возможно, будут править ручками
2. Ее надо хранить энное количество времени.

← →
normandia (2005-04-26 09:59) [13]

Форма придумана дядей Костей.
Раньше он вел ее в Word для унутреннего пользования (для себя).
Когда стала слишком большой, задумался об автоматизации.

В Access я ему быстренько состряпал выборку по указанному человеку за указанный любой интервал времени.
А потом собрался сделать указанную итоговую форму и уперся.

Не хотелось бы городить базу в Interbase, так как в принципе она небольшая. Лучше бы остаться в рамках Access, так как он уже есть.

← →
Sergey13 © (2005-04-26 10:17) [14]

2 [13] normandia (26.04.05 09:59)
Не понимаю я твоих метаний/сомнений. Если ты знаешь, как сделать на ИБ в ХП, то почему не можешь сделать то-же самое на делфовом клиенте? Какая разница то? Тем паче база "в принципе она небольшая".
Получи группировку по юзерам/месяцам и крути на клиенте как хочешь.

← →
Val © (2005-04-26 10:42) [15]

> [14] Sergey13 © (26.04.05 10:17)
Судячи з усього...он делает на экцесс без делфевых и прочих клиентов. Тогда, лучший способ, наверное, таки временная таблица, хп-то там нет, насколько я понимаю.Хотя черт его знает - там бейсик не прикручен?(это больше к знатокам экцесса.)

← →
Sergey13 © (2005-04-26 10:47) [16]

2[15] Val © (26.04.05 10:42)
Хм. Действительно. Сори.

← →
msguns © (2005-04-26 12:02) [17]

>normandia (26.04.05 09:59) [13]
>Форма придумана дядей Костей.
Раньше он вел ее в Word для унутреннего пользования (для себя).
Когда стала слишком большой, задумался об автоматизации.

Значицца так:
Таблицу размечаем в Екселе. Оттуда намного удобнее печатать и Константину удобнее будет с ворда перейти на ексель, чем в акцесс, где формы неудобные (для непривыкших).
В ексель запихиваем данные из Акцеса. Для этого в екселе пишем макрос, запускающий экзешник, котрый и перегоняет данные из акцеса в ексель. Другой макрос запускает экзешник, который будет "забирать" из екселя и писать в акцес измененные (добавленные) данные.
Два экзешника+макросы - вот, собсно, и все, что надо написать.

Есть, конечно, маленькие траблы по "забору" из екселя, связанные со "стыковкой" записей, но это уже дело техники ;))

Во как я придумал !!!

← →
normandia (2005-04-26 19:57) [18]

> msguns © (26.04.05 12:02) [17]

Круто загнул. Уважаю за размах.
Еще можно подключить winamp, чтобы все это выполнялось под танец с саблями или под гимн Советского Союза.
Дядя Костя обомлеет.

Но лучше сначала конечно помучиться.
Во-первых, хочу пошарить, что в Access есть типа ХП.
Во-вторых, очень смутно подозреваю, что могут помочь перекрестные запросы с подзапросами (хотя вряд ли).
В-третьих, пошел до завтра домой со всем этим разбираться.

← →
aus (2005-04-26 21:06) [19]

Добавь таблицу примерно такой структуры:
[Номер поля][Дата с][Дата по]
Запрос составляй с внешним объединением с этой таблицей, из этого запроса делай перекрестный, где [Номер поля] будет именем столбца, а фамилии - строками.
Мудрёно загнул, но вроде понятно, если вникнуть...

чего же ты сразу не написал что у тебя Access? (тему видел и проигнорировал, сейчас заглянул случайно, просто больше ничего интересного нет...)

ХП тебе не поможет, в аксесе они убогие в одну команду, зато точно поможет (не раз делалось аналогичное) инструкция TRANSFORM PIVOT, транспонирует таблицу в Access, см. в нем хелп.

не знаю как в самом аксесе, а в дельфях (с аксесовской базой) я бы сделал простое транспонирование по месяцам а "1квартал/2квартал/..." вычисляемыми полями.
получилось бы очень просто, даже элементарно, хотя можно и кварталы просчитать. выделить через iif прямо в запросе (в условии не получится... наверное, а может и получится но будут вложенные запросы, тормоза...)

вот в принципе запрос, только поля надо будет "развести"/поставить по местам (т.е. с первого места "1квартал" на четвертое после третьего месяца, и месяца переназвать/дисплейнейме поставить "январь" вместо 1-ци и т.д.).

TRANSFORM Sum([сумма]) SELECT [фамилия], Sum(iif(Month([дата_оплаты]) >=1 and Month([дата_оплаты]) <=3,[сумма],0)) AS [1квартал], Sum(iif(Month([дата_оплаты]) >=4 and Month([дата_оплаты]) <=6,[сумма],0)) AS [2квартал], Sum(iif(Month([дата_оплаты]) >=7 and Month([дата_оплаты]) <=9,[сумма],0)) AS [3квартал], Sum(iif(Month([дата_оплаты]) >=10 and Month([дата_оплаты]) <=12,[сумма],0)) AS [4квартал] FROM [Есть_Tаблица_Bида] GROUP BY [фамилия] PIVOT Month([дата_оплаты]) IN (1,2,3,4,5,6,7,8,9,10,11,12)

← →
normandia (2005-04-27 09:03) [22]

> sniknik © (26.04.05 22:05) [21]

Спасибо.

Дома вечером посмотрел перекрестные запросы и сразу нашел TRANSFORM PIVOT.
Сделал два запроса: один по месяцам, другой по кварталам.
Теперь хочу попробовать смешанный.

А не подскажет кто, что конкретно делает TRANSFORM PIVOT?
А то получил результат, но не совсем понял, как оно работает.
Хочу уяснить на будущее.

> Теперь хочу попробовать смешанный.
вот этот > [21] смешанный.

← →
normandia (2005-04-29 09:59) [24]

> sniknik © (26.04.05 22:05) [21]
> вот в принципе запрос, только поля надо будет "развести"/поставить
> по местам (т.е. с первого места "1квартал" на четвертое
> после третьего месяца, и месяца переназвать/дисплейнейме
> поставить "январь" вместо 1-ци и т.д.).

Прошу прощения за глупый вопрос.
А как развести и переназвать?

Напоминаю, пытаюсь все сделать только в Access без Delphi.
Получилось только вот так, в тупую:

TRANSFORM Sum([по всем исполнителям].[сумма]) AS сумма
SELECT [по всем исполнителям].[фамилия], Sum(IIf(Month([по всем исполнителям].[дата оплаты])=1,[по всем исполнителям].[сумма],0)) AS январь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=2,[по всем исполнителям].[сумма],0)) AS февраль, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=3,[по всем исполнителям].[сумма],0)) AS март, Sum(IIf(Month([по всем исполнителям].[дата оплаты])>=1 And Month([по всем исполнителям].[дата оплаты])<=3,[по всем исполнителям].[сумма],0)) AS 1квартал, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=4,[по всем исполнителям].[сумма],0)) AS апрель, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=5,[по всем исполнителям].[сумма],0)) AS май, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=6,[по всем исполнителям].[сумма],0)) AS июнь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])>=4 And Month([по всем исполнителям].[дата оплаты])<=6,[по всем исполнителям].[сумма],0)) AS 2квартал, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=7,[по всем исполнителям].[сумма],0)) AS июль, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=8,[по всем исполнителям].[сумма],0)) AS август, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=9,[по всем исполнителям].[сумма],0)) AS сентябрь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])>=7 And Month([по всем исполнителям].[дата оплаты])<=9,[по всем исполнителям].[сумма],0)) AS 3квартал, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=10,[по всем исполнителям].[сумма],0)) AS октябрь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=11,[по всем исполнителям].[сумма],0)) AS ноябрь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])=12,[по всем исполнителям].[сумма],0)) AS декабрь, Sum(IIf(Month([по всем исполнителям].[дата оплаты])>=10 And Month([по всем исполнителям].[дата оплаты])<=12,[по всем исполнителям].[сумма],0)) AS 4квартал
FROM [по всем исполнителям]
GROUP BY [по всем исполнителям].[фамилия]
PIVOT Month([по всем исполнителям].[дата оплаты]) In (1,2,3,4,5,6,7,8,9,10,11,12);

Но тут получатся лишние столбцы в конце (1, 2, 3....).
Можно правда оставить только один
(PIVOT Month([по всем исполнителям].[дата оплаты]) In (1)).
Однако это не есть красиво.

все проигнорировали [11]
а зря

> А как развести и переназвать?
в дельфях легко. двойной клик на гриде, добавляеш колонки в нужном порядке, и там же есть displayname колонки. это все. в access х.з. не работаю в access хотя и использую access-овские базы (почемуто часто не видят разницы между этим ;), но должно быть чтото подобное (могу посмотреть, найти, но лень... по той же причине, не работаю, и не понадобится, и нужно не мне а тебе. иши).

Polevi © (29.04.05 10:05) [25]
почему проигнорировали? я к примеру обдумывал этот вариант... но, именно для access это лишнее, результат достигается проше. (имхо конечно)

>sniknik © (29.04.05 10:44) [26]
проблема в том что большинство (имхо, сужу по вопросам) даже не понимают такое куб и многомерный анализ данных
и начинают тонны TRANSACT SQL советовать

← →
AlexO (2005-04-29 16:51) [28]

Я лично делаю так:

SELECT
[фамилия],
SUM(case when Month([дата_оплаты]) = 1 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 2 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 3 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 4 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 5 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 6 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 7 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 8 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 9 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 10 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 11 then [сумма] else 0 end),
SUM(case when Month([дата_оплаты]) = 12 then [сумма] else 0 end),
FROM [Tаблица]
WHERE [дата_оплаты]) between [data1] and [data2]
GROUP BY [фамилия]

Запрос по месяцам и кварталам Найти похожие ветки