sqldatedb2datefilter

SQL Query to Count Items Within Date Range?

To start, I know just enough at this point to get myself into trouble. I am working on writing a query for an AS/400 system. I have a file that contains a start date and end date. What I want to do is create a query that can count how many line items were open for a range of dates. (for my application open is defined as: DATE > START_DATE AND (DATE <= END_DATE OR END_DATE = '0')). I can get this working for manually setting "DATE", but I'm trying to go back and pull this information in bulk historically. What I'm having trouble with is having it run this query against a list of dates. What I want is to input the historical interval (say CURRENT_DATE - 5) and output:

Date             Open
20200206         30,000
20200207         31,000
20200210         29,675
20200211         31,375
20200212         32,000

What I use for 'one-day-at-a-time' query:

SELECT
        COUNT(*) 
    FROM
        MYFILE
    WHERE 
        START_DATE < '20200210' 
    AND (
            END_DATE >= '20200210' 
        OR  END_DATE = '0'
        )

This works great, but is time consuming. From here I have no idea how to transition to the desired output...or even if it's possible...but it would speed things up for me. Thanks in advance.

P.S. any help is welcomed, if you know how to accomplish this in mySQL etc. please share. I can take that and run with it in terms of getting it to work for AS/400 specifically. That is how I've gotten to where I'm at now.

**************** Edit for clarifications 1. Current Code The current-day code provided is all that I have. I don't know where to start on what I am asking for. 2. Example Data:

Line Item       START_DATE        END_DATE
1               20200206          20200210
2               20200207          20200210
3               20200207          20200207
  1. Expected Output:
DATE           COUNT
20200205       0
20200206       0
20200207       1
20200210       2
20200211       0
  1. DBMS I've updated the tag to include DB2. 5. I don't believe GROUP BY will work.
    Basically I am looking for this to be calculated in a iterative-esque fashion. Coming from my python/java background this makes sense to me. I'm not looking for everything that was open 5 days ago, I want to start 5 days ago and calculate how many line items were open each of those 5 days. If this is in fact possible with GROUP, please elaborate and show me the error of my ways. Meanwhile, I will begin messing with grouping to this end.
Solution

  • Below is a query returning consecutive dates between given date and given date - 5 days. You may use CURRENT DATE instead of this constant of course.

    WITH T (DT) AS 
(
VALUES DATE('2020-02-13') - 5 DAYS
  UNION ALL
SELECT DT + 1 DAY
FROM T 
WHERE DT < DATE('2020-02-13')
)
SELECT DT FROM T;

    The result is:

    |DT        |
|----------|
|2020-02-08|
|2020-02-09|
|2020-02-10|
|2020-02-11|
|2020-02-12|
|2020-02-13|

    Further usage of such a "virtual table" is obvious: you may LEFT JOIN it with your base table on B.START_DATE < DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) AND DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) <= B.END_DATE grouping the result by T.DT.