Use Datepart as Query Parameter

Navigation
2

I'm developing a system and am bumping into a SQL query that consists of the user searching by Date and will have the option to choose whether the query will be done by adding Weekend to those 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 that it does not 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 have already tried within Query, with Join, with Union, Having, creating an auxiliary Bench to compare and nothing. Could someone explain to me how this could be done?

Below is the code for 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'

In the Case, I made this test query to return the DatePart from days 03 to 05. It returned 6 for the occurrences of Day 03 (Friday), 7 for those of Day 4 (Saturday) and 1 for Sunday, the problem is that the final query will be an average of these occurrences and the total number of hours it has been out. And in this, the day of the week is not fitting.

Below is the final query that I need to fit into the Weekend Choice. 

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 solve this? Is there a procedure or is there any other way?

    
asked by anonymous 22.11.2017 / 03:27

1 answer

1

As you want to search using the days of the week, you can treat DATEPART as an integer.

In the case of searching 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 the 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)))
    
22.11.2017 / 06:04
Do import another detrous module from another using LibreOffice macros Add data in a single Json