oracle 如何在SQL中检查日期是否有效，执行A，否则执行B

twh00eeo 于 2022-12-18 发布在 Oracle

关注(0)|答案(4)|浏览(102)

我想创建一个显示如下数据的查询
请注意，该数据用于考勤报告输入数据：
| 姓名|日期从|达托|地位|
| - ------|- ------|- ------|- ------|
| 安迪|二○二二年十一月一日|- -|1个|
| 贝丝|二○二二年十一月一日|二○二二年十一月三日|第二章|
| 凯西|二○二二年十一月一日|- -|1个|
| 安迪|二○二二年十一月二日|- -|1个|
| 凯西|二○二二年十一月二日|- -|1个|
...
| 姓名|一|二|...|三十一|共计|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 安迪|是的|是的|...|十|八个|
| 贝丝|离开|离开|...|十|五个|
| 凯西|是的|是的|...|十|七|
（一个月内的总出勤率/“是”）
我的问题是有些月份有30天，有些有31天，甚至28/ 29天
我手动选择了数据，就像这样...

select user_fullname as usrname,
nvl(
    max(CASE WHEN to_char(datefrom,'dd') = '01' and status = 1 THEN 'yes' else 'no' end END)
,'-') as "01",

等等...
我也试过

case when to_date('01-11-2022','dd-mm-yyyy')-to_date('01-12-2022','dd-mm-yyyy') = 30
then 
nvl(
    max(CASE WHEN to_char(datefrom,'dd') = '31' and status = 1 THEN 'yes' else 'no' end END)
,'-') 
else 'X' end as "31",

但是它显示了一个无效日期错误，因为显然SQL验证了所有代码，即使它将被跳过（假设case语句将返回false）
有人能给予我一个解决这个问题的主意吗？

oracle

来源：https://stackoverflow.com/questions/74781414/how-to-check-when-date-is-valid-do-a-else-do-b-in-sql

4条答案

按热度按时间

4nkexdtk1#

从你的数据中，我了解到你想把X放在没有这些日期的月份中。

CASE WHEN last_day(datefrom) < 29 THEN 'X' ELSE --your original condition
 As "29"
 CASE WHEN last_day(datefrom) < 30 THEN 'X' ELSE --your original condition
 As "30"
 CASE WHEN last_day(datefrom) < 31 THEN 'X' ELSE --your original condition
 As "31"

