Помогите составить запрос, остатков и оборотов за период, с группировками и...

[BOZKURT]

Помогите составить SQL запрос (MS SQL 2005), остатков и оборотов за период, с группировками и суммами следующего вида:



Никак не въеду, как это сделать..
Заранее спасибо.

[soleil@mmc]

пока не будет описания структуры данных (движухи и самих остатков) говорить не о чем
максимум что могу - написать 7 юнионов

[BOZKURT]

Есть таблица движений (некий аналог регистра остатков и оборотов 1с).
В нем имеются следующие поля:

_ID - ключ, автоинкрементное поле (bigint)
_OPERATION_ID - идентификатор операции (движения)
_DOCUMENT_ID - идентификатор документа (которое добавило данное движение)
_PERIOD - период движения (DateTime)
_BRANCH_ID - организация
ACCOUNTABLE_PERSON_TYPE_ID - тип подотчетника (у меня их два: организация или сотрудник)
ACCOUNTABLE_PERSON_ID - идентификатор подотчетника
INSURANCE_CLASS_ID - идентификатор класса страхования
SERIES_ID - идентификатор серии бланков строгой отчетности
BSO_NUMBER - номер бланка СО
COUNT - количество (1 или -1, в зависимости от операции)

Просто остатки я получил без проблем, а вот прицепить туда обороты не получается...(

[BOZKURT]

Вот мой текущий запрос:

SELECT DISTINCT 
                    row_number() over(partition BY REG_BLANKS.ACCOUNTABLE_PERSON_TYPE_ID, REG_BLANKS.ACCOUNTABLE_PERSON_ID, REG_BLANKS.INSURANCE_CLASS_ID, REG_BLANKS.SERIES_ID order by REG_BLANKS.ACCOUNTABLE_PERSON_TYPE_ID, REG_BLANKS.ACCOUNTABLE_PERSON_ID, REG_BLANKS.INSURANCE_CLASS_ID, REG_BLANKS.SERIES_ID, REG_BLANKS.BSO_NUMBER) NPP,

                                          REG_BLANKS.ACCOUNTABLE_PERSON_ID, REG_BLANKS.ACCOUNTABLE_PERSON_TYPE_ID, 
                                          CASE WHEN ACCOUNTABLE_PERSON_TYPE_ID = 1 THEN 'Организации' WHEN ACCOUNTABLE_PERSON_TYPE_ID = 2 THEN 'Страховые агенты' END
                                           AS ACCOUNTABLE_PERSON_TYPE_NAME, CASE WHEN ACCOUNTABLE_PERSON_TYPE_ID = 1 THEN
                                              (SELECT     NAME
                                                FROM          SPR_BRANCHES
                                                WHERE      _ID = REG_BLANKS.ACCOUNTABLE_PERSON_ID) WHEN ACCOUNTABLE_PERSON_TYPE_ID = 2 THEN
                                              (SELECT     NAME
                                                FROM          USERS
                                                WHERE      _ID = REG_BLANKS.ACCOUNTABLE_PERSON_ID) END AS ACCOUNTABLE_PERSON, REG_BLANKS.INSURANCE_CLASS_ID, 
                                          SPR_INS_CLASSES.NAME AS INSURANCE_CLASS_NAME, REG_BLANKS.SERIES_ID, SPR_NUMBER_SERIES.NAME AS SERIES_NAME, 
                                          REG_BLANKS.BSO_NUMBER, SUM(REG_BLANKS.COUNT) AS COUNT
                    FROM         REG_BLANKS INNER JOIN
                                          SPR_INS_CLASSES ON REG_BLANKS.INSURANCE_CLASS_ID = SPR_INS_CLASSES._ID INNER JOIN
                                          SPR_NUMBER_SERIES ON REG_BLANKS.SERIES_ID = SPR_NUMBER_SERIES._ID
                    
                    WHERE 1=1
                    
                    GROUP BY REG_BLANKS.ACCOUNTABLE_PERSON_TYPE_ID, REG_BLANKS.ACCOUNTABLE_PERSON_ID, REG_BLANKS.INSURANCE_CLASS_ID, 
                                          SPR_INS_CLASSES.NAME, REG_BLANKS.SERIES_ID, SPR_NUMBER_SERIES.NAME, REG_BLANKS.BSO_NUMBER
                    HAVING      (SUM(REG_BLANKS.COUNT) > 0)
                    ORDER BY REG_BLANKS.ACCOUNTABLE_PERSON_TYPE_ID, REG_BLANKS.ACCOUNTABLE_PERSON_ID, ACCOUNTABLE_PERSON_TYPE_NAME, 
                                          ACCOUNTABLE_PERSON, REG_BLANKS.INSURANCE_CLASS_ID, INSURANCE_CLASS_NAME, REG_BLANKS.SERIES_ID, SERIES_NAME, 
                                          REG_BLANKS.BSO_NUMBER

[soleil@mmc]

ну запрос этот просто издевательство как минимум в плане чтения
и сам запрос - это только подытоги?
предлагаю пока писать без расшифровки (т.е. пока без слияния с SPR_...)

а в МССКЛ2005 есть group by rollup / grouping_id() ?

[BOZKURT]

Да, поддерживает rollup.
Нетолько подитоги, по желанию пользователя я должен расшифровать вплоть до номера БСО.
Ну за чтение запроса, звиняйте, я замучался уже его приводить к читабельному виду, руки надо оторвать MS-вцам за такое форматирование в интерпрайс менеджере..))

