0

I have a table abs_details that give data like follows -

PERSON_NUMBER       ABS_DATE            ABS_TYPE_NAME               ABS_DAYS
1010            01-01-2022              PTO                             1
1010            06-01-2022              PTO                             0.52
1010            02-02-2022              VACATION                        1
1010            03-02-2022              VACATION                        0.2
1010            01-12-2021              PTO                             1
1010            02-12-2021              sick                            1                   
1010            30-12-2021              sick                            1
1010            30-01-2022              SICK                            1

I want this data to be displayed in the following way:

 PERSON_NUMBER          ABS_TYPE_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   
    1010                    PTO             2            0.52
    1010                    VACATION           1   0.2
    1010                    SICK                                           1                                                        2

For the days, 1-31 should should come in the header, if there is any absence taken on say 01st of the month or quarter passed then the value should go under 1 , if there is no value for date of the month, say no value is there from 07th-11th in the above case, then output should display the numbers but no value should be provided under it.

Is this feasible in SQL? I have an idea we can use pivot, but how to fix 1-31 header and give values underneath each day. Any suggestions?

  • If I pass multiple quarter that is Q1(JAN-MAR), Q2(APR-JUN) it should sum up the values between the dates between those two quarters. if Just q1 then only q1 result
  • If I pass multiple month then it should display the sum of the values for an absence type in those multiple months.

I will be passing the year in the parameter and the above two should consider the year I pass.

SSA_Tech124
  • 577
  • 1
  • 9
  • 25

1 Answers1

1

Create a column which has all the dates, and pivot up using pivot function in oracle.

SELECT *
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE
    -- Add additional filter here which you want
 ) 
PIVOT(SUM(ABS_DAYS)
         FOR DAY_X IN (0,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))

Db fiddle - https://dbfiddle.uk/?rdbms=oracle_21&fiddle=ad3af639235f7a6db415ec714a3ee0d9

Equinox
  • 6,483
  • 3
  • 23
  • 32
  • Hi equinox, when I am adding quarter in teh filter condition it is not giving any output, it just keeps running – SSA_Tech124 May 18 '22 at 17:24
  • Not sure what's the issue works for me as you can see here https://dbfiddle.uk/?rdbms=oracle_21&fiddle=0d65ab1853de37f6ee9cbf5d0eb90561 – Equinox May 20 '22 at 11:17