kevinabraham kevinabraham - 24 days ago 12
SQL Question

How to get the third month of a query without using variables?

I have a query in which I need to get the third month of the given reporting date using SQL and then use it as part of the query. I am able to get all the months but I specifically need to get the third month how would I go about doing that? I know this is fairly easy to do in other languages but is it possible in SQL?

SELECT REPORTING_MONTH, COUNT(*)
FROM database1 AS fb
JOIN (
--derrived core set
SELECT service_no, subscription_id
FROM database2
WHERE REPORTING_MONTH = '2015-04-01' <-- this is the reporting month
) AS c
ON fb.SERVICE_NO = c.service_no
AND fb.subscription_id = c.subscription_id
AND fb.REPORTING_MONTH = '2015-07-01' <-- THIS SHOULD BE THE THIRD MONTH
AND fb.ACTIVE_BASE_IND_NEW = 1
GROUP BY 1
ORDER BY 1


For example if the reporting month is '2015-04-01 I need the variable month to then be '2015-07-01' to be used as part of the query

Answer

You don't specify the database you are using. A typical approach would be:

SELECT REPORTING_MONTH, COUNT(*)
FROM database1 fb JOIN
     database2 c
     ON fb.SERVICE_NO = c.service_no AND
        c.REPORTING_MONTH = '2015-04-01' AND
        fb.subscription_id = c.subscription_id AND
        fb.REPORTING_MONTH = c.reporting_month + interval '3 month' AND
        fb.ACTIVE_BASE_IND_NEW = 1
GROUP BY 1
ORDER BY 1;

The exact syntax for + interval '3 month' varies by database.

Comments