赞(0）回复(0）举报 2022-12-18

xjreopfe2#

with data(usrname, datefrom) as (
    select 'Andy', to_date('01-01-2022', 'dd-mm-yyyy') from dual union all
    select 'Andy', to_date('02-01-2022', 'dd-mm-yyyy') from dual union all
    select 'Andy', to_date('03-01-2022', 'dd-mm-yyyy') from dual union all
    select 'Andy', to_date('30-01-2022', 'dd-mm-yyyy') from dual union all
    select 'Andy', to_date('02-02-2022', 'dd-mm-yyyy') from dual -- union all
),
doms(d) as (
    select level from dual connect by level <= 31
)
select *
from (
    select usrname, to_char(ym,'YYYY-MM') as ym, d.d as dom,
        nvl2(dom,'yes',case when d.d > to_number(to_char(add_months(ym,1)-1,'DD')) then 'X' else 'no' end) as present
    from doms d
    left outer join (
        select usrname, trunc(datefrom,'MM') as ym, to_number(to_char(datefrom,'DD')) as dom
        from data) dat 
    partition by (dat.usrname, dat.ym)
    on d.d = dom
)
pivot (
    max(present) for dom in (
        1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31
    )
)
;

USRN YM      1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31 
---- ------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
Andy 2022-01 yes yes yes no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  yes no 
Andy 2022-02 no  yes no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  no  X   X   X

赞(0）回复(0）举报 2022-12-18

c6ubokkw3#

更新日期：

WITH           -- sample data
    tbl AS
        (
            Select 1 "ID", 'Andy' "USER_FULL_NAME", To_Date('01.12.2022', 'dd.mm.yyyy') "DATE_FROM", 1 "STATUS" From Dual Union All
            Select 1 "ID", 'Andy', To_Date('02.12.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 1 "ID", 'Andy', To_Date('04.12.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 1 "ID", 'Andy', To_Date('05.12.2022', 'dd.mm.yyyy'), 0 From Dual Union All
            Select 1 "ID", 'Andy', To_Date('12.11.2022', 'dd.mm.yyyy'), 0 From Dual Union All
            Select 1 "ID", 'Andy', To_Date('21.11.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('02.12.2022', 'dd.mm.yyyy'), 0 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('09.12.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('10.12.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('12.11.2022', 'dd.mm.yyyy'), 0 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('28.12.2022', 'dd.mm.yyyy'), 0 From Dual Union All
            Select 2 "ID", 'Rick', To_Date('31.12.2022', 'dd.mm.yyyy'), 1 From Dual Union All
            Select 3 "ID", 'Beth', To_Date('01.11.2022', 'dd.mm.yyyy'), 2 From Dual Union All
            Select 3 "ID", 'Beth', To_Date('05.11.2022', 'dd.mm.yyyy'), 1 From Dual 
        ),
--
    days AS (  Select LPAD(LEVEL, 2, '0') "DAY_NO" From Dual Connect By LEVEL <= 31  )
--
SELECT  ID, USER_FULL_NAME, MONTH , 
        Nvl("'01'", 'x') "'01'", Nvl("'02'", 'x') "'02'", Nvl("'03'", 'x') "'03'", Nvl("'04'", 'x') "'04'", Nvl("'05'", 'x') "'05'", Nvl("'06'", 'x') "'06'",
        Nvl("'07'", 'x') "'07'", Nvl("'08'", 'x') "'08'", Nvl("'09'", 'x') "'09'", Nvl("'10'", 'x') "'10'", Nvl("'11'", 'x') "'11'", Nvl("'12'", 'x') "'12'",
        Nvl("'13'", 'x') "'13'", Nvl("'14'", 'x') "'14'", Nvl("'15'", 'x') "'15'", Nvl("'16'", 'x') "'16'", Nvl("'17'", 'x') "'17'", Nvl("'18'", 'x') "'18'",
        Nvl("'19'", 'x') "'19'", Nvl("'20'", 'x') "'20'", Nvl("'21'", 'x') "'21'", Nvl("'22'", 'x') "'22'", Nvl("'23'", 'x') "'23'", Nvl("'24'", 'x') "'24'",
        Nvl("'25'", 'x') "'25'", Nvl("'26'", 'x') "'26'", Nvl("'27'", 'x') "'27'", Nvl("'28'", 'x') "'28'", Nvl("'29'", 'x') "'29'", Nvl("'30'", 'x') "'30'",  Nvl("'31'", 'x') "'31'",
        TOTAL
FROM    ( Select  t.ID,   -- this subquery defines conditions and the data for pivoting
                  t.USER_FULL_NAME,
                  To_Char(t.DATE_FROM, 'MON-yyyy') "MONTH",
                  CASE t.STATUS WHEN 1 THEN 'yes' WHEN 0 THEN 'no' WHEN 2 THEN 'leave' ELSE 'x' END "DAY_STAT",   -- here you can make any other condition that you need to define status
                  Sum(CASE t.STATUS WHEN 1 THEN 1 ELSE 0 END) OVER(PARTITION BY t.ID, To_Char(t.DATE_FROM, 'MON-yyyy')) "TOTAL",
                  d.DAY_NO
          From  tbl t
          Inner Join  days d ON(d.DAY_NO = To_Char(t.DATE_FROM, 'dd'))
        ) 
    PIVOT ( Max(DAY_STAT)
            FOR DAY_NO IN('01','02','03','04','05','06','07', '08', '09', '10',
                          '11','12','13','14','15','16','17', '18', '19', '20',
                          '21','22','23','24','25','26','27', '28', '29', '30', '31') 
          )

结果：（您可以使用Where子句进行过滤，只获取一个用户和/或感兴趣的月份）

/*
        ID USER_FULL_NAME MONTH    '01'  '02'  '03'  '04'  '05'  '06'  '07'  '08'  '09'  '10'  '11'  '12'  '13'  '14'  '15'  '16'  '17'  '18'  '19'  '20'  '21'  '22'  '23'  '24'  '25'  '26'  '27'  '28'  '29'  '30'  '31'       TOTAL
---------- -------------- -------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----------
         1 Andy           DEC-2022 yes   yes   x     yes   no    x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x              3 
         1 Andy           NOV-2022 x     x     x     x     x     x     x     x     x     x     x     no    x     x     x     x     x     x     x     x     yes   x     x     x     x     x     x     x     x     x     x              1 
         2 Rick           DEC-2022 x     no    x     x     x     x     x     x     yes   yes   x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     no    x     x     yes            3 
         2 Rick           NOV-2022 x     x     x     x     x     x     x     x     x     x     x     no    x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x              0 
         3 Beth           NOV-2022 leave x     x     x     yes   x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x     x              1 
*/

赞(0）回复(0）举报 2022-12-18

uhry853o4#

您可以生成日历并使用分区外联接到表，然后进行透视：

SELECT *
FROM   (
  SELECT m.month,
         EXTRACT(DAY FROM c.day) AS day,
         t.name,
         COALESCE(t.status, 1) AS status
  FROM   (SELECT DISTINCT TRUNC(datefrom, 'MM') AS month FROM table_name) m
         CROSS JOIN LATERAL (
           SELECT m.month + LEVEL - 1 AS day
           FROM   DUAL
           CONNECT BY m.month + LEVEL - 1 < ADD_MONTHS(month, 1)
         ) c
         LEFT OUTER JOIN table_name t
         PARTITION BY (t.name)
         ON ( c.day BETWEEN t.datefrom AND COALESCE(t.dateto, t.datefrom) )
)
PIVOT (
  MAX(status) FOR day IN (
     1, 2, 3, 4, 5, 6, 7, 8, 9,10,
    11,12,13,14,15,16,17,18,19,20,
    21,22,23,24,25,26,27,28,29,30,
    31
  )
)

对于您的示例数据：

CREATE TABLE table_name (name, datefrom, dateto, status) AS
SELECT 'Andy',  DATE '2022-11-01', NULL, 1 FROM DUAL UNION ALL
SELECT 'Beth',  DATE '2022-11-01', DATE '2022-11-03', 2 FROM DUAL UNION ALL
SELECT 'Casey', DATE '2022-11-01', NULL, 1 FROM DUAL UNION ALL
SELECT 'Andy',  DATE '2022-11-02', NULL, 1 FROM DUAL UNION ALL
SELECT 'Casey', DATE '2022-11-02', NULL, 1 FROM DUAL;

输出：
| 月份|姓名|1个|第二章|三个|四个|五个|第六章|七|八个|九|十个|十一|十二|十三|十四|十五|十六|十七个|十八|十九|二十个|二十一|二十二|二十三|二十四|二十五|二十六|二十七|二十八|二十九|三十|三十一|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 2022年11月1日|安迪|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个| * 无效 *|
| 2022年11月1日|贝丝|第二章|第二章|第二章|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个| * 无效 *|
| 2022年11月1日|凯西|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个| * 无效 *|

注意：如果要从数字状态Map到表，可以包含到表的联接，或者使用CASE表达式或DECODE函数。*

fiddle

赞(0）回复(0）举报 2022-12-18

我来回答

oracle 如何在SQL中检查日期是否有效，执行A，否则执行B

4条答案

相关问题

热门标签

最新问答