How to make this query in sql server 2014?

Question

How to make this query in sql server 2014?

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I have a problem when trying to make a query, I explain what I have:

I have a schedule of X device, to calculate the hours of operation against the hours that have failures, what I need is a query to totalize the hours of operation per month. I mean, I have a table that I have running hours Monday-Friday, Saturdays, and Sundays. For example: X appliance operates Monday through Friday from 7:00 AM to 8:00 PM, Saturdays from 8:00 AM to 8:00 PM and Sundays from 8:00 AM to 12:00 PM. So the idea is to take out how many hours should work per month, but I'm stuck in the query, I would like to be able to cover the whole month, that is, add up the hours worked per month.

The TIME tables is this:

CREATE TABLE [Horarios](
    [COD_ATM] [int] NOT NULL,
    [LV_INI] [datetime] NULL,
    [LV_FIN] [datetime] NULL,
    [SA_INI] [datetime] NULL,
    [SA_FIN] [datetime] NULL,
    [DO_INI] [datetime] NULL,
    [DO_FIN] [datetime] NULL)

LV_INI = Monday To Friday Start to work LV_FIN = Monday to Friday Stop working (so do the others too) Some data:

COD_ATM LV_INI               LV_FIN                 SA_INI             
                             SA_FIN                 DO_INI         DO_FIN
1   2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000
2   2014-06-09 08:00:00.000 2014-06-09 16:00:00.000 2014-06-09 08:00:00.000 2014-06-09 12:00:00.000 2014-06-09 00:00:00.000 2014-06-09 00:00:00.000
3   2014-06-09 00:00:00.000 2014-06-09 23:59:00.000 2014-06-09 00:00:00.000 2014-06-09 23:59:00.000 2014-06-09 00:00:00.000 2014-06-09 23:59:00.000
4   2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000
5   2014-06-09 08:00:00.000 2014-06-09 20:00:00.000 2014-06-09 08:00:00.000 2014-06-09 20:00:00.000 2014-06-09 08:00:00.000 2014-06-09 20:00:00.000
6   2014-06-09 07:00:00.000 2014-06-09 21:00:00.000 2014-06-09 07:00:00.000 2014-06-09 21:00:00.000 2014-06-09 07:00:00.000 2014-06-09 21:00:00.000

And the query I have is this:

    /****** Promedio de horas ******/
    SELECT COD_ATM,
    sum(convert(int, DATEDIFF(HOUR, LV_INI, LV_FIN))*5) as LunesAViernes,
    sum(convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN))) as Sabado,
    sum(convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN))) as Domingo,
    sum((convert(int, (DATEDIFF(HOUR, LV_INI, LV_FIN))*5) + convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) + convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)))) AS TOTAL,
    sum((convert(int, (DATEDIFF(HOUR, LV_INI, LV_FIN))*5) + convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) + convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)))*4) AS TOTAL
FROM [ATMs].[dbo].[AT1203]
group by COD_ATM, LV_INI, LV_FIN

Then in this query I calculate them assuming that the month has 4 weeks. What I would like to do is add up according to the month I'm in.

And what I get is this: It approaches the desired data but it is not what I want

COD_ATM LunesAViernes   Sabado  Domingo TOTAL   TOTAL
1         65               13       13    91    364
2         40               4        0     44    176
3        115              23        23    161   644
4         65              13        13     91   364
5         60              12        12     84   336
6         70              14        14     98   392

I hope you have given me to understand, any doubt is pending. I clarify that the date fields are not necessary, what I occupy of those fields is the start time and the end time respectively.

An expected example would be the following for ATM 1, September has 5 Saturdays, 4 Sundays, and 21 days a week. Then the ATM1 works from Monday to Friday from 7AM to 8PM, which means that it works 13 hours a day, if we multiply it by 21 we obtain the hours that work per week (Monday to Friday) which would be 273 hours a week and They are 4 Sundays (4 Sundays * 13) = 52 same on Saturday = 52

Entoces el total trabajado seria:
273 los dias de la semana
52 los sabados
52 los domingos 
**377 Total**. Ese seria el resultado esperado, **y en la consulta que tengo solo me salen 364**

