Use Datepart as Query parameter

Question

I’m developing a system and I’m bumping into an SQL query that consists of the user searching for Date and will have the option to choose if the query will be made adding Weekend to these dates, excluding the weekend or just consulting the Weekend.

In this case, I have a View that will be used for this application. The problem is she doesn’t have a specific field for the day of the week, so I had to do a Datepart.

The problem is that when I use with other elements of the query, the error in it.

I already tried inside the Query, with Join, with Union, with Having, creating an auxiliary Bank to compare and nothing. Could someone explain to me how this could be done?

Below is the code of the two tables.

SELECT [DT_ABERT_OPER], DATEPART(weekday, [DT_ABERT_OPER]) AS 
DIA_DA_SEMANA
FROM [dbo].[reavw001_ocorrencias_xxxxx] WHERE [DT_ABERT_OPER] 
BETWEEN '2017-11-03' AND '2017-11-05' 

No Case, I made this test query to return the Datepart from days 03 to 05. Returned 6 for the occurrences of the day 03(Friday), 7 for the day 4(Saturday) and 1 for Sunday, the problem is that the final consultation will be an average of these occurrences and the total of hours she was out. And in that, the day of the week is not fitting.

Below is the final query that I need to fit the choice of Weekend.

SELECT GITEC,   SUM (DATEDIFF (minute,INICIO,DH_ULTIMA_CATEGORIZACAO)) 
as Soma_tempo, 
AVG (DATEDIFF (minute,INICIO,DH_ULTIMA_CATEGORIZACAO)) as Media_tempo 
FROM [readb004].[dbo].[reavw001_ocorrencias_xxxxxx] WHERE 
([DT_ABERT_OPER]
BETWEEN '2017-11-03' AND '2017-11-06 23:59:59') GROUP BY GITEC order 
by GITEC DESC  

What can be done to fix this? A trial or some other way?

Answer 1

How you want to search using the days of the week, can treat the DATEPART as a whole.

In case of search for days of the week:

SELECT *
  FROM view v
 WHERE DATEPART(WEEKDAY, v.data) NOT IN (7, 1) -- Qualquer dia que não seja Sábado ou Domingo

If you only want weekends:

SELECT *
  FROM view v
 WHERE DATEPART(WEEKDAY, v.data) IN (7, 1) -- Qualquer dia que seja Sábado ou Domingo

If you want to work with variables:

DECLARE @opcao INT;

SET @opcao = 1; -- Use 1 para dias da semana, 2 para fins de semana e 3 para todos os dias

SELECT *
FROM view v
WHERE @opcao = 3
  OR (@opcao = 1 AND DATEPART(WEEKDAY, v.data) NOT IN (7, 1))
  OR (@opcao = 2 AND DATEPART(WEEKDAY, v.data) IN (7, 1))

If you want to add the date range in the above query:

DECLARE @opcao INT;

SET @opcao = 1; -- Use 1 para dias da semana, 2 para fins de semana e 3 para todos os dias

SELECT *
FROM view v
WHERE v.data BETWEEN '2017-11-03' AND '2017-11-06 23:59:59'
 AND (@opcao = 3
  OR (@opcao = 1 AND DATEPART(WEEKDAY, v.data) NOT IN (7, 1))
  OR (@opcao = 2 AND DATEPART(WEEKDAY, v.data) IN (7, 1)))