Styjun

I have a table holding email data in particular the date and time they were sent. I need to extract the records that were sent after a given time each day (ie after close of business). The problem is that the close of business time each day can be different. Currently we just use after 6pm, but some businesses may finish early on Friday compared to the rest of the week.

I can do individual single queries along the lines of:

SELECT DataExtractTableID FROM DataExtractTable WHERE (Box = 'Sent Items') AND
(Owner = @EmailAddress) AND 
(DateSent BETWEEN @StartDate AND @EndDate)
AND (CAST(DateSent as time) >= CAST(@MondayCloseOfBusiness as time));

Then repeat this for each day of the week and then dump all the DataExtractTableIDs into a single temporary table to get my total list of DataExtractTableIDs that relate to emails set after the individual times each day.

This all seems a bit long winded and I wondered if there is a cleverer solution that can be done in a single query or SP?

There are two options here.

1. Add a table (it can also be a temporary table / table variable) for coupling weekday and business hours, then join to this table on weekday:

SET DATEFIRST 7;
DECLARE @WorkingHours AS TABLE
(
 WeekDay int,
 OpenTime Time,
 CloseTime Time
);

INSERT INTO @WorkingHours(WeekDay, OpenTime, CloseTime) VALUES
(1, '08:00:00', '18:00:00'),
(2, '08:30:00', '18:30:00'),
(3, '09:00:00', '19:00:00'),
(4, '08:00:00', '18:00:00'),
(5, '08:00:00', '18:00:00'),
(6, '08:00:00', '12:00:00');

SELECT DataExtractTableID 
FROM DataExtractTable 
JOIN @WorkingHours AS WH
 ON DATEPART(WEEKDAY, DataExtractTable.DateSent) = WH.WeekDay
WHERE (Box = 'Sent Items') 
AND Owner = @EmailAddress 
AND DateSent BETWEEN @StartDate AND @EndDate
AND CAST(DateSent as time) >= WH.CloseTime;

2. Use a case expression instead:

SET DATEFIRST 7;
DECLARE @SundayClosingTime Time = '16:00:00',
@MondayClosingTime Time = '12:00:00',
@RegularClosingTime Time = '18:00:00';

SELECT DataExtractTableID 
FROM DataExtractTable 
WHERE Box = 'Sent Items'
AND Owner = @EmailAddress 
AND DateSent BETWEEN @StartDate AND @EndDate
AND CAST(DateSent as time) >= CASE DATEPART(WEEKDAY, DateSent)
 WHEN 1 THEN @SundayClosingTime 
 WHEN 2 THEN @MondayClosingTime 
 ELSE @RegularClosingTime
 END;

Please note that DatePart output for weekday depends on regional settings - specifically, the first day of the week which can be configured by SET DATEFIRST.