SELECT DISTINCT 
	REG_BLANKS.INSURANCE_CLASS_ID, 
	REG_BLANKS.SERIES_ID, 
	REG_BLANKS.BSO_NUMBER, 
	SUM(REG_BLANKS.COUNT) AS COUNT
FROM
	REG_BLANKS 
                    
WHERE 1=1

GROUP BY 
	REG_BLANKS.INSURANCE_CLASS_ID, 
	REG_BLANKS.SERIES_ID, 
	REG_BLANKS.BSO_NUMBER

HAVING      
	(SUM(REG_BLANKS.COUNT) > 0)

ORDER BY 
	REG_BLANKS.INSURANCE_CLASS_ID, 
	REG_BLANKS.SERIES_ID, 
	REG_BLANKS.BSO_NUMBER

Т.е. в этом запросе, я получаю остатки не на дату, а вообще, что осталось на момент выполнения запроса (условие по периоду потом сделаю).

Условие "WHERE 1=1" служит ключевым выражением, для замены на динамические условия запроса (по выбранным параметрам пользователя).

[soleil@mmc]

уже лучше
давай я тебе завтра точно все нарисую
а сейчас помедитирую над тем что дает вот это
кстати, и зачем тебе дистинкт в группбае?

select 
  grouping_id(rb.insurance_class_id, rb.series_id, rb.bso_number) f_level,
  rb.insurance_class_id, 
  rb.series_id, 
  rb.bso_number, 
  sum(rb.count) as count
from reg_blanks rb                    
where 1=1
group by rollup(rb.insurance_class_id, rb.series_id, rb.bso_number)
having sum(rb.count) > 0
/*order by rb.insurance_class_id, rb.series_id, rb.bso_number*/

апд
а у тебя обороты и остатки в одной таблице?
если так, то может стоит с нуля нормально написать с учетом остатков?!
и что там в СК кроме бабла еще можно приходовать/расходовать?

[BOZKURT]

ок, я тоже уже никакой, спать охота..))
согласен с дистинктом, недосмотрел.
спасибо!

переделал твой запрос, вот так на MSSQL2005 не вызывает эксепшена:

SELECT     
	grouping(INSURANCE_CLASS_ID) AS Expr1, 
	grouping(SERIES_ID) AS Expr2, 
	grouping(BSO_NUMBER) AS Expr3, 
	INSURANCE_CLASS_ID, 
	SERIES_ID, 
	BSO_NUMBER, 
	SUM(COUNT) AS count
FROM
	REG_BLANKS AS rb

WHERE     (1 = 1)

GROUP BY 
	INSURANCE_CLASS_ID, 
	SERIES_ID, 
	BSO_NUMBER 
WITH ROLLUP

HAVING
(SUM(COUNT) > 0)
[/CODE]
... )

апд:

Остатки и обороты в одной таблице.
Кстати, хорошая идея, сделать отдельную оборотную (приход и расход = 2 таблицы, надо подумать) таблицу, просто не хотелось чтобы БД раздувалась, обороты у СК большие..
Кроме бабла СК, меня больше ничего не интересует, да и нет у них больше ничего..))

[soleil@mmc]

Сообщение от BOZKURT

Остатки и обороты в одной таблице.
Кстати, хорошая идея, сделать отдельную оборотную (приход и расход = 2 таблицы, надо подумать) таблицу, просто не хотелось чтобы БД раздувалась, обороты у СК большие..

не-не-не
я такого не предлагал
оставляй как есть
я спрашивал про остатки: видимо, остатки ты получаешь суммированием всех приходов/расходов на начало периода?
т.е. остаток расчетный, а не хранимый где-то еще

Сообщение от BOZKURT

Кроме бабла СК, меня больше ничего не интересует, да и нет у них больше ничего..))

просто там остатки в штуках, а не в тысячах/миллионах/копейках

[BOZKURT]

Сообщение от soleil@mmc

я спрашивал про остатки: видимо, остатки ты получаешь суммированием всех приходов/расходов на начало периода?
т.е. остаток расчетный, а не хранимый где-то еще


просто там остатки в штуках, а не в тысячах/миллионах/копейках

Ага, получаю суммированием прихода и расхода.
В штуках конечно, это же бланки.
А полисы с суммами потом буду в другом регистре учитывать.

Получается что то вроде (1С) Регистр.ПартииБСО и Регистр.ПартииПолисов.