SOLDIER-OF-FORTUNE SOLDIER-OF-FORTUNE - 2 months ago 8
SQL Question

Filter based on multiple date ranges?

The user clicks on a month and then this stored procedure is executed. It checks for the total booked time and what groups have been filtered.

| Job Group | Month Booked | Time (hrs) |
Cleaning Jan 7


I have the following SQL:

SELECT
tsks.grouping_ref, ttg.description AS grouping_desc,
SUM(ts.booked_time) AS booked_time_total,
DATENAME(MONTH, ts.start_dtm) + ' ' + DATENAME(YEAR, ts.start_dtm) AS month_name,
@month_ref AS month_ref
FROM
timesheets ts
JOIN
timesheet_categories cat ON ts.timesheet_cat_ref = cat.timesheet_cat_ref
JOIN
timesheet_tasks tsks ON ts.task_ref = tsks.task_ref
JOIN
timesheet_task_groupings ttg ON tsks.grouping_ref = ttg.grouping_ref
WHERE
ts.status IN(1, 2) --Booked and approved
AND cat.is_leave_category = 0 --Ignore leave
AND DATEPART(YEAR, ts.start_dtm) = @Year
AND DATEPART(MONTH, ts.start_dtm) = @Month
GROUP BY
tsks.grouping_ref, ttg.description,
DATENAME(MONTH, ts.start_dtm),
DATENAME(YEAR, ts.start_dtm)
ORDER BY
grouping_desc


I want to filter based on multiple date ranges.

I thought about adding this:

AND ((ts.start_dtm BETWEEN '2011-12-28' AND '2012-01-01')
OR (ts.start_dtm BETWEEN '2012-01-02' AND '2012-01-29'))


But then realized it wouldn't matter what month the user clicked it would still show all the records as it will carry out the OR statement.

What I need is something that's based on the month_ref, eg:

CASE WHEN @month_ref = 81201 THEN
AND (ts.start_dtm BETWEEN '2011-12-28' AND '2012-01-01')
END


But the case statement needs to go just after the WHERE clause.

I have about 12 accounting months for 2012 which I need to add as case statements so that when the user clicks on March, it will fire the correct filter.

In the database
ts.start_dtm
looks like this:

2011-04-01 00:00:00.000


Hope that was enough information for my first post?

I'm stuck writing the case statement and where to put it, been trying for hours now.

Hope you can help :)

Answer

Give the irregular nature of your dates would preclude using dateparts; I would build a temporary table of the permissible dates based on the user query and join on it. The static integers table in my app has 1 through 64000 your tables may vary.

DECLARE 
    @startdate DateTime  = '2012-05-01',
    @EndDate DateTime = '2012-06-03'

DECLARE
    @AllDates TABLE (MyDate DateTime)

INSERT INTO @AllDates
SELECT 
    DATEADD(dd, StaticInteger, @startdate)
FROM dbo.tblStaticIntegers
WHERE StaticInteger <= DATEDIFF(dd, @startdate, @EndDate)