Display all Dates in a given month and year in firebird select procedure

I am making a program for Time and Attendance.

I have a table with this fields

  ATTEND_PK INTEGER NOT NULL,
  EMP VARCHAR,
  ATTEND_DATETIME TIMESTAMP,
  USER_PK SMALLINT,
  ATTEND_UPDATETIME TIMESTAMP);

here is the sample data

| ATTEND_PK  |     EMP       |      ATTEND_DATETIME       |
|     1      |    john       |     1/23/2018 7:00:00      |
|     2      |    john       |     1/23/2018 12:00:00     |
|     3      |    john       |     1/23/2018 13:00:00     |
|     4      |    john       |     1/23/2018 16:00:00     |
|     5      |    john       |     1/24/2018 7:30:00      |
|     6      |    john       |     1/24/2018 11:50:00     |
|     7      |    john       |     1/24/2018 13:20:00     |
|     8      |    john       |     1/24/2018 16:40:00     |

and my desired output is this

 |   EMP    |    DATE     |    AM_IN   |   AM_OUT   |   AM_IN  |   AM_OUT   |
 |   john   |  1/1/2018   |     NULL   |    NULL    |   NULL   |    NULL    | 
 |   john   |  1/2/2018   |     NULL   |    NULL    |   NULL   |    NULL    |
 |   ....   |   .....     |     NULL   |    NULL    |   NULL   |    NULL    |
 |   john   |  1/23/2018  |   7:00:00  |  12:00:00  |  1:00:00 |   4:00:00  |
 |   john   |  1/23/2018  |   7:30:00  |  11:50:00  |  1:20:00 |   4:40:00  |
 |   ....   |   .....     |     NULL   |    NULL    |   NULL   |    NULL    |
 |   john   |  1/31/2018  |     NULL   |    NULL    |   NULL   |    NULL    |

the time format of my desired output and my data is different and display the complete dates of the given month and year

Answer

You could solve this problem with a selectable stored procedure.

As a start, here’s a procedure that lists the days in a month:

create procedure days_in_month (y int, m int)
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

Can be called like this

select * from days_in_month (2018, 1)

and will return (in this case) 31 rows with all dates of the days in january 2018.

The table definition in your question text is only pseudo-code, so I cannot use it to provide a working sample. But with the real data table you may either use (left) join syntax (with the above days_in_month procedure on the left side and the physical table on the other side) and make use of Firebirds built-in functions extract and case, or you may put it all in the stored procedure, which may allow to program in a more imperative way.