Appending a string

Question

I am building a chart and need to have all of the days of a given month represented for applications submitted. On some days, there may be 5 and on others there may be 0. I am getting my data from an applications table but I need append the string for dates that do not have a value. For example today is 05/29 and 3 people applied. On 05/21, 2 people applied. In between (and before that) no others applied. So I need to get those dates and store a 0 value for them.

Below is what I am using to bring back and group the data:

SELECT DISTINCT to_char ("applicationdate",'mm') AS applicationmonth
      , to_char ("applicationdate",'dd') AS applicationdate
      , to_char ("applicationdate", 'yyyy') AS applicationyear
      , term
      , COUNT(*) AS count
FROM  campapplications.basketball
WHERE term = 'SU' 
GROUP BY applicationdate, term
ORDER BY applicationdate, term

At the end of the day, I am doing an insert.

I have looked other places but could not really get any input outside of using the listAppend() function.

Answer 1

It's not clear how "appending a string" relates to charting or inserts... However, just focusing on the query, either a calendar table or CTE could be used generate all dates within a specific date range. (For larger data sets, a calendar table is usually better). Once you have the dates, outer join them back to your application table to get the counts for each date.

You didn't mention which DBMS you are using, but from the syntax I'm guessing Oracle. Assuming the "applicationDate" column ONLY contains a date (no time), the query below would return a count for all dates within the given range: #startDate# and #endDate#. Adjust the dates as needed.

SQL: http://sqlfiddle.com/#!4/c09877/2

WITH CTE(TheDate) AS
(
    SELECT FromDate+level-1
    FROM (
        SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS FromDate
               , <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">  AS ToDate 
        FROM   dual
     ) d
     CONNECT BY LEVEL <=  ToDate - FromDate + 1
)
SELECT TheDate
      , COUNT(b.applicationDate) AS count
FROM   cte LEFT JOIN basketball b ON b.applicationDate = cte.TheDate
GROUP BY TheDate
ORDER BY TheDate      
;

Sample Results:

|              THEDATE | COUNT |
|----------------------|-------|
| 2019-05-21T00:00:00Z |     2 |
| 2019-05-22T00:00:00Z |     0 |
| 2019-05-23T00:00:00Z |     0 |
| 2019-05-24T00:00:00Z |     0 |
| 2019-05-25T00:00:00Z |     0 |
| 2019-05-26T00:00:00Z |     0 |
| 2019-05-27T00:00:00Z |     0 |
| 2019-05-28T00:00:00Z |     0 |
| 2019-05-29T00:00:00Z |     3 |

Update:

I'm not great with PostgreSQL, but this thread shows how to generate a date range. Just replace the hard coded dates with cfqueryparam.

SQL: http://sqlfiddle.com/#!15/2a0a7/4

SELECT TheDate
      , COUNT(b.applicationDate) AS count
FROM  generate_series
        ( '2019-05-21'::date 
        , '2019-05-29'::date
        , '1 day'::interval) TheDate 
       LEFT JOIN basketball b ON b.applicationDate = TheDate
GROUP BY TheDate
ORDER BY TheDate