SQL graph fun

[localzuk]

I am trying to pull out data from our till system as follows:

 

Number of sales between 12pm and 2pm, grouped by the day they happened.

 

I then want to use this to display on a graph in Microsoft Reporting Services, so the results need to be in pairs - the number, and the date in question.

 

Now, my question is, how do I do this grouping? I can do the 'between' part I think

 

WHERE DATEPART(hh,DateColumn) BETWEEN 12 AND 14

 

But the grouping is being a little more elusive, as there will be more than one Day '1' and I don't want these stuck together too.

[localzuk]

Ok, answering this myself, I can do it using the DATEPART(dy, DateColumn) instead, and then limit it down to a specific date range (eg. year)

[GREED]

So Day 1 is Monday? Why not group by the date, a singular figure, and just include the attached day (if needed)

[GREED]

Or that

[localzuk]

The problem I have is the system uses DateTime columns, rather than Date columns, so I have to work around that.

[GREED]

I understand, yes I think you are on right lines