Using LAG or Other function in SUM clause

Question

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 technician makes a change to the ticket or logs a note the corresponding time stamp will log in the database along with the ticket number. What i am trying to accomplish is to count the first occurrence of the ticket number in each series and i cant use distinct because it will not count the ticket again if the technician works the ticket again later in the day. Please reference the attached picture.

http://s13.postimg.org/i5gx4l4nb/sample.jpg

Using the lag function i can flag the tickets at the row level but i cant add this to a SUM clause to accomplish my ultimate goal.

CASE WHEN LAG(IPCS_AUDIT_HISTORY.ENTITYID,1) over (ORDER BY IPCS_AUDIT_HISTORY.HISTORYDATE)=IPCS_AUDIT_HISTORY.ENTITYID THEN 0 ELSE 1 END

Any ideas or thoughts on what direction to take this in?

SELECT
  UPPER(IPCS_PLANNER.PLANNERSURNAME || ', ' || IPCS_PLANNER.PLANNERFIRSTNAME) AS FULL_NAME,
  COUNT(DISTINCT IPCS_AUDIT_HISTORY.ENTITYID) AS TICKETS_COUNT,
  SUM(CASE WHEN LAG(IPCS_AUDIT_HISTORY.ENTITYID,1) over (ORDER BY IPCS_AUDIT_HISTORY.HISTORYDATE)=IPCS_AUDIT_HISTORY.ENTITYID THEN 0 ELSE 1 END) AS TOUCH_COUNT
FROM 
  IPCS_AUDIT_HISTORY,
  IPCS_PLANNER
WHERE
  IPCS_AUDIT_HISTORY.PLANNERID=IPCS_PLANNER.PLANNERID and
  TRUNC(IPCS_AUDIT_HISTORY.HISTORYDATE, 'IW')='30-DEC-13'
GROUP BY
  UPPER(IPCS_PLANNER.PLANNERSURNAME || ', ' || IPCS_PLANNER.PLANNERFIRSTNAME)

What is the desired output from the shown in the picture sample data? — peterm, Jan 09 '14 at 03:02
A count of 5. if i was to run the select on the table in the picture, i would get 4 for the ticket count and 5 for the touch count... — user2708719, Jan 09 '14 at 03:33

score 5 · Answer 1 · answered Jan 09 '14 at 03:02

5

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)

answered Jan 09 '14 at 03:02

Hart CO

34,064
6
48
63

1

I don't know if this is the right answer to the question, but for me it helped a lot! Thanks. – user2511599 Apr 16 '19 at 11:32

peterm · Accepted Answer · 2014-01-09T05:03:40.990

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

Hello- thank you for taking time out of your day to work on this. I greatly appreciate your guidance. Unfortunately i was not able to get your code to work but it did get me pointed in the right direction and i ultimately came up with the below statement that is working as desired. — user2708719, Jan 10 '14 at 07:41

Using LAG or Other function in SUM clause

2 Answers2

Linked