0

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>
Leigh
  • 28,765
  • 10
  • 55
  • 103
Patrick Schomburg
  • 2,494
  • 1
  • 18
  • 46
  • 1
    QoQ's do not support date functions. Read [the documentation](http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html) for more details. If you need date functions, that must be done in a db query. Syntax is dbms specific. – Leigh Jul 06 '16 at 13:57
  • What would this look like in oracle? – Patrick Schomburg Jul 06 '16 at 14:00
  • No idea. I am not an Oracle guy. Did you do a search on Oracle and date functions? I am sure it must have equivalent of the dateAdd function. – Leigh Jul 06 '16 at 15:00

1 Answers1

0

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
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Unless there is a good reason to do otherwise, let the database do what it does best. Using QoQ's to avoid writing a proper database query is a poor approach. – Leigh Jul 06 '16 at 16:57