Display a calendar list select procedure using left join and display more than one data in a single date

Question

I am working on a small hostel reservation project. I tried creating a calendar list on a grid that displays all the dates on a month and beside the dates are the transaction details of the hostel (booking details or reservation details).

Here is my sample data:

Transaction table

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

Here is my desired output:

Select procedure

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

My select procedure can only get one data on a single date.

Here is the actual output of my procedure

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
...

I would love to display multiple transactions in a single date.

I created a procedure that will displays all the dates of the month.

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;

and calling this procedure to my main select procedure.

This is the select procedure I am working on.

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;

Actual Results

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
...

My select procedure can only get one data on a single date.

here is the desired output

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
...

seems you would merely need a primitive loop-based stored procedure that generates all the dates in the time span (the leftmost column) and then you simply do a LEFT JOIN of that SP with the table. Something like `Select D, SALESPARTICULAR || ' ' || SALES_DATEFROM || ' - ' || SALES_DATETO from DAYS_IN_MONTH(...) LEFT JOIN Transaction ON SALES_DATEFROM = D` Granted, keeping dates as strings is usually a bad idea per se — Arioch 'The, May 17 '19 at 16:36
But why not making a REGULAR grid or tree, where all three rooms would be displayed for every day, regardless if the room is booked or not? — Arioch 'The, May 17 '19 at 16:42
okay, the irregular American text date presentation is getting at you. Store date columns as DATE not VARCHAR or at least use a regular ordered top-down representations YYYY-MM-DD, and then you can have `...LEFT JOIN Transaction ON D BETWEEN SALES_DATEFROM AND SALES_DATETO` — Arioch 'The, May 17 '19 at 16:45
thank you Sir @Arioch'The :) this solves my problem... The "BETWEEN SALES_DATEFROM AND SALES_DATETO" is the lacking clause. — Don Juan, May 18 '19 at 03:45

score 2 · Accepted Answer · answered May 18 '19 at 06:13

Your use of :COMPLETE_DATE inside the select is rather suspicious, even though it is NULL at the start of the select. I wasn't even aware that Firebird will populate and apply the variable in mid-execution as if it is an alias. In addition, that sub-select itself seems odd and I'm not sure what you are trying to do with it.

From a little testing (adding a record with a SALES_DATEFROM on the first of May), it seems that this is due to evaluation order: inside the WHERE-clause of that sub-select the :COMPLETE_DATE variable still has the value of the previous row (which is probably why you defined it as 1 + DAYS_IN_MONTH.D). As the first of May is the first row, :COMPLETE_DATE is still NULL at that point. However on the 18th of May you have two records (for room 1 and 2), but when the record for room 2 is evaluated, the join condition no longer applies, because the :COMPLETE_DATE value has been updated when the record for room 1 was output.

Long story short: do not use the output variables of your query inside that same query. Given the side effects of evaluation order, their behavior should probably be considered undefined.

Replacing

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

with

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

will do the trick and is - in my opinion - also easier to understand.

score 2 · Answer 2 · answered May 18 '19 at 10:33

No need for that complex code for such a simple task: it only confuses you.

Seems you would merely need one single primitive loop-based stored procedure that generates all the dates in the time span (the leftmost column) and then you simply do a LEFT JOIN of that SP with the bookings table. Something like

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

Display a calendar list select procedure using left join and display more than one data in a single date

2 Answers2