ByronOne (9/17/2012)
Clearly this is only a small sample of the data but at present I create a report in SSRS that references this table via a stored procedure. The report shows sales for the past week, current month and year to date. The weekly info is fine as I can identify by the WeekEndDate and the YTD figures are essentially everything but the issue arises with the monthly figures. Currently I use a filter in SSRS to pick out any sales from the current month but of course this is a little inaccurate when half a week I in one month and half a week is in another. Now I know that any mathematical solution is not going to b 100% accurate - given the table this is not possible - but I just wondered whether there was a better solution to the current method. Also the current solution requires the filter on the report to be changed every month. Obit possible for the filter to update automatically by looking at the the latest month date in the table and using that to filter?
I am not so much worried about a big spread of data to work with but I would ask a third time for what you expect the results to be based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/