Hi,
I have the following table DDL :
**CREATE TABLE KNDTSCM**
**(**
**"TSCM\_CNCONTRA" NUMBER(8, 0) NOT NULL ENABLE,**
**"TSCM\_CNSERVIC" CHAR(4 BYTE) NOT NULL ENABLE,**
**"TSCM\_CNSUBSER" CHAR(2 BYTE) NOT NULL ENABLE,**
**"TSCM\_CNMAPCO" CHAR(4 BYTE) NOT NULL ENABLE,**
**"TSCM\_CNCNLIDA" CHAR(4 BYTE) DEFAULT '0030' NOT NULL ENABLE,**
**"TSCM\_CNCENTID" CHAR(4 BYTE) DEFAULT 'H2H1' NOT NULL ENABLE,**
**"TSCM\_CNESTADO" CHAR(2 BYTE),**
**"TSCM\_CNFHALTA" DATE DEFAULT SYSDATE,**
**"TSCM\_CNFHULMO" TIMESTAMP(6) DEFAULT SYSDATE,**
**"TSCM\_CNUSUALT" VARCHAR2(8 BYTE) NOT NULL ENABLE,**
**"TSCM\_CNUSUMOD" VARCHAR2(8 BYTE),**
**"TSCM\_CNYACURE" CHAR(1 BYTE),**
**"TSCM\_CNYCONAR" CHAR(1 BYTE),**
**"TSCM\_CNACTIVS" DATE**
**)**
And the below sample data with the relevant columns highlighted
My problem is as below :
When tscm_cnfhulmo has anything less than SYSDATE, i don't want the query to select anything.If any date is equal to SYDATE, i do want the query to return me the data, but if i run the same query tomorrow and there is one record with the SYSDATE and the existing records from today I do not want it to give me an output. Makes sense? The query i have below is still returning meaning my HAVING clause is not working.
SELECT DISTINCT tscm\_cncontra, COUNT(\*)
FROM kndtscm
WHERE tscm\_cnservic = '3102'
AND tscm\_cnsubser IN ('I1','I2','R1','R2')
AND tscm\_cnfhulmo > trunc(SYSDATE)
GROUP BY tscm\_cncontra
HAVING COUNT(tscm\_cnestado) NOT IN (SELECT COUNT(tscm\_cnestado)
FROM kndtscm
WHERE tscm\_cnservic = '3102'
AND tscm\_cnsubser IN ('I1','I2','R1','R2')
AND tscm\_cnfhulmo \< trunc(SYSDATE)
)