SQL Query to Grafana Time Series

Hey, I’m trying to convert my SQL Query into the Grafana format but I am stuck and would greatly appreciate anyone’s help. I’m trying to convert it into a graph format.

Here’s is my SQL code:

select
CAST(MONTH(B.invoice_date) AS varchar(2)) + ‘-’ + RIGHT(‘0’ + CAST(DAY(B.invoice_date) AS varchar(2)), 2) + ‘-’ + CAST(YEAR(B.invoice_date)AS varchar(4)) AS ‘Invoice Date’,
‘$’+ Format(SUM(B.total_amount), ‘#,00’) as ‘Total Amount’
from
invoice_hdr as B
join oe_pick_ticket as A on A.invoice_no = B.invoice_no
Where
B.invoice_date >= DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE()), 0) and
B.approved = ‘Y’ and
A.location_id = ‘10’
GROUP by
CAST(MONTH(B.invoice_date) AS varchar(2))+ ‘-’ + RIGHT(‘0’ + CAST(DAY(B.invoice_date) AS varchar(2)), 2) + ‘-’ + CAST(YEAR(B.invoice_date)AS varchar(4))

You’re not telling what type of sql database you’re using. If MySQL please read documentation carefully. If other sql datasource you find documentation for those in the left menu on the documentation site. I can spot directly that you’re not using a column named time.

I’m using MySQL.

I updated my code but it is giving me “Incorrect syntax near the keyword ‘as’.”

select
CAST(MONTH(B.invoice_date) AS varchar(2)) + ‘-’ + RIGHT(‘0’ + CAST(DAY(B.invoice_date) AS varchar(2)), 2) + ‘-’ + CAST(YEAR(B.invoice_date)AS varchar(4)) as metric,
__timeEpoch(datestamp) as time_sec, ''+ Format(SUM(B.total_amount), ‘#,00’) as value
from invoice_hdr as B
join oe_pick_ticket as A on A.invoice_no = B.invoice_no
Where
$__timeFilter(B.invoice_date >= DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE()), 0))
and B.approved = ‘Y’
and A.location_id = ‘10’
GROUP by CAST(MONTH(B.invoice_date) AS varchar(2))+ ‘-’ + RIGHT(‘0’ + CAST(DAY(B.invoice_date) AS varchar(2)), 2) + ‘-’ + CAST(YEAR(B.invoice_date)AS varchar(4))

I can only refer you to checkout the MySQL documentation to troubleshoot your syntax issues. I would suggest you first try to get your query working using mysql command line tool or similar management tools without the grafana specific macro functions ($__)

You cannot use $__timeFilter(B.invoice_date >= DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE()), 0)) since __timeFilter doesn't take any arguments - if you need this please use `B.invoice_date >= __timeFrom()` or something instead.

I get a mssql: Incorrect syntax near ‘>’.

I will not help you to write your query - please seek advice in any MySQL support site or similar.