How can I combine these two query from the same table?

Question

Since "ColdFusion does not support joins through INNER JOIN or OUTER JOIN clauses" How would I be able to combine these two queries, where they give me data from the past 2 months?

select starburst_dept_name, starburst_dept_average,MONTHLY_PAST_DATE
  from cse_reports_month
  where starburst_dept_name is not NULL
 and YEAR(MONTHLY_PAST_DATE) = YEAR(GETDATE())
    and    MONTH(MONTHLY_PAST_DATE) = MONTH(GETDATE())-1


      select starburst_dept_name, starburst_dept_average,MONTHLY_PAST_DATE
  from cse_reports_month
  where starburst_dept_name is not NULL
 and YEAR(MONTHLY_PAST_DATE) = YEAR(GETDATE())
    and    MONTH(MONTHLY_PAST_DATE) = MONTH(GETDATE())-2

I would like a query to combine these two how would I do this so it would work in ColdFusion? What I would like to to is compare the starburst_dept_average last month > starburst_dept_average last 2 month ago?

what i would like to do is compare the starburst_dept_average , with last month > last 2 months ago — user3408399, Apr 23 '14 at 21:31
Your statement about joins is false. You can do outer and inner joins in your SQL just fine. — Chris Tierney, Apr 23 '14 at 21:33
Query Of Query does not support certain JOIN operations. One might JOIN subselects in the SQL statement against the database, though. — Bernhard Döbler, Apr 23 '14 at 21:36
@christierney i got that statement from http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html — user3408399, Apr 23 '14 at 21:38
Do not roll your own date logic. The logic above is flawed and will fail if the current month is January or February, because there is no month=0 or month=<-1>. Plus, it is not index-friendly anyway. A better option is to use actual date values: `where col >= {janaryFirstInSomeYear} and col < {januaryFirstInSomeYearPlusOne}` — Leigh, Apr 23 '14 at 21:38
That is for query of queries like @Bardware states. Is this SQL or QoQ? — Chris Tierney, Apr 23 '14 at 21:39
Rule of thumb: if you can do it in SQL or QOQ, do it in SQL. You will get better performance and the SQL engine is probably better and dealing with rows of data anyway — James A Mohler, Apr 24 '14 at 01:58

Dave Jemison · Accepted Answer · 2014-04-24T15:18:43.757

7

Just to clarify, CF does not support INNER AND OUTER JOINs only in a query of a query (QoQ). Is cse_reports_month the name of a query or the name of a database table? Your use of getdate() makes it sound like this is a regular SQL query, not a QoQ.

The following query should work according to your original logic:

SELECT starburst_dept_name, starburst_dept_average, MONTHLY_PAST_DATE
FROM   cse_reports_month
WHERE  starburst_dept_name is not NULL
AND    YEAR(MONTHLY_PAST_DATE) = YEAR(GETDATE())
AND    (
       MONTH(MONTHLY_PAST_DATE) = MONTH(GETDATE())-1 OR 
       MONTH(MONTHLY_PAST_DATE) = MONTH(GETDATE())-2
       )

But as was pointed out, this won't work for Jan. & Feb.- you'll need to use some different date comparison operators.

Depending on exactly what you are trying to show (and what the MONTHLY_PAST_DATE data represents), the following may work (you may need to modify a bit according your specific needs):

SELECT starburst_dept_name, starburst_dept_average, MONTHLY_PAST_DATE
FROM   cse_reports_month
WHERE  starburst_dept_name is not NULL
AND    DATEDIFF(m,MONTHLY_PAST_DATE,getdate()) IN (1,2)

edited Apr 24 '14 at 15:18

answered Apr 23 '14 at 21:40

Dave Jemison

694
3
7

thanks, i didnt even think about that, i thought 'sql' was smart enough to give me the right data on jan or february – user3408399 Apr 23 '14 at 22:18
it seems something like this works declare @today datetime set today ='2015-1-22' select DATEADD(month, -2, (today)), but still may need to change it – user3408399 Apr 23 '14 at 22:19
1

Databases do what you *tell* them to do - not what you *want* them to do ;-) Your SQL says perform a comparison between two numbers, so that is exactly what the database does. If you want to compare two *date* values, then you need to use the correct comparison expression. (Edit) As mentioned on a few of your other threads, [something like this construct](http://stackoverflow.com/questions/23090210/output-data-from-current-month#comment35301869_23090293) which is better in terms of correctness and performance. – Leigh Apr 23 '14 at 22:32
1

See also [Date Comparisons](http://stackoverflow.com/questions/13666883/date-comparison-in-ms-sql-2005/13687006#13687006) – Leigh Apr 23 '14 at 22:38
@DaveJemison - You might consider revising your answer to include a working version of the query. It will help the next guy, that comes across this thread :) – Leigh Apr 24 '14 at 01:32

score 2 · Answer 2 · answered Apr 23 '14 at 21:14

2

You can combine these query results by using the SQL UNION statement.
http://www.w3schools.com/sql/sql_union.asp

answered Apr 23 '14 at 21:14

itsben

1,017
1
6
11

How can I combine these two query from the same table?

2 Answers2