0

I have a requirement to write a SQL Server Query that will fetch all the records of the table excluding Friday and Saturday of all the weeks and data between 4PM to 8PM of all the days of the week

Ex:

If I have the following records in my table

Name ProcessStartTime Status
Name1 2017-04-01 17:00:02.000 SUCCESS(Saturday)
Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name5 2017-04-05 18:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name7 2017-04-06 23:00:02.000 SUCCESS(Thursday)
Name8 2017-04-07 15:00:02.000 SUCCESS(Friday)

If I execute the Query I should get the Following records only,

Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-06 23:00:02.000 SUCCESS(Thursday)

And the remaining data should not fetched because, those records are either Friday or Saturday as well as date time between 4PM to 8PM(Not only Friday and Saturday but for all the days of the week).

I have my query here below,

SELECT *
  FROM [TABLE]
  WHERE 
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

In this query I'm able to get the records not in Friday and Saturday but not able to neglect between 16 to 20 hours. I have written in a such way that query will fetch records between 00 to 16. but I need another condition to select between 20 to 23 hours. If I do that condition, I'm getting 0 records.

This is the further modified query that showing 0 records,

SELECT *
  FROM [TABLE]
  WHERE 
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND DATEPART(hh,ProcessStartTime) > 20 AND DATEPART(hh,ProcessStartTime) <= 23
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

Please help me on this. If needs more information let me know.

2 Answers 2

3

Assuming that DATEFIRST = 1 (Monday), you can use datepart(dayofweek) to avoid Friday and Saturday and for the time part, I've edited my previous answer due I think you said 8PM, I supposed 08:00:01 must be included in the final result.

declare @events table (Name varchar(20), ProcessStartTime datetime, Status varchar(20));
insert into @events values
('Name1', '2017-04-01 17:00:02', 'SUCCESS'), -- (Saturday)
('Name2', '2017-04-02 21:00:02', 'SUCCESS'), -- (Sunday)
('Name3', '2017-04-03 01:00:02', 'SUCCESS'), -- (Monday)
('Name4', '2017-04-04 05:00:02', 'SUCCESS'), -- (Tuesday)
('Name5', '2017-04-05 18:00:02', 'SUCCESS'), -- (Wednesday)
('Name6', '2017-04-05 15:00:02', 'SUCCESS'), -- (Wednesday)
('Name7', '2017-04-06 23:00:02', 'SUCCESS'), -- (Thursday)
('Name8', '2017-04-07 15:00:02', 'SUCCESS'); -- (Friday)

SET DATEFIRST 1

SELECT Name, ProcessStartTime, Status
FROM   @events
WHERE  datepart(DW, ProcessStartTime) NOT IN (5,6)
AND    (datepart(hh, ProcessStartTime) < 16
       OR    
       ProcessStartTime > DATEADD(dd, DATEDIFF(dd, 0, ProcessStartTime), '08:00:00'))

GO
Name  | ProcessStartTime    | Status 
:---- | :------------------ | :------
Name2 | 02/04/2017 21:00:02 | SUCCESS
Name3 | 03/04/2017 01:00:02 | SUCCESS
Name4 | 04/04/2017 05:00:02 | SUCCESS
Name5 | 05/04/2017 18:00:02 | SUCCESS
Name6 | 05/04/2017 15:00:02 | SUCCESS
Name7 | 06/04/2017 23:00:02 | SUCCESS

dbfiddle here

0
1

In your second query, you want to select records with ProcessStartTime between 00:00 and 16:00 AND between 20:00 and 24:00, and that's obviously impossible (for instance 09:13 is between 00:00 and 16:00 but not between 20:00 and 24:00, so it doesn't match).

You should use OR instead of AND.

Something like:

SELECT *
  FROM [TABLE]
  WHERE 
    (DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    OR DATEPART(hh,ProcessStartTime) > 20 AND DATEPART(hh,ProcessStartTime) <= 23)
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.