sql-server query

asked by Luis Fernando 27.09.2017 в 19:10

source

2 answers

0

Finally it's like this:

SELECT  COD_ATM,
    sum(convert(int, DATEDIFF(HOUR, LV_INI, LV_FIN)) * B.LaV) as LunesAViernes,
    sum(convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) * B.Sab) as Sabado,
    sum(convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)) * B.Dom) as Domingo,
    sum((convert(int, datediff(hour, LV_INI, LV_FIN)) * B.Lav) + (convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) * B.Sab) + (convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)) * B.Dom)) as Total_Horas_Trabajadas
    FROM [ATMs].[dbo].[AT1203] A
    CROSS JOIN (SELECT DATEDIFF(DAY, -7, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -6, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 +
            DATEDIFF(DAY, -6, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -5, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 +
            DATEDIFF(DAY, -5, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -4, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 +
            DATEDIFF(DAY, -4, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -3, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 +
            DATEDIFF(DAY, -3, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -2, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 AS 'LaV',
            DATEDIFF(DAY, -2, EOMONTH (GETDATE()))/7-DATEDIFF(DAY, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 AS 'Sab',
            DATEDIFF(DAY, -1, EOMONTH (GETDATE()))/7-DATEDIFF(DAY,  0, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))/7 AS 'Dom'
        ) B
    GROUP BY COD_ATM, LV_INI, LV_FIN

Staying like this:

1   273    65      52   390
2   168    20      0    188
3   483    115     92   690
4   273    65      52   390
5   252    60      48   360
6   294    70      56   420
7   252    60      48   360

answered by 27.09.2017 в 23:27

Take out the percentage between 2 amounts in different jQuery elements I do not clarify with the loops for in Python

score 2 · Accepted Answer

I imagine that you will start from a range of dates to list, the following is the "easy" way to solve your problem, which basically involves calculating which day of the week is each date between the ranges requested. Let's see what it would be like:

SET DATEFIRST 1

DECLARE @FechaDesde     DATETIME
DECLARE @FechaHasta     DATETIME

SELECT  @FechaDesde     = '20170113'
SELECT  @FechaHasta     = '20170521'

First of all SET DATEFIRST 1 to make sure that all calculations are based on our first day of the week being a Monday. Then we set the dates to list.

-- Horas semanales de cada ATM
DECLARE @HorasATMSemana TABLE (
    COD_ATM     INT, 
    DiaSemana   INT,
    Horas       INT
)

INSERT INTO @HorasATMSemana (COD_ATM, Diasemana, Horas)
VALUES  (1, 1, 13), (1, 2, 13), (1, 3, 13), (1, 4, 13), (1, 5, 13), (1, 6, 12), (1, 7, 16),
    (2, 1,  8), (2, 2,  8), (2, 3,  8), (2, 4,  8), (2, 5,  8), (2, 6,  8), (2, 7,  8)

The following is a reformulation of your table Horarios , instead of having start and end dates, I have the ATM, the day of the week and the hours that correspond to that day, according to your example 13 of Monday to Friday, 12 on Saturdays and 16 on Sundays (add another ATM with another regime as a test). All this simply to make it a little easier and easier to understand, but you could calmly use your table Horarios and generate a structure similar to this. Then, the important thing is to generate our set of Dates from @FechaDesde to @FechaHasta and on each date we calculate that day of the week is.

-- Generamos las fechas (1000 dias)
DECLARE @Fechas TABLE (
    Nro         INT,
    DiaSemana   INT,
    Fecha   DATETIME    
)

INSERT INTO @Fechas(Nro)
SELECT  (T1.NRO-1)*100 + (T2.NRO-1)*10 + T3.NRO
    FROM ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T1 
    CROSS JOIN ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T2
    CROSS JOIN ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T3
    ORDER BY T1.NRO,T2.NRO,T3.NRO

UPDATE  @Fechas
    SET DiaSemana = DATEPART(dw, DATEADD(DAY, Nro-1, @FechaDesde)),
    Fecha =  DATEADD(DAY, Nro-1, @FechaDesde)

There are several methods to generate sequences in SQL, in this example we use a Cartesian product of 3 queries of 10 values which will give us a total of 1000 records, that will be our maximum range of days, if we need more we have to modify this consult.

Finally we make the final query:

SELECT  D.COD_ATM,
    SUM(CASE WHEN D.DiaSemana BETWEEN 1 AND 5 THEN D.Horas ELSE 0 END) AS 'LaV',
    SUM(CASE WHEN D.DiaSemana = 6             THEN D.Horas ELSE 0 END) AS 'Sab',
    SUM(CASE WHEN D.DiaSemana = 7             THEN D.Horas ELSE 0 END) AS 'Dom',
    SUM(D.Horas)                                                       AS 'Tot'
    FROM @Fechas F
    INNER JOIN @HorasATMSemana D
        ON D.DiaSemana = F.DiaSemana 
    WHERE F.Fecha BETWEEN @FechaDesde AND @FechaHasta
    GROUP BY D.COD_ATM

That we would return something like this

COD_ATM LaV      Sab     Dom     Tot
------- -------- ------- ------- --------
1,00    1.183,00 228,00  304,00  1.715,00
2,00    728,00   152,00  152,00  1.032,00

Before I told you that this was the "easy" way, because if I remember correctly there is a more direct formula to calculate how many working days, Saturdays and Sundays are in an interval, knowing these numbers is simply a matter of multiplying the hours and add them. If I find this other solution I add it to the answer.

Edited: As I said the other way is calculating how many days of the week there are in a range of dates ( This question helped a lot , is simpler and does not require an intermediate table.

SELECT  D.COD_ATM,
    SUM(CASE WHEN D.DiaSemana BETWEEN 1 AND 5 THEN D.Horas * F.Cant ELSE 0 END) AS 'LaV',
    SUM(CASE WHEN D.DiaSemana = 6             THEN D.Horas * F.Cant ELSE 0 END) AS 'Sab',
    SUM(CASE WHEN D.DiaSemana = 7             THEN D.Horas * F.Cant ELSE 0 END) AS 'Dom',
    SUM(D.Horas*F.Cant)                                                         AS 'Tot'
    FROM @HorasATMSemana D
    INNER JOIN  (
        SELECT  1 AS Diasemana, DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 AS Cant UNION
        SELECT  2 AS Diasemana, DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 AS Cant UNION
        SELECT  3 AS Diasemana, DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 AS Cant UNION
        SELECT  4 AS Diasemana, DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 AS Cant UNION
        SELECT  5 AS Diasemana, DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS Cant UNION
        SELECT  6 AS Diasemana, DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS Cant UNION
        SELECT  7 AS Diasemana, DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY,  0, @FechaDesde)/7 AS Cant
    ) F
    ON F.Diasemana = D.Diasemana
    GROUP BY D.COD_ATM

Finally, for your example you need the days Monday to Friday, Saturdays and Sundays, you could do a join directly with this table and get how many days there are of each one in the month

SET DATEFIRST 1

DECLARE @FechaDesde     DATETIME
DECLARE @FechaHasta     DATETIME

SELECT  @FechaDesde     = '20170901'
SELECT  @FechaHasta     = '20170930'

SELECT  DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 +
    DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 +
    DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 +
    DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 +
    DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS 'LaV',
    DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS 'Sab',
    DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY,  0, @FechaDesde)/7 AS 'Dom'

LaV     Sab     Dom
------- ------- -------
21,00   5,00    4,00

Finally you could have something like this:

SELECT  COD_ATM,
    sum(convert(int, DATEDIFF(HOUR, LV_INI, LV_FIN)) * B.LaV) as LunesAViernes,
    sum(convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) * B.Sab) as Sabado,
    sum(convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)) * B.Dom) as Domingo
    FROM [ATMs].[dbo].[AT1203] A
    CROSS JOIN (SELECT DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 +
            DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 +
            DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 +
            DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 +
            DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS 'LaV',
            DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS 'Sab',
            DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY,  0, @FechaDesde)/7 AS 'Dom'
        ) B
    GROUP BY COD_ATM, LV_INI, LV_FIN