Edward
Thank you very much, it is something like this that I am looking for. The only issue is that I only need to find the count for the latest consecutive days used. i.e. If the itemnr was used for 12 weeks consecutively and then had a day(or two) off and then used again for 5 days consecutively, I need to see a result of 5 and not 84. (MAX(DAYS) as per your query). MIN(DAYS) will also not work as it will return the minimum consecutive days worked and not the count of latest days. I hope this makes sense. Regards Stef From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 03 March 2017 01:20 AM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] QUERY QUESTION Stef, Maybe this query might work for you. WITH CTE AS (SELECT J0.*, (SELECT COUNT(*) FROM TEST_TABLE S1 -- LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR = S2.ITEMNR AND S1.DATEUSED = S2.DATEUSED - 1 WHERE S1.DATEUSED >= J0.START_DATE AND S1.ITEMNR = J0.ITEMNR AND S1.DATEUSED < J0.END_DATE) + 1 DAYS FROM (SELECT DISTINCT J1.ITEMNR, J1.DATEUSED START_DATE, (SELECT FIRST 1 S1.DATEUSED FROM TEST_TABLE S1 LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR = S2.ITEMNR AND S1.DATEUSED = S2.DATEUSED - 1 WHERE S2.ITEMNR IS NULL AND S1.DATEUSED > J1.DATEUSED AND S1.ITEMNR = J1.ITEMNR ORDER BY 1) END_DATE FROM TEST_TABLE J1 LEFT OUTER JOIN TEST_TABLE J2 ON J1.ITEMNR = J2.ITEMNR AND J1.DATEUSED = J2.DATEUSED + 1 WHERE J2.ITEMNR IS NULL ORDER BY 1, 2) J0), CTE_SUM AS (SELECT ITEMNR, MAX(DAYS) DAYS FROM CTE GROUP BY ITEMNR) SELECT CTE.* FROM CTE INNER JOIN CTE_SUM ON CTE.ITEMNR = CTE_SUM.ITEMNR AND CTE.DAYS = CTE_SUM.DAYS I hope this helps, Edward Mendez From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, March 2, 2017 11:37 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] QUERY QUESTION Importance: High Good day, I have a question for you SQL boffins, how can I query a table to get the count of consecutive days found ? A Table contains rows with dates where an item(s) has been added for every day this item was used. i.e. itemnr dateused abc 2017/02/01 abc 2017/02/02 abc 2017/02/25 abc 2017/02/25 abc 2017/02/25 abc 2017/02/26 abc 2017/02/27 abc 2017/02/28 abc 2017/03/01 abc 2017/03/02 The result from above sample should be 6 as there is a break between 2017/02/02 and 2017/02/25? Regards Stef [Non-text portions of this message have been removed]