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/
> resources/documents/
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
