Oops, forgot to include the last three lines:
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 AND
TTFirst.DateUsed <= TTLast.DateUsed
WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot
WHERE TTFirst.ItemNr = TTNot.ItemNr
AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
AND TTLast.DateUsed <= :MyEarlierDate
GROUP BY 1)
SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays
FROM TMP
2017-03-06 8:04 GMT+01:00 Svein Erling Tysvær <[email protected]>:
> 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 AND
> TTFirst.DateUsed <= TTLast.DateUsed
> WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot
> WHERE TTFirst.ItemNr = TTNot.ItemNr
> AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
> AND TTLast.DateUsed <= :MyEarlierDate
> GROUP BY 1)
> SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays
> FROM TMP
>
> 2017-03-06 7:44 GMT+01:00 'Stef' [email protected] [firebird-support] <
> [email protected]>:
>
>> 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
>>
>>
>>
>>
>>
>> ------------------------------------
>> Posted by: "Stef" <[email protected]>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> 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/resou
>> rces/documents/
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>>
>
