locked
SSRS and datetime sorting RRS feed

  • Question

  • Hello all,

    I'm trying to create a new report based on the number of Leads that were qualified, so I would like to create a table as follows:

    Jan: 2344

    Feb: 3245

    Mar: 3276

    Apr: 9083

    How can I do this using SQL? I am going by createdon date.

    Thank you.

    Tuesday, November 25, 2014 1:47 PM

Answers

  • This should work:

    SELECT YEAR(createdon ) [Year], MONTH(createdon ) [Month], 
     DATENAME(MONTH,createdon ) [Month Name], COUNT(1) [Sales Count]
    FROM SHK2014_MSCRM.dbo.FilteredLeads
    WHERE statecode = 0
    GROUP BY YEAR(createdon ), MONTH(createdon ), 
     DATENAME(MONTH, createdon )
    ORDER BY 1,2

    • Marked as answer by StephenBB Tuesday, November 25, 2014 5:52 PM
    Tuesday, November 25, 2014 4:57 PM

All replies

  • This should work:

    SELECT YEAR(createdon ) [Year], MONTH(createdon ) [Month], 
     DATENAME(MONTH,createdon ) [Month Name], COUNT(1) [Sales Count]
    FROM SHK2014_MSCRM.dbo.FilteredLeads
    WHERE statecode = 0
    GROUP BY YEAR(createdon ), MONTH(createdon ), 
     DATENAME(MONTH, createdon )
    ORDER BY 1,2

    • Marked as answer by StephenBB Tuesday, November 25, 2014 5:52 PM
    Tuesday, November 25, 2014 4:57 PM
  • Thanks - I got rid of the Sales Count but that worked.
    Tuesday, November 25, 2014 5:52 PM