oracle-databasecoldfusionqoq

Coldfusion query of queries count by date

I'm trying to get an count based on two dates and I'm not sure how it should look in a query. I have two date fields; I want to get a count based on those dates. 

<cfquery>
    SELECT COUNT(*)
    FROM Table1
    Where month of date1 is one month less than month of date2 
</cfquery>
Solution

  • Assuming Table1 is your original query, you can accomplish your goal as follows.

    Step 1 - Use QueryAddColumn twice to add two empty columns.

    Step 2 - Loop through your query and populate these two columns with numbers. One will represent date1 and the other will represent date2. It's not quite as simple as putting in the month numbers because you have to account for the year as well.

    Step 3 - Write your Q of Q with a filter resembling this:

    where NewColumn1 - NewColumn2 = 1