如何生成sysdate-30和sysdate + 30之间的所有日期的列表? [英] How to generate list of all dates between sysdate-30 and sysdate+30?

问题描述

我正在尝试创建一个视图，以检查是否缺少劳务交易.该视图将被馈送到Crystal报表.

I am attempting to create a view which checks for missing labor transactions. The view will be fed to a Crystal report.

在这种情况下，视图应采用sysdate + 30到sysdate -30之间的所有日期，然后应让在职员工在每个日期的外部加入所有人工记录.然后，它计算出每个日期每个雇员的劳务交易次数.

In this case, the view should take all dates between sysdate+30 and sysdate -30, and then should left outer join all labor records by active employees for each of those dates. It then gives a count of the number of labor transactions for each employee for each date.

这将传递给Crystal Report，该报告将根据特定的日期范围(在视图的+/- 30范围内)进行过滤.从那里开始，Crystal中每个员工的总工作日总数将被汇总，并且出现零笔交易的员工将出现.

This gets passed to the Crystal Report, which will filter based on a specific date range (within the +/- 30 range by the view). From there, the count of all days will summed up per employee in Crystal, and employees will show up which have zero transactions.

最初，我没有使用每个日期的清单，而是在每个日期使用人工交易，但是有些不包含任何日期的计数.这些人显示零时交易日期.这表明他们在整个期间内都没有任何费用，这是有道理的.

Without spitting out a list of every date, initially, I'm using labor transaction for each date, but some have no counts for any date. These folks show null transaction dates with zero hours. This indicates they have no charges for the entire period, which makes sense.

但是，当Crystal对数据进行过滤并选择一个范围时，我相信它会忽略这些空值，从而使我无法显示没有时间提交的所有人员.

However, when Crystal does a filter on that data and selects a range, I believe it leaves out these null values, thus not allowing me to show the full range of folks who don't have time submitted.

是否有一种方法可以等效于视图中选择(sysdate + 30)和(sysdate-30)之间的每个日期"，以便我可以使用它来比较所有时间?

Is there a way to do the equivalent of "select every date between (sysdate+30) and (sysdate-30)" in a view, so that I can use it to compare all the time against?

SELECT QUERY.LABORRECLABORCODE
       , QUERY.LABORRECEMPLOYEENUM
       , QUERY.PERSONRECDISPLAYNAME
       , QUERY.TRANSSTARTDATE
       , COUNT(TRANSROWSTAMP) AS ROWCOUNT
FROM   (SELECT *
        FROM  (SELECT LABOR.LABORCODE      AS LABORRECLABORCODE
                      , LABOR.LA20         AS LABORRECEMPLOYEENUM
                      , PERSON.DISPLAYNAME AS PERSONRECDISPLAYNAME
               FROM   LABOR
                      LEFT OUTER JOIN PERSON
                        ON ( LABOR.LABORCODE = PERSON.PERSONID )
               WHERE  LABOR.STATUS = 'ACTIVE'
                  AND LABOR.LA20 IS NOT NULL
                  AND PERSON.DISPLAYNAME IS NOT NULL
                  AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%kimball%'
                  AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%electrico%'
                  AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%misc labor cost adj%'
                  AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%brossoit%'
                  AND LOWER(PERSON.DISPLAYNAME) NOT LIKE '%brossiot%')PERSONINFO
              LEFT OUTER JOIN (SELECT STARTDATE   AS TRANSSTARTDATE
                                      , LABORCODE AS TRANSLABORCODE
                                      , ROWSTAMP  AS TRANSROWSTAMP
                               FROM   LABTRANS
                               WHERE  STARTDATE BETWEEN ( SYSDATE - 30 ) AND ( SYSDATE + 30 ))LABTRANSLIMITED
                ON ( PERSONINFO.LABORRECLABORCODE = LABTRANSLIMITED.TRANSLABORCODE ))QUERY
GROUP  BY LABORRECLABORCODE
          , TRANSSTARTDATE
          , LABORRECEMPLOYEENUM
          , PERSONRECDISPLAYNAME
ORDER  BY LABORRECLABORCODE
          , TRANSSTARTDATE
; 

推荐答案

select trunc(sysdate)+31-level from dual connect by level <=61

这是生成任意值列表的好方法.

This is a good method for generating any arbitrary list of values.

这篇关于如何生成sysdate-30和sysdate + 30之间的所有日期的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！