How to check when date is valid, do A, else do B in SQL-CodePudding

I want to make a query that shows data that looks like this

Note that the data is used for attendance report Input Data:

name	datefrom	dateto	status
Andy	01-11-2022	-	1
Beth	01-11-2022	03-11-2022	2
Casey	01-11-2022	-	1
Andy	02-11-2022	-	1
Casey	02-11-2022	-	1

...

Name	01	02	...	31	Total
Andy	yes	yes	...	X	8
Beth	leave	leave	...	X	5
Casey	yes	yes	...	X	7

(Total attendance/ "yes" in a month)

My problem is that some months has 30 days, some 31, or even 28/ 29

I manually selected the data, like so...

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

and so on...

and I've tried

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

but it shows an invalid date error because apparently SQL validates all the codes even if it would be skipped (assuming that the case statement would return false)

Can someone give me an idea to resolve this?

CodePudding user response：

From your data i understood you want to put X for months which doesn't have those dates.

 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"

CodePudding user response：

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

CodePudding user response：

Updated:

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

R e s u l t : (you can filter it with Where clause to get just one user and/or month of interest)

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

CodePudding user response：

You can generate a calendar and use a partitioned outer join to the table and then pivot:

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

Which, for your sample data:

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;

Outputs:

MONTH	NAME	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
01-NOV-22	Andy	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	null
01-NOV-22	Beth	2	2	2	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	null
01-NOV-22	Casey	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	null

Note: if you want to map from numeric statuses to tables you can include a join to a table with the mappings or use a CASE expression or the DECODE function.

fiddle