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

Откуда:
Сообщений: 1984
Привет Всем!

Пусть есть следующие данные:

- остаток на 03.06.09 300
- обороты:
07.06.09 +1300
11.06.09 -500
и отчет строится за интервал с 03.06 по 13.06

Надо получить таблицу с интервалами:

Дата нач. инт. Дата окон. инт. Кол-во дней Сумма
03.06.2009 07.06.2009 5 300
08.06.2009 11.06.2009 4 1600
12.06.2009 13.06.2009 2 1100

Помогите, пожалуйста, составить запрос. На firebird у меня была составлены процедура, которая давала остатки на каждый день и номер интервала в группе. Потом я просто дела Select sum(), max(), min() from procedure() group by. А в MS SQL что бы такой запрос написать, надо результат процедуры во временную таблицу поместить, а этого не хочется делать. Может можно запросом?
14 июл 09, 10:20    [7411859]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Нарастающий итог
?
14 июл 09, 10:22    [7411865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Паганель,

А даты интервалов как вычислить?
14 июл 09, 10:26    [7411906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
"Дата окон. инт." - это даты оборота union all дата конца периода, за который строится отчет
"Дата нач. инт." - это предшествующая вышеупомянутой "Дате окон. инт." дата оборота + 1 день
либо, если таковая не нашлась, дата начала периода, за который строится отчет

как-то так
14 июл 09, 10:33    [7411960]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Паганель,

Да, процедурой гораздо легче.........
14 июл 09, 10:36    [7411977]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
в чем измеряется легкость?
14 июл 09, 10:36    [7411981]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Паганель
в чем измеряется легкость?


Потому, что если есть процедура, которая возращает остатки на утро за каждый день, то задача превращается в простой запрос с группировкой, примерно такой:
  SELECT MIN(GP."DATE"), MAX(GP."DATE"), COUNT(*), GP.REST, GP.RATE, CAST(SUM(GP.INTEREST) AS NUMERIC(15,2))
  FROM procedure(:DateStart, :DateEnd, 0) AS GP 
  GROUP BY GP.INTERVAL_ID, EXTRACT(YEAR FROM GP."DATE") || ''.'' || EXTRACT(MONTH FROM GP."DATE"), 4, 5
Здесь тебе и начало периода и конец и кол-во дней. И не надо заморачиватся ни на +1 день и так далее. Плюсы очевидны.
14 июл 09, 10:48    [7412066]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
__Avenger__
есть процедура, которая возращает остатки на утро за каждый день
__Avenger__
Здесь тебе и начало периода и конец
исходник процедуры покажите, пожалуйста, ато я уже запутался, что же она возвращает
14 июл 09, 10:55    [7412145]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Паганель
исходник процедуры покажите, пожалуйста, ато я уже запутался, что же она возвращает


Применительно к приведенному выше примеру, она бы вернула следующее:

Дата, Остаток на утро, Номер интервала
03.06.09 300 1
04.06.09 300 1
05.06.09 300 1
06.06.09 300 1
07.06.09 300 1
08.06.09 1600 2
09.06.09 1600 2
10.06.09 1600 2
11.06.09 1600 2
12.06.09 1100 3
13.06.09 1100 3

А процедура примерно следующая:
  OPEN SUM_OPERATIONS;
  FETCH SUM_OPERATIONS INTO :DATE_TMP, :REST_TMP;
  IF (ROW_COUNT = 0) THEN
  BEGIN
    CLOSE SUM_OPERATIONS;
    EXIT;
  END

  DATE_CURRENT = DATE_TMP;
  REST_CURRENT = NULL;
  INTERVAL_ID  = 0;

  IF (DATE_TMP > DATE_END) THEN
  BEGIN
    CLOSE SUM_OPERATIONS;
    EXIT;
  END

  WHILE (DATE_CURRENT <= DATE_END) DO
  BEGIN
    IF (DATE_CURRENT > DATE_TMP AND ROW_COUNT > 0) THEN
    BEGIN
      REST_CURRENT = COALESCE(REST_CURRENT, 0) + REST_TMP;
      IF (REST_CURRENT IS NOT NULL AND REST IS NOT NULL AND REST_CURRENT <> REST) THEN
        INTERVAL_ID = INTERVAL_ID + 1;
      FETCH SUM_OPERATIONS INTO :DATE_TMP, :REST_TMP;
    END

    REST   = REST_CURRENT;
    "DATE" = DATE_CURRENT;
    IF (REST IS NOT NULL) THEN
      SUSPEND;

    DATE_CURRENT = DATE_CURRENT + 1;
  END
  CLOSE SUM_OPERATIONS;
14 июл 09, 11:04    [7412200]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
__Avenger__

Пусть есть следующие данные:

- остаток на 03.06.09 300
- обороты:
07.06.09 +1300
11.06.09 -500
и отчет строится за интервал с 03.06 по 13.06

Надо получить таблицу с интервалами:

Дата нач. инт. Дата окон. инт. Кол-во дней Сумма
03.06.2009 07.06.2009 5 300
08.06.2009 11.06.2009 4 1600
12.06.2009 13.06.2009 2 1100
Я что-то не очень понимаю закономерности построения интервалов. То 5 дней, то 4 и вдруг 2?
14 июл 09, 11:07    [7412224]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Senya_L
Я что-то не очень понимаю закономерности построения интервалов. То 5 дней, то 4 и вдруг 2?


Если с этих данных
 03.06.09 300     1
04.06.09 300 1
05.06.09 300 1
06.06.09 300 1
07.06.09 300 1
08.06.09 1600 2
09.06.09 1600 2
10.06.09 1600 2
11.06.09 1600 2
12.06.09 1100 3
13.06.09 1100 3
сделать запрос
select interval, rest, min(date), max(date), count(*) from table group by 1, 2
, то получим

03.06.2009      07.06.2009         5                300
08.06.2009 11.06.2009 4 1600
12.06.2009 13.06.2009 2 1100
14 июл 09, 11:11    [7412244]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
__Avenger__,

Не видел последнего поста. Тебе бы и в FB начистили кое-что за такую ХП. :)
А заодно бы посоветовали использовать таблицу-календарь.
14 июл 09, 11:14    [7412256]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Вопрос в том как рациональнее получить "Эти данные"....
14 июл 09, 11:14    [7412257]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
__Avenger__
А процедура примерно следующая
Что ж, эта процедура, скорее всего, будет Вам полезна и под MS SQL,
рекомендую переписать ее под эту СУБД

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

Единственное, что хочется пожелать - чтобы в Вашем коде было чуть больше комментариев
Удачи
14 июл 09, 11:14    [7412258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Senya_L
__Avenger__,

Не видел последнего поста. Тебе бы и в FB начистили кое-что за такую ХП. :)
А заодно бы посоветовали использовать таблицу-календарь.


Ну не знаю, процедура работает очень быстро...........
14 июл 09, 11:16    [7412266]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Предоставьте скрипты создания таблицы движения и остатков. Здесь кстати есть Рекомендации по офрмлению, п.6 ;)

