I ve been recently creating few reports that required dates as parameters. It is quite common to have a report that must be provided range of dates (from and to). I investigated few reports that was created by my colleagues and I found those reports are not intuitive. Let me explain:
When I see two dates that must be provided to a report I believe that when I provide the same date in from and to parameters it will show all data for the paricular day. See screenshot below:
But to achieve this you need to create a query in a specific way. Usually in such case both parameters passed to the query are equal so if you want your report to work in an intuitive way you need to write this kind of SELECT:
SELECT
MyDate
FROM
MyTable alias
WHERE
(alias.MyDate >= @DateFrom) AND
(alias.MyDate < DATEADD(d, 1, @DateTo))
Here the MyDate should be greater or equal to DateFrom and less to DateTo (but the DateTo is added with one day).
Maybe it's obvious but I found many reports with the bug. developer usually do not care (or do not know) if the report shows what it should show. Baka (stupid in Japanese) developer.
If you want to provide open range of dates:
- start with infinite end
- end with no start
- no date filtering at all
use the following snippet:
SELECT
MyDate
FROM
MyTable alias
WHERE
(@DateFrom IS NULL OR alias.MyDate >= @DateFrom) AND
(@DateTo IS NULL OR alias.MyDate < DATEADD(d, 1, @DateTo))
This blog entry will be useful at least for my colleagues.