Group By & Having problem

[WWRA]

I tried to "search" on this forum, however the "search" function is currently not available.

I'm very new to SQL, so please bear with me. The code below gives this result:
LWIA Participants
00 1

However, if I remove the Having statement, I get LWIA
00 through 13, which is correct but,the number of participants is not correct.
LWIA Participants
00 1
01 500
02 112
03 222
etc. through LWIA 13

I'm using MAX because there could be several occurrences of SPP.ACTV_ACTL_END_DATE for one participant.
In other words, one participant could have several activities associated with him. However, all I need is the latest activity date.

What am I doing wrong. I've read about the Having statement and everything appears to be coded okay.
Why does removing the Having statement give all LWIAs, even though the number of participants is incorrect. I need to refer to only the latest activity date when doing the calculation, so the Having statement is critical.

Code Follows:

select SUBSTR (IPP.CUR_LOC_ID,3,2)"LWIA", Count(Distinct IPP.PARTC_ID) "Participants"
from ecmatsdba.ETR_INT_PARTC_PGM IPP,ecmatsdba.ETR_SDT_PARTC_PGM_ACTV SPP
where SUBSTR (IPP.Cur_Loc_ID, 3,2)is not null
and IPP.Pgm_Actv_Ind = 1
and IPP.AGY_ID = 'LW'
and IPP.partc_id = SPP.partc_id
and IPP.APPLC_ID = SPP.APPLC_ID
and SPP.ACTV_ACTL_END_DTE is not null
Group by SUBSTR (IPP.CUR_LOC_ID,3,2)
-- remove Having and you get all LWIAs
-- but the number of participangts is incorrect.
Having SYSDATE - MAX(SPP.ACTV_ACTL_END_DTE) >90
Order By "LWIA"

TIA for any assistance.
Newbie

 

[rac2]

I dont know, there might be a way to make that work, but I think I would take a different tack.

I think of the HAVING clause as being applied to the rows generated by the GROUP BY query. So any conditions I put in the HAVING clause use values in the rows produced in the GROUP BY query. I dont see any columns with SYSDATE or MAX(SPP.ACTV_ACTL_END_DTE) in the SELECT list in the GROUP BY query.

And then you say the COUNTS arent correct either.

And you are writing a JOIN query too, so why not use JOIN syntax? It really helps me to separate the filtering criteria from the JOINery.

With those thoughts in mind -

SELECT SUBSTR (IPP.CUR_LOC_ID,3,2) AS "LongWarInAkkadia",
       COUNT(IPP.PARTC_ID) AS "Participants"
FROM ecmatsdba.ETR_INT_PARTC_PGM IPP
JOIN ecmatsdba.ETR_SDT_PARTC_PGM_ACTV SPP ON
     IPP.partc_id     = SPP.partc_id
     AND IPP.APPLC_ID = SPP.APPLC_ID
     AND SPP.ACTV_ACTL_END_DTE is not null

WHERE SUBSTR (IPP.Cur_Loc_ID, 3,2)is not null
  AND IPP.AGY_ID       = 'LW'
  AND IPP.Pgm_Actv_Ind = 1

GROUP BY SUBSTR (IPP.CUR_LOC_ID,3,2)

I dont know why one would write
Count(Distinct IPP.PARTC_ID). Do you find that counting DISTINCT gives the number of different values of PARTC_ID for a given "LWIA". It might, I dont know. I would try the query with and without the DISTINCT keyword and see if I got different results.

I know that COUNT(IPP.PARTC_ID) gives the number of rows with a value in the PARTC_ID column and ignores rows with NULLs.

So my query is summarizing by location, or SUBSTR (IPP.CUR_LOC_ID,3,2).

It is limited to the rows in ecmatsdba that have a location, are run by the 'LW' agency, and have an active, independent program (or some such).

These rows are JOINed to rows with the same application and partc id but with an end date.

A location may have multiple applications and partc ids.
The problem is, how many.


But back to your question. I would put the
SYSDATE - MAX(SPP.ACTV_ACTL_END_DTE) >90
in the WHERE clause or in the JOIN conditions, as it seems to be a limit on which rows you want to count rather than on which counts you want to see.

To do that you need to create a VIEW or derived table (to borrow E**2's terminology if I understand it correctly).

/* Create a VIEW */
CREATE VIEW LatestActivity AS
SELECT partc_id,
       APPLC_ID,
       MAX(ACTV_ACTL_END_DTE) AS "LateDate"
FROM ecmatsdba
GROUP BY partc_id, APPLC_ID



/* Use it in the JOIN */
SELECT SUBSTR (IPP.CUR_LOC_ID,3,2) AS "LongWarInAkkadia",
       COUNT(IPP.PARTC_ID) AS "Participants"
FROM ecmatsdba.ETR_INT_PARTC_PGM IPP
JOIN ecmatsdba.ETR_SDT_PARTC_PGM_ACTV SPP ON
     IPP.partc_id     = SPP.partc_id
     AND IPP.APPLC_ID = SPP.APPLC_ID
JOIN LatestActivity dt ON
     dt.partc_id     = IPP.partc_id
     AND dt.APPLC_ID = IPP.APPLC_ID
     AND dt.LateDate = SPP.ACTV_ACTL_END_DTE
     AND (SYSDATE - dt.LateDate) > 90

WHERE SUBSTR (IPP.Cur_Loc_ID, 3,2)is not null
  AND IPP.AGY_ID       = 'LW'
  AND IPP.Pgm_Actv_Ind = 1

GROUP BY SUBSTR (IPP.CUR_LOC_ID,3,2)

The addtional JOIN acts like a filter, only the partc_id, APPLC_ID s with the latest date will be used.

 

[WWRA]

rac2,
thanks for the insight & suggestions. I'll use your ideas and see if they work. Stay tuned, this could get interesting ;>)

Seriously, I really appreciate your taking the time to respond. Being new at this SQL stuff has proven to be quite a challenge. I can use all the help I can get!