CODEWITHSUNDEEP
sqloracle11g
Lola_B
Lola_B

Reputation: 27

Efficient SQL -combining repetitive queries in one

I want to get some numbers and their sum should be greater than zero between the following timelines: Day-7>0 and Day-14>0 and Day-21>0. I'm trying to figure out if there's a way to combine the 3 sql statements into one single call more efficiently?

SELECT DISTINCT NUMBER FROM (
   SELECT    NUMBER,SUM(DATA) DATA
   FROM TABLEA
   WHERE
      DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-7,'YYYYMMDD'))
   GROUP BY VF_NUMBER
   HAVING SUM(DATA) > 0

AND

SELECT    NUMBER,SUM(DATA) DATA
FROM TABLEA
WHERE
    DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-14,'YYYYMMDD'))
GROUP BY VF_NUMBER
HAVING SUM(DATA) > 0

AND

SELECT    NUMBER,SUM(DATA) DATA
FROM TABLEA
WHERE
    DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-21,'YYYYMMDD'))
GROUP BY VF_NUMBER
HAVING SUM(DATA) > 0

)

Upvotes: 0

Views: 73

Answers (2)

Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

actually >7 , >14 , >21 = >7 isn't it?

Following should work:

I have edited response as per OP's comment

SELECT VF_NUMBER
FROM 
(SELECT  VF_NUMBER,
         SUM(CASE WHEN DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-7,'YYYYMMDD'))  THEN DATA ELSE 0 END) GREATER_7,
         SUM(CASE WHEN DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-14,'YYYYMMDD'))  THEN DATA ELSE 0 END) GREATER_14, 
         SUM(CASE WHEN DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-21,'YYYYMMDD'))  THEN DATA ELSE 0 END) GREATER_21, 
  NUMBER,SUM(DATA) DATA
FROM TABLEA
WHERE DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-7,'YYYYMMDD'))
GROUP BY VF_NUMBER)
WHERE GREATER_7>0
AND GREATER_14>0
AND GREATER_21>0

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44981

  • SYSDATE-21 covers all other periods.
  • I assume NUMBER should be replaced with VF_NUMBER. 
SELECT    VF_NUMBER
FROM TABLEA
WHERE
    DATEID >= TO_NUMBER(TO_CHAR(SYSDATE-21,'YYYYMMDD'))
GROUP BY VF_NUMBER
HAVING SUM(DATA) > 0

Upvotes: 0

Related Questions