__Avenger__
Ну не знаю, процедура работает очень быстро...........
И хочешь сказать, что это показатель оптимальности?
14 июл 09, 11:23    [7412305]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Senya_L
Предоставьте скрипты создания таблицы движения и остатков.


Думаю, что это не реально, потому что данные будут браться из банковской системы DiaSoft. Что бы там вытащить остатки, надо лопатой сначало яму выкопать :-) Так что если у кого есть желание решить эту задачу, то лучше на тестовых данных, приведенных в первом посте.
14 июл 09, 11:30    [7412342]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Может есть готовое решение по выводу всех дат с ДатаНачала по ДатаКонец?
14 июл 09, 11:39    [7412383]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Мне на ум приходит только рекурсивный запрос.......
14 июл 09, 11:40    [7412384]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
__Avenger__
Может есть готовое решение по выводу всех дат с ДатаНачала по ДатаКонец?
Элементарно. Создаёте таблицу с полем DATETIME и заполняете её датами с '19700101', например, до, скажем, '20291231'.
Оттуда берёте даты по мере необходимости.
14 июл 09, 11:43    [7412407]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
__Avenger__
Мне на ум приходит только рекурсивный запрос.......

DECLARE @StartDate DATETIME, @EndDate DATETIME, @Diff INT
SET @StartDate = '2009-01-01'
SET @EndDate = '2009-01-02'

	SET @Diff = CAST(@EndDate - @StartDate AS INT) - 1
	SELECT
		CONVERT(VARCHAR(10), @StartDate + v.[number], 121)
	FROM master.dbo.spt_values v
	WHERE [type] = 'P' AND [number] BETWEEN 0 AND @Diff
