Hi Set, Your solution returns exactly what I am looking for. Much appreciated.
However when trying to query a snapshot for an earlier date, other than the most recent day, seems problematic. Stef van der Merwe -----Original Message----- From: [email protected] [mailto:[email protected]] Sent: 03 March 2017 10:27 PM To: [email protected] Subject: Re: [firebird-support] QUERY QUESTION Den 02.03.2017 17:36, skrev 'Stef' [email protected] [firebird-support]: > 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? Hi Stef, thanks for teaching me a new word, I've never heard 'boffin' before! I think your task roughly can be reformulated as: I want to find the number of days from the most recent day of a record without any record for the preceeding day to the most recent day for a particular item, and add 1 to the result. If so, this query will get you what you're asking for, although it may take a while to return any result if the table is large: WITH TMP(ItemNr, FirstDate, LastDate) AS (SELECT TTFirst.ItemNr, MAX(TTFirst.DateUsed), MAX(TTLast.DateUsed) FROM TEST_TABLE TTFirst JOIN TEST_TABLE TTLast ON TTFirst.ItemNr = TTLast.ItemNr WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot WHERE TTFirst.ItemNr = TTNot.ItemNr AND TTFirst.DateUsed - 1 = TTNot.DateUsed) GROUP BY 1) SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays FROM TMP So, what originally seems simple but grows complex once thinking about how to solve it, happens to have a rather simple solution. HTH, Set ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links
