Select all days in a week, when calendarweek is given-CodePudding

certainly something very simple, but for an application I would like to know how, if I know the calendar week, I can display the first to the last day of the week per row.

Currently, I am only shown the day in which content is present.

I would like to have 7 days displayed (as date, not necessarily with name) whether they are empty or not.

SELECT 
  MIN( TO_CHAR(LP_BELEGUNG.GEN_DATUM,'DD.MM.YYYY'))  AS GRD_ROW_ID    

, COUNT( DISTINCT
  CASE
    WHEN LP_BELEGUNG.ART = 1 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS ANZAHL_ART_1

, COUNT( DISTINCT
  CASE
    WHEN LP_BELEGUNG.ART = 2 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS ANZAHL_ART_2
  
, COUNT( DISTINCT
  CASE
    WHEN LP_BELEGUNG.ART = 3 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS ANZAHL_ART_3          

, COUNT( DISTINCT
  CASE
    WHEN LP_BELEGUNG.ART = 99 THEN  LP_BELEGUNG.LP_BELEGUNG_ID
    ELSE NULL
  END ) AS ANZAHL_ART_4

  FROM
     LP_BELEGUNG 
  WHERE 
     TO_CHAR(LP_BELEGUNG.GEN_DATUM, 'WW') = 37 --the calendar week

CodePudding user response：

Here is a set of dates counted and divided to days of the week using to_char and pivot.

select  *
from
(
select  dt
       ,to_char(dt, 'D') as dow
from    t
) t
pivot   (count(dt) for dow in('1', '2', '3', '4', '5', '6', '7')) p

'1'	'2'	'3'	'4'	'5'	'6'	'7'
1	1	0	0	1	3	1

Fiddle

CodePudding user response：

Use conditional aggregation:

SELECT TO_CHAR(MIN(GEN_DATUM),'DD.MM.YYYY')  AS GRD_ROW_ID,
       COUNT( DISTINCT
              CASE
              WHEN ART = 1
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 0
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_1_DAY1,
       COUNT( DISTINCT
              CASE
              WHEN ART = 1
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 1
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_1_DAY2,
       -- ...
       COUNT( DISTINCT
              CASE
              WHEN ART = 1
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 6
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_1_DAY7,
       COUNT( DISTINCT
              CASE
              WHEN ART = 2
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 0
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_2_DAY1,
       COUNT( DISTINCT
              CASE
              WHEN ART = 2
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 1
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_2_DAY2,
       -- ...
       COUNT( DISTINCT
              CASE
              WHEN ART = 2
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 6
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_2_DAY7,
       COUNT( DISTINCT
              CASE
              WHEN ART = 3
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 0
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_3_DAY1,
       COUNT( DISTINCT
              CASE
              WHEN ART = 3
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 1
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_3_DAY2,
       -- ...
       COUNT( DISTINCT
              CASE
              WHEN ART = 3
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 6
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_3_DAY7,
       COUNT( DISTINCT
              CASE
              WHEN ART = 99
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 0
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_4_DAY1,
       COUNT( DISTINCT
              CASE
              WHEN ART = 99
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 1
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_4_DAY2,
       -- ...
       COUNT( DISTINCT
              CASE
              WHEN ART = 99
              AND  TRUNC(gen_datum) - TRUNC(gen_datum, 'WW')  = 6
              THEN LP_BELEGUNG_ID
              END
       ) AS ANZAHL_ART_4_DAY7
FROM   LP_BELEGUNG 
WHERE  TO_CHAR(GEN_DATUM, 'WW') = 37