+
Сделай таблицу-календарь. Сколько тебе уже можно повторять? Вот, пользуйся. Из запасников :)
IF OBJECT_ID('DayCalendar') IS NOT NULL
	DROP TABLE [DayCalendar]
GO

CREATE TABLE [dbo].[DayCalendar](
	[NDay] INT NOT NULL,
	[date] [datetime] NOT NULL,
	[Year] SMALLINT NOT NULL,
	[Month] TINYINT NOT NULL,
	[Week] TINYINT NOT NULL,
	[DayOfMonth] TINYINT NOT NULL,
	[DayEnd] DATETIME NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [clst] ON [DayCalendar]([date]);
ALTER TABLE [DayCalendar] ADD CONSTRAINT PK_DayCalendar PRIMARY KEY NONCLUSTERED ([NDay])
CREATE NONCLUSTERED INDEX [IX_year_month] ON [DayCalendar]([year], [month]) include([date]);
GO

CREATE TRIGGER IOI_DayCalendar ON [DayCalendar]
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [DayCalendar] ([date], [Year], [Month], [Week], [DayOfMonth], [NDay], [DayEnd]) 
	SELECT
		[date],
		DATEPART(year, [date]), 
		DATEPART(month, [date]), 
		DATEPART(week, [date]), 
		DATEPART(day, [date]),
		ROUND(CAST([date] AS FLOAT), 0),
		[date] + 1 - 0.00000004
	FROM inserted
END
GO


--SELECT CAST(0.00000004 AS DATETIME)

SET NOCOUNT ON;
-- наполняем данными
DECLARE @StartDate DATETIME, @EndDate DATETIME, @CurrDate DATETIME

SET DATEFORMAT ymd
SET @StartDate = '2000-01-01'
SET @EndDate = '2015-12-31'



SET @CurrDate = @StartDate
WHILE @CurrDate <= @EndDate BEGIN
	INSERT INTO [DayCalendar] ([date]) VALUES (@CurrDate) 
--	PRINT CONVERT(VARCHAR(10), @CurrDate, 121)
	SET @CurrDate = @CurrDate + 1
END
GO

--
--SELECT
--	[NDay],
--	CONVERT(VARCHAR(10), [date], 121), '    '
--	[Year],
--	[Month],
--	[Week],
--	[DayOfMonth]
--	,[DayEnd]
--FROM [DayCalendar]
14 июл 09, 11:51    [7412448]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
А как сделать так, что бы в результате вот этого блока
    FETCH NEXT FROM Turns INTO @DATE_TMP, @REST_TMP
    WHILE (@DATE_CURRENT <= @DateEnd) 
    BEGIN
      IF (@DATE_CURRENT > @DATE_TMP) AND (@@FETCH_STATUS = 0) 
      BEGIN
        set @REST_CURRENT = @REST_CURRENT + @REST_TMP;
        IF (@REST_CURRENT <> @REST)
          set @INTERVAL_ID = @INTERVAL_ID + 1;
        FETCH NEXT FROM Turns INTO @DATE_TMP, @REST_TMP
      END

      select @DATE_CURRENT, @REST_CURRENT, @INTERVAL_ID

      set @REST         = @REST_CURRENT;
      set @DATE_CURRENT = DATEADD(day, 1, @DATE_CURRENT);
    END

я получил один набор данных а не 10-ть? Временная таблица?
14 июл 09, 14:25    [7413548]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
Может чего с оптимизировать можно?
alter PROCEDURE DBO.GetAccountRests
  @ResourceID DSIDENTIFIER,
  @DateStart  DSOPERDAY,
  @DateEnd    DSOPERDAY
AS
BEGIN

    /* ---------------------------------------------------------------------
      Вытащим остаток на начало дня */
    declare
      @DateFixed     DSOPERDAY,
      @DateAlign     DSOPERDAY
    -- Получаем дату последнего фиксированного дня
    select
      @DateFixed = isnull(DateFixed, isnull(DateClosed, '19000101'))
    from tBranchBalance (NOLOCK)
    where InstitutionID  = 2000
      and BalanceID      = 2140
      and AccountingType = 1

    select @DateAlign = isnull(max(date), '19000101')
    from tRestDate (NOLOCK)
    where Date <= @DateFixed
      and Date <  @DateStart

    declare @RestBefore DSBIGMONEY

    select
      @RestBefore = sum(AR.Rest)
    from (
      select
        sum(r.Rest) as Rest
      from tRest r (NOLOCK)
      where r.InstitutionID  = 2000
        and r.BalanceID      = 2140
        and r.Confirmed      = 1
        and r.AccountingType = 1
        and r.ResourceType   = 1
        and r.ResourceID     = @ResourceID
        and r.Date = (
          select max(Date)
          from tRest (NOLOCK)
          where InstitutionID  = r.InstitutionID
            and BalanceID      = r.BalanceID
            and AccountingType = r.AccountingType
            and ResourceType   = r.ResourceType
            and ResourceID     = r.ResourceID
            and FundID         = r.FundID
            and Date          >= @DateAlign
            and Date          <= @DateFixed
            and Date           < @DateStart
        )

      union all

      select
        sum(op.CharType * op.Qty)
      from tOperPart op (NOLOCK)
      where op.InstitutionID      = 2000
        and op.BalanceID          = 2140
        and op.Confirmed          = 1
        and op.OperDate           < @DateStart
        and op.OperDate           > @DateFixed
        and op.AccountingType & 1 = 1
        and op.ResourceID         = @ResourceID
        and @DateStart            > @DateFixed
    ) as AR


    /* ---------------------------------------------------------------------
      Создадим курсор с оборотами за интервал */
    DECLARE Turns CURSOR LOCAL STATIC READ_ONLY FOR
      select OperDate, sum(CharType * Qty)
      from tOperPart (NOLOCK)
      where InstitutionID      = 2000
        and BalanceID          = 2140
        and Confirmed          = 1
        and OperDate between @DateStart and @DateEnd
        and AccountingType & 1 = 1
        and ResourceID         = @ResourceID
      group by OperDate
      order by 1
      
    OPEN Turns

    declare
      @DATE_CURRENT smalldatetime,
      @DATE_TMP     smalldatetime,
      @REST_CURRENT numeric(28,10),
      @REST_TMP     numeric(28,10),
      @REST         numeric(28,10),
      @INTERVAL_ID  integer


    set @DATE_CURRENT = @DateStart;
    set @REST_CURRENT = IsNull(@RestBefore, 0);
    set @INTERVAL_ID  = 0;
    
    
    FETCH NEXT FROM Turns INTO @DATE_TMP, @REST_TMP
    WHILE (@DATE_CURRENT <= @DateEnd) 
    BEGIN
      IF (@DATE_CURRENT > @DATE_TMP) AND (@@FETCH_STATUS = 0) 
      BEGIN
        set @REST_CURRENT = @REST_CURRENT + @REST_TMP;
        IF (@REST_CURRENT <> @REST)
          set @INTERVAL_ID = @INTERVAL_ID + 1;
        FETCH NEXT FROM Turns INTO @DATE_TMP, @REST_TMP
      END

      select @DATE_CURRENT, @REST_CURRENT, @INTERVAL_ID

      set @REST         = @REST_CURRENT;
      set @DATE_CURRENT = DATEADD(day, 1, @DATE_CURRENT);
    END

    CLOSE Turns
    DEALLOCATE Turns
END
14 июл 09, 14:26    [7413555]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
__Avenger__
Member

Откуда:
Сообщений: 1984
И еще один вопрос:
select * from
exec GetAccountRests 10000000577, '20090501', '20090601'

процедуру с селектом связать можно, или только через временную таблицу?
14 июл 09, 14:29    [7413573]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос.  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
через курсор )
14 июл 09, 14:33    [7413600]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить