#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM leads WHERE ProgramID = ‘1’ AND TimeStamp >= ‘2013-01-01 00:00:00’ AND `T’ at line 1
This is the query i am using:
SELECT ProgramName AS `name`, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads`, FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `TimeStamp` < '2013-01-31 23:59:99' GROUP BY ProgramName
I think i know why im getting the error, its because i have the field TimeStamp wrapped in ’ ’ but if i remove them i still get the same error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM leads WHERE ProgramID = ‘1’ AND TimeStamp >= ‘2013-01-01 00:00:00’ AND Time’ at line 1
SELECT ProgramName AS `name`, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads` FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `TimeStamp` < '2013-01-31 23:59:99' GROUP BY ProgramName
this error would’ve been a lot easier to spot if you had been using the leading comma convention, along with suitable formatting…
SELECT ProgramName AS `name`
, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`
, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`
, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads`
, [COLOR="#FF0000"]-- a lot easier to see this, eh?[/COLOR]
FROM leads
WHERE ProgramID = '1'
AND `TimeStamp` >= '2013-01-01 00:00:00'
AND `TimeStamp` < '2013-01-31 23:59:99'
GROUP
BY ProgramName
do a search (on “leading comma”) in this forum for other examples of people who have had exactly the same problem
FYI, you’re on the right track with your datetime range tests, but you’re (incorrectly) omitting a small portion of the 31st
do it this way instead –
AND `TimeStamp` >= '2013-01-01'
AND `TimeStamp` < '2013-02-01'
this technique works correctly for both DATE and DATETIME columns, with the added bonus that you never have to figure out leap years