Survey Report result
I run the following query but only get months 1-8 January Through August but should be get 1-9 with September
SELECT DateTable.YEAR 'Year',
DateTable.MONTH 'Year/Month',
SentTable.SENT_COUNT '# of surveys sent',
ReceivedTable.RECEIVED_COUNT '# of surveys answered',
(SentTable.SENT_COUNT-ReceivedTable.RECEIVED_COUNT) '# of surveys unanswered',
(ReceivedTable.RECEIVED_COUNT*100)/SentTable.SENT_COUNT 'return %'
FROM
(
(
SELECT YEAR(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000)) 'YEAR',
MONTH(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000)) 'MONTH' FROM SurveyPerRequest
LEFT JOIN WorkOrder ON SurveyPerRequest.WORKORDERID = WorkOrder.WORKORDERID
)
UNION
(
SELECT YEAR(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000)) 'YEAR',
MONTH(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000)) 'MONTH' FROM SurveyPerRequest
LEFT JOIN SurveyPerRequestExt ON SurveyPerRequest.SURVEYID = SurveyPerRequestExt.SURVEYID
)
) AS DateTable
LEFT JOIN
(
SELECT YEAR(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000)) 'YEAR',
MONTH(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000)) 'MONTH',
COUNT(*) 'SENT_COUNT' FROM SurveyPerRequest
LEFT JOIN WorkOrder ON SurveyPerRequest.WORKORDERID = WorkOrder.WORKORDERID
GROUP BY (YEAR(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000))) , (MONTH(FROM_UNIXTIME(WorkOrder.COMPLETEDTIME/1000)))
) AS SentTable ON DateTable.YEAR = SentTable.YEAR AND DateTable.MONTH = SentTable.MONTH
LEFT JOIN
(
SELECT YEAR(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000)) 'YEAR',
MONTH(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000)) 'MONTH',
COUNT(*) 'RECEIVED_COUNT' FROM SurveyPerRequest
LEFT JOIN SurveyPerRequestExt ON SurveyPerRequest.SURVEYID = SurveyPerRequestExt.SURVEYID
GROUP BY (YEAR(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000))), (MONTH(FROM_UNIXTIME(SurveyPerRequestExt.CREATEDTIME/1000)))
) AS ReceivedTable ON DateTable.YEAR = ReceivedTable.YEAR AND DateTable.MONTH = ReceivedTable.MONTH
WHERE DateTable.MONTH!=0 AND DateTable.YEAR!=0 AND DateTable.YEAR=2018 ORDER BY 1,2