SQL graph fun
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
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.
WHERE DATEPART(hh,DateColumn) BETWEEN 12 AND 14
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)
So Day 1 is Monday? Why not group by the date, a singular figure, and just include the attached day (if needed)
The problem I have is the system uses DateTime columns, rather than Date columns, so I have to work around that.
I understand, yes I think you are on right lines