Using LAG Or Other Function In SUM Clause

I am looking for some guidance on an Oracle SQL Query I have been struggling with. I have a data table that contains Time Stamps and Help-Desk Ticket numbers... Every time a techni

Solution 1:

I could be misinterpreting your question, but I believe you just need to add ROWS BETWEEN to your SUM():

SUM(ColName) OVER(ORDER BY Col2 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Solution 2:

UPDATED: Try it this way

SELECT full_name, 
       COUNT(DISTINCT entityid) ticket_count,
       COUNT(DISTINCT series) - COUNT(DISTINCT entityid) touch_count
  FROM
(
  SELECT plannerid, full_name, entityid, 
         rnum - ROW_NUMBER() OVER (PARTITION BY plannerid, entityid ORDER BY rnum) series
    FROM
  (  
    SELECT p.plannerid, 
           UPPER(p.plannersurname || ', ' || p.plannerfirstname) full_name,
           h.historydate, 
           h.entityid,
           ROW_NUMBER() OVER (PARTITION BY p.plannerid ORDER BY h.historydate) rnum
      FROM ipcs_audit_history h JOIN ipcs_planner p
        ON h.plannerid = p.plannerid
     WHERE h.historydate >= TO_DATE('30-DEC-13')
       AND h.historydate <  TO_DATE('30-DEC-13') + 1
  ) q
) p
 GROUP BY plannerid, full_name

Sample output:

| FULL_NAME | TICKET_COUNT | TOUCH_COUNT |
|-----------|--------------|-------------|
| DOE, JOHN |            4 |           1 |

Here is SQLFiddle demo