RE: [firebird-support] QUERY QUESTION
Brilliant thank you Set that does the trick Stef van der Merwe From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 06 March 2017 09:05 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] QUERY QUESTION 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' s...@autotech.co.za [firebird-support] <firebird-support@yahoogroups.com>: 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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 03 March 2017 10:27 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] QUERY QUESTION Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [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" <s...@autotech.co.za> ++ 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
Re: [firebird-support] QUERY QUESTION
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' s...@autotech.co.za [firebird-support] < firebird-support@yahoogroups.com>: > 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: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: 03 March 2017 10:27 PM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] QUERY QUESTION > > Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [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" <s...@autotech.co.za> > > > ++ > > 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 > > > >
Re: [firebird-support] QUERY QUESTION
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 <setys...@gmail.com>: > 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' s...@autotech.co.za [firebird-support] < > firebird-support@yahoogroups.com>: > >> 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: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] >> Sent: 03 March 2017 10:27 PM >> To: firebird-support@yahoogroups.com >> Subject: Re: [firebird-support] QUERY QUESTION >> >> Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za >> [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
RE: [firebird-support] QUERY QUESTION
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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 03 March 2017 10:27 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] QUERY QUESTION Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [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
Re: [firebird-support] QUERY QUESTION
You could use a stored procedure
SET TERM ^ ;
CREATE OR ALTER PROCEDURE CONSECUTIVE_DAYS (ipITEMNUMBER CHAR(3))
RETURNS (opDATEFROM DATE, opDATETO DATE, opCONSECUTIVEDAYS INTEGER)
AS
DECLARE VARIABLE vItem CHAR(3);
DECLARE VARIABLE vDate DATE;
BEGIN
opCONSECUTIVEDAYS = 0;
-- get latest day used or pass in as parameter
SELECT FIRST 1 DATEUSED
FROM ITEM_USED
WHERE ITEMNR = :ipITEMNUMBER
ORDER BY DATEUSED DESC
INTO :opDATETO;
FOR
SELECT ITEMNR, DATEUSED
FROM ITEM_USED
WHERE ITEMNR = :ipITEMNUMBER
GROUP BY ITEMNR, DATEUSED
ORDER BY DATEUSED DESC
INTO :vItem, :vDate
DO
BEGIN
IF (vDate = opDATEFROM -1) THEN
opCONSECUTIVEDAYS = opCONSECUTIVEDAYS + 1;
IF (vDate < opDATEFROM -1) THEN
BREAK;
opDATEFROM = vDate;
END
IF ((opCONSECUTIVEDAYS > 0) OR ((opDATEFROM = opDATETO) AND (opDATEFROM
IS NOT NULL))) THEN
opCONSECUTIVEDAYS= opCONSECUTIVEDAYS + 1;
SUSPEND;
END ^
SET TERM ; ^
SELECT opDATEFROM, opDATETO, opCONSECUTIVEDAYS
FROM CONSECUTIVE_DAYS ('abc');
On 2 March 2017 at 17:36, 'Stef' s...@autotech.co.za [firebird-support] <
firebird-support@yahoogroups.com> wrote:
>
>
> 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]
>
>
>
Re: [firebird-support] QUERY QUESTION
Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] QUERY QUESTION
Stef, Will this work. WITH CTE AS (SELECT J0.*, (SELECT COUNT(*) FROM TEST_TABLE S1 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, 3 desc) J0), CTE_SUM AS (SELECT ITEMNR, MAX(END_DATE) LATEST_DATE FROM CTE GROUP BY ITEMNR) SELECT CTE.* FROM CTE INNER JOIN CTE_SUM ON CTE.ITEMNR = CTE_SUM.ITEMNR AND CTE.END_DATE = CTE_SUM.LATEST_DATE I hope this is what you are looking for. Thanks, Edward Mendez From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Friday, March 3, 2017 1:05 AM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] QUERY QUESTION Importance: High 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> [mailto:firebird-support@yahoogroups.com] Sent: 03 March 2017 01:20 AM To: firebird-support@yahoogroups.com <mailto: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> [mailto:firebird-support@yahoogroups.com] <mailto:[mailto:firebird-support@yahoogroups.com]> Sent: Thursday, March 2, 2017 11:37 AM To: firebird-support@yahoogroups.com <mailto: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]
RE: [firebird-support] QUERY QUESTION
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]
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]
RE: [firebird-support] QUERY QUESTION
Select itemnr,count(*) as days >From table Group by itemnr,cast(dateused as date) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 02 March 2017 06:37 PM 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] [Non-text portions of this message have been removed]
Re: [firebird-support] QUERY QUESTION
select itemnr count(dateused) from table group by itemnr Best Regards | ISMAEL | - Original Message - From: 'Stef' s...@autotech.co.za [firebird-support] To: firebird-support@yahoogroups.com Sent: Thursday, March 02, 2017 11:36 AM Subject: [firebird-support] QUERY QUESTION 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]
[firebird-support] QUERY QUESTION
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]
