在Firebird选择过程中显示给定月份和年份中的所有日期 [英] 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);

这是示例数据

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

我想要的输出是这个

 |   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

推荐答案

您可以使用可选的存储过程来解决此问题.

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

可以这样称呼

select * from days_in_month (2018, 1)

，并将返回(在这种情况下)31行，其中包含当天所有日期为2018年1月.

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

问题文本中的表定义仅是伪代码，因此我无法使用它提供有效的示例.但是，对于真实数据表，您可以使用(左)连接语法(上面的days_in_month过程在左侧，物理表在另一侧)，并使用Firebird内置函数extract和case，或者您可以将其全部放入存储过程中，这可能允许以更强制的方式进行编程.

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.

这篇关于在Firebird选择过程中显示给定月份和年份中的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！