SQL - return grouped results

[localzuk]

I have the following table:

 

Transactions

 

ID - int

EntryDate - datetime

Value - double

 

(Not the real table, but it will do for my question).

 

In Report Builder for SSRS I'm trying to create a graph that displays the number of transactions on a day - count on the y axis, and the dates on the x axis.

 

The problem is, to do this, I have to use something like

 

SELECT COUNT(ID) AS Amount, DATEPART(dy,EntryDate) AS TransactionDates

FROM Transactions

GROUP BY DATEPART(dy,EntryDate)

 

Great, if I want to just have days numerically listed on the X axis, but that's not what I want - I want real dates (and then I'll use scaling on the axis in the graph to make it legible).

 

How do I get such a query to group the data I want, and then return real dates instead? I can't just add EntryDate as a SELECT item, as it wouldn't be in an aggregate query or group.

[localzuk]

Anyone got any ideas?

[TechMonkey]

Would you not groupby the day and month as that will then be displayed?

[localzuk]

Would you not groupby the day and month as that will then be displayed?

But I don't want anything grouped by month - I want every day's grouped results as a result, listed by that specific date.

[pleach85]

Hi,

I've done a similar thing in my code before, I've not had time to test it but I think this will work.

 

 

SELECT COUNT(ID) AS Amount, DATEADD(DAY, DATEDIFF(DAY, 0, EntryDate), 0) AS TransactionDates
FROM Transactions
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, EntryDate), 0)

[Geoff]

You almost had it @localzuk

 

SELECT COUNT(*) AS Amount, Convert(date,EntryDate,113) AS TransactionDates
FROM Transactions
GROUP BY Convert(date,EntryDate,113)
Order by Convert(date,EntryDate,113)

[localzuk]

Thanks @Geoff, that works perfectly.