Отображение процедуры выбора списка календаря с использованием левого соединения и отображение нескольких данных за одну дату

Я работаю над небольшим проектом бронирования общежитий. Я попытался создать список календарей в сетке, в которой отображаются все даты месяца, а рядом с датами указаны сведения о транзакциях общежития (данные о бронировании или данные о бронировании).

Вот мой образец данных:

Таблица Транзакции

SALE_PK     SALESPARTICULAR   SALES_DATEFROM   SALES_DATETO
------------------------------------------------------------
   1        Room 1-Reserved      5/17/2019      5/18/2019
   2        Room 2-Reserved      5/18/2019      5/20/2019    
   3        Room 3-Reserved      5/22/2019      5/23/2019

Вот мой желаемый результат:

Выберите процедуру

GET_DATE   SALES_DSCRPTION 
--------------------------
5/1/2019      Null
5/2/2019      Null
5/3/2019      Null
5/4/2019      Null
5/5/2019      Null
5/6/2019      Null
5/7/2019      Null
5/8/2019      Null
5/9/2019      Null
5/10/2019     Null
5/11/2019     Null
5/12/2019     Null
5/13/2019     Null
5/14/2019     Null
5/15/2019     Null
5/16/2019     Null
5/17/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/19/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/20/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/21/2019     Null
5/22/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/23/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/24/2019     Null
5/25/2019     Null
5/26/2019     Null
5/27/2019     Null
5/28/2019     Null
5/29/2019     Null
5/30/2019     Null
5/31/2019     Null

Моя процедура выбора может получить только одни данные за одну дату.

Вот фактический результат моей процедуры

GET_DATE   SALES_DSCRPTION 
---------------------------
5/14/2019     Null
5/15/2019     Null
5/16/2019     Null
5/17/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/19/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/20/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/21/2019     Null
5/22/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/23/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/24/2019     Null
5/25/2019     Null
...

Я хотел бы отображать несколько транзакций за одну дату.

Я создал процедуру, которая будет отображать все даты месяца.

CREATE PROCEDURE DAYS_IN_MONTH(
  Y INTEGER,
  M INTEGER)
RETURNS(
  D DATE)
AS
begin
  d = cast(y || '-' || m || '-01' as date);
  while (extract(month from d) = m) do
  begin
    suspend;
    d = d + 1;
  end
end;

и вызов этой процедуры в мою основную процедуру выбора.

Это процедура выбора, над которой я работаю.

CREATE PROCEDURE SALES_LISTCALENDAR(
  Y INTEGER,
  M INTEGER)
RETURNS(
  MONTHDATE DATE,
  DAYINWORDS VARCHAR(20) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SALES_DSCRPTION VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
AS
DECLARE VARIABLE COMPLETE_DATE DATE;
BEGIN
  FOR
    SELECT
       DAYS_IN_MONTH.D,

       1 + DAYS_IN_MONTH.D,

       CASE extract (WEEKDAY from DAYS_IN_MONTH.D)
        WHEN '0' THEN 'Sunday'
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
        WHEN '6' THEN 'Saturday'
        ELSE '' END,

        A.SALESPARTICULAR

    FROM DAYS_IN_MONTH (:Y, :M)

    LEFT JOIN
      (SELECT A.SALE_PARTICULARS AS SALESPARTICULAR
      FROM SALES A WHERE :COMPLETE_DATE >= A.SALES_DATEFROM AND :COMPLETE_DATE <= A.SALES_DATETO
      GROUP BY A.SALE_PARTICULARS ) A ON DAYS_IN_MONTH.D = :COMPLETE_DATE

    INTO
      :MONTHDATE,
      :COMPLETE_DATE,
      :DAYINWORDS,
      :SALES_DSCRPTION
  DO
    BEGIN
      SUSPEND;
    END
END;

Фактические результаты

GET_DATE   SALES_DSCRPTION 
...
5/14/2019     Null
5/15/2019     Null
5/16/2019     Null
5/17/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/19/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/20/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/21/2019     Null
5/22/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/23/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/24/2019     Null
5/25/2019     Null
...

Моя процедура выбора может получить только одни данные за одну дату.

вот желаемый результат

GET_DATE   SALES_DSCRPTION 
...
5/14/2019     Null
5/15/2019     Null
5/16/2019     Null
5/17/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 1-Reserved 5/17/2019-5/18/2019
5/18/2019     Room 2-Reserved 5/18/2019-5/20/2019
5/19/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/20/2019     Room 2-Reserved 5/18/2019-5/20/2019 
5/21/2019     Null
5/22/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/23/2019     Room 3-Reserved 5/22/2019-5/23/2019
5/24/2019     Null
5/25/2019     Null
...

sql firebird firebird-3.0
person Don Juan    schedule 17.05.2019    source источник
comment
кажется, вам просто нужна примитивная хранимая процедура на основе цикла, которая генерирует все даты в промежутке времени (крайний левый столбец), а затем вы просто выполняете ЛЕВОЕ СОЕДИНЕНИЕ этого SP с таблицей. Что-то вроде Select D, SALESPARTICULAR || ' ' || SALES_DATEFROM || ' - ' || SALES_DATETO from DAYS_IN_MONTH(...) LEFT JOIN Transaction ON SALES_DATEFROM = D Конечно, хранить даты в виде строк — плохая идея сама по себе.   -  person Arioch 'The    schedule 17.05.2019
comment
Но почему бы не сделать ОБЫЧНУЮ сетку или дерево, где отображались бы все три комнаты за каждый день, независимо от того, забронирован номер или нет?   -  person Arioch 'The    schedule 17.05.2019
comment
ладно, нерегулярное американское текстовое представление даты задевает вас. Храните столбцы даты как DATE, а не VARCHAR или, по крайней мере, используйте обычные упорядоченные представления сверху вниз ГГГГ-ММ-ДД, и тогда вы можете иметь ...LEFT JOIN Transaction ON D BETWEEN SALES_DATEFROM AND SALES_DATETO   -  person Arioch 'The    schedule 17.05.2019
comment
спасибо, сэр @Arioch'The :), это решает мою проблему ... BETWEEN SALES_DATEFROM AND SALES_DATETO - это недостающее предложение.   -  person Don Juan    schedule 18.05.2019

Ответы (2)


arrow_upward
2
arrow_downward

Ваше использование :COMPLETE_DATE внутри выбора довольно подозрительно, хотя это NULL в начале выбора. Я даже не знал, что Firebird будет заполнять и применять переменную в середине выполнения, как если бы это был псевдоним. Кроме того, сам этот подвыбор кажется странным, и я не уверен, что вы пытаетесь с ним сделать.

Из небольшого тестирования (добавление записи с SALES_DATEFROM первого мая) кажется, что это связано с порядком оценки: внутри предложения WHERE этого вложенного выбора переменная :COMPLETE_DATE все еще имеет значение предыдущей строки ( вероятно, поэтому вы определили его как 1 + DAYS_IN_MONTH.D). Поскольку первое мая — это первая строка, :COMPLETE_DATE в этот момент все еще равно NULL. Однако 18 мая у вас есть две записи (для комнаты 1 и 2), но когда оценивается запись для комнаты 2, условие соединения больше не применяется, поскольку значение :COMPLETE_DATE было обновлено при выводе записи для комнаты 1. .

Короче говоря: не используйте выходные переменные вашего запроса внутри того же самого запроса. Учитывая побочные эффекты порядка вычисления, их поведение, вероятно, следует считать неопределенным.

Замена

FROM DAYS_IN_MONTH (:Y, :M)
LEFT JOIN
  (SELECT A.SALE_PARTICULARS AS SALESPARTICULAR
  FROM SALES A WHERE :COMPLETE_DATE >= A.SALES_DATEFROM AND :COMPLETE_DATE <= A.SALES_DATETO
  GROUP BY A.SALE_PARTICULARS ) A ON DAYS_IN_MONTH.D = :COMPLETE_DATE

с

FROM DAYS_IN_MONTH (:Y, :M)
LEFT JOIN
  SALES A on DAYS_IN_MONTH.D BETWEEN A.SALES_DATEFROM AND A.SALES_DATETO

добьется цели и, на мой взгляд, также легче понять.

person Mark Rotteveel    schedule 18.05.2019

arrow_upward
2
arrow_downward

Нет необходимости в таком сложном коде для такой простой задачи: он только запутает вас.

Кажется, вам просто нужна одна единственная хранимая процедура на основе примитивного цикла, которая генерирует все даты в промежутке времени (крайний левый столбец), а затем вы просто выполняете LEFT JOIN этого SP с таблицей заказов. Что-то типа

Select D, SALESPARTICULAR || '   ' || SALES_DATEFROM || '  -  ' || SALES_DATETO 
From DAYS_IN_MONTH(...) 
    LEFT JOIN Transactions
        ON D BETWEEN SALES_DATEFROM AND SALES_DATETO
person Arioch 'The    schedule 18.05.2019