SQL query for specific month

Ignition
1

Can some one help me to figure out the query to retrieve the records for a specific month.

I am using this for a monthly report.

Here is my query for the daily report.

SELECT distinct(Date_Format(CurrentDateTime, ‘%m/%d/%y %H:%i’)) as Hourly_Data, CurrentDateTime, hour(CurrentDateTime)as rptHour, head_level, tail_level, (head_level - tail_level) as net_head_level, U1_mw_hr_mtr, U1_unit_flow AS U1_CFS, U2_mw_hr_mtr, U2_unit_flow AS U2_CFS, U3_mw_hr_mtr, U3_unit_flow AS U3_CFS, U4_mw_hr_mtr, U4_unit_flow AS U4_CFS, volts, pf AS PowerFactor,
(U1_unit_flow + U2_unit_flow + U3_unit_flow + U4_unit_flow) AS Total_CFS
FROM hourly_data
WHERE DATE(CurrentDateTime) = DATE(’{Root Container.Calendar.date}’) group by Hourly_Data

I have a calendar on the Window where the report is and I am using it to select the date of the report.

Thanks in advance.

2

I usually add a couple of dynamic properties.

year:

dateExtract({Root Container.Calendar.date}, "year")

month:

dateExtract({Root Container.Calendar.date}, "month")+1

(The +1 is because the month is zero-indexed, ie January=0, February=1, …)

Then your where clause can be something like:

WHERE MONTH(CurrentDateTime) = '{Root Container.Calendar.month}' AND YEAR(CurrentDateTime) = '{Root Container.Calendar.year}' group by Hourly_Data
3

Okay just had a DOH! moment… :laughing:

Really don’t need the dynamic properties… it was something I did while still learning.

WHERE MONTH(CurrentDateTime) = MONTH('{Root Container.Calendar.date}') AND YEAR(CurrentDateTime) = YEAR('{Root Container.Calendar.date}') group by...