• clai_shock008 - Thursday, March 8, 2018 2:07 AM

    Hi I need some fix here to get the proper result for grouping dates in my BAR chart. I need to group them according to dates, not in date and time.
    I used this query in mysql and it works, however applying to SSRS Query Builder it returns an sql error.

               Error[07002][MySQL][ODBC 3.51 Driver][mysql-5.0.45-community-nt]SQLBindParameter not used for all parameters 

    SELECT COUNT(*) AS total_exam, DATE(labcollect) AS collecteddate 
    FROM labinpat 
    WHERE (labcollect BETWEEN ? AND ?) 
    GROUP BY DATE(labcollect) 

    BUT this one works fine but doesn't get the result I wanted: 

    SELECT COUNT(*) AS total_exam, labcollect AS collecteddate 
    FROM labinpat 
    WHERE (labcollect BETWEEN ? AND ?) 
    GROUP BY labcollect 

    I tried to change the query to this:
    SELECT   COUNT(*) AS total_exam, CONVERT(labcollect, DATETIME) AS collecteddate
    FROM    labinpat
    WHERE   (labcollect BETWEEN ? AND ?)
    GROUP BY CONVERT(labcollect, DATETIME)

    but still result is the same in the screenshot.

    i want the 12/25/2017 dates be merged as one group using my query

    I'm not hugely familiar with MySQL and I only rarely use Query Builder in SSRS but the problem is possibly: 
    GROUP BY CONVERT(labcollect, DATETIME)
    which is not T-SQL syntax.  
    In T-SQL you'll need to use
    CAST(labcollect AS DATE)
    in your SELECT list and GROUP BY clause to remove the time component.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537