I meant consecutive days. Taking Patient 3 as an example:   1st prescription: 31AUG2013: NO FLAG   2nd prescription to 5th prescription: (NDC: 591261205) 09DEC2013 TO 12DEC2013 ie no break-in days which equates to 4 days.   6th prescription to 8th:  (NDC: 591261205) 19JUNE2014 TO 21STJUNE2014 ie no break in days which equates to 3 days   Question: why is duration not  7days because of same NDC? Explanation: 5th prescription was taken 12DEC2013 while 6th prescription was on 19JUNE2014 (a gap of almost 6 months), thus I want to keep them separate.   If patient 3, however, had 6th to 8th prescription from 13DEC2013 TO 15DEC2013 for that same NDC, Duration would have been added to 7days.   Sorry, English is not my first language, so I am trying to explain as best as I can. Again, thank you for your time:)     ... View more

The '3' is not added to the previous one even though they have the same NDC code because of the date variable. The patient had a prescription  in 12DEC2013 and the next one was on 19JUN2014 THROUGH 21JUNE2014 (3 DAYS). Because there is almost a 6months lapse between Dec and June, the dates are not consecutive.  ... View more

@PaigeMiller Thank you for your swift response. For ID, it summed up the non-consecutive events with same ndc into 5 is there a way to make that a distinction into 3 & 2?   Thank you for your help ... View more

@novinosrin yes sample 1 expected output is correct because the dates are not consecutive even though the NDC is the same. ie there's a break from 12dec2013 to 19june2014.  @PaigeMiller code collapses those two dates, so it is not correct for that ID ... View more