'SQL QUERY- How to remove repetitive days in sum function in "PIVOT" clause

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            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            01-12-2021              PTO                             1
1010            02-12-2021              sick                            1                   
1010            30-12-2021              sick                            1
1010            30-01-2022              SICK                            1

The output looks like -

 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         4               0.52
    1010                    VACATION           1   0.2
    1010                    SICK                                           1                                                        2

Using the query -

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

This is giving me the correct output mostly. But I want if for 2 dates say for example 01-01-2022 which has PTO twice, so the SUM of abs_Days for that date should be 1 and not 2. How can i tweak this inside the pivot function ?

sqloraclebi-publisherpythonpython-3.xlistcompare


Solution 1:[1]

Maybe I'm wrong, but it looks to me like the value of ABS_DAYS per every day in month (1 to 31, don't know why 0 is here) should not be greather then 1 (as like 1 day is 1 day max). In that case you should probably handle that in the Select clause...

SELECT 
    PERSON_NUMBER, ABS_TYPE_NAME, 
    CASE WHEN "0" > 1 THEN 1 ELSE "0" END "0", 
    CASE WHEN "1" > 1 THEN 1 ELSE "1" END "1", 
    CASE WHEN "2" > 1 THEN 1 ELSE "2" END "2",
    CASE WHEN "3" > 1 THEN 1 ELSE "3" END "3",
    CASE WHEN "4" > 1 THEN 1 ELSE "4" END "4",
    CASE WHEN "5" > 1 THEN 1 ELSE "5" END "5",
    CASE WHEN "6" > 1 THEN 1 ELSE "6" END "6",
--  ...
    CASE WHEN "30" > 1 THEN 1 ELSE "30" END "30",
    CASE WHEN "31" > 1 THEN 1 ELSE "31" END "31"
FROM
( 
    SELECT PERSON_NUMBER,
           EXTRACT(DAY FROM TO_DATE(ABS_DATE)) AS DAY_X,
           ABS_TYPE_NAME,
           ABS_DAYS
    FROM TABLE1
    -- 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))

Solution 2:[2]

You could try the following rudimental one:

list1 = [("JOHN","BLUE","BANANA"),("JOHN","BLUE", "APPLE"),("MARY","PURPLE","GRAPE"),("BEN","GREEN","WATERMELON")]
list2 = [("JOHN","BLUE"),("MARY","PURPLE"),("TOMMY","PINK")]

tmp = set()
for i in list1:
    for j in list2:
        if len(set(i)&set(j)) > 0:
            tmp.add(i)
print tmp

Result:

set([('MARY', 'PURPLE', 'GRAPE'), ('JOHN', 'BLUE', 'BANANA'), ('JOHN', 'BLUE', 'APPLE')])

Solution 3:[3]

def common_member(a, b):
    a_set = set(a)
    b_set = set(b)
 
    if (a_set & b_set):
        print(a_set & b_set)
    else:
        print("No common elements")
a = [JOHN,BLUE,BANANA),(JOHN,BLUE, APPLE),(MARY,PURPLE,GRAPE),(BEN,GREEN,WATERMELON]
b = [(JOHN,BLUE),(MARY,PURPLE),(TOMMY,PINK)]
common_member(a, b)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 d r
Solution 2 baskettaz
Solution 3 ZygD

Related Questions