Subject RE: [firebird-support] QUERY QUESTION
Author Edward Mendez

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]
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]