Gettin SUM of a few values

Transact-SQL
1

Hi, I work in Company and I need to get from SQL data a time when the machine is stopped. We work on 3 shifts (6-14,14-22,22-6), on every end of shifts the counter of stopped time is reset, so I only need to get the MAX value in schifts. And this is work fine, but I have problems how to SUM this time from for ex. 2 days?

There is dump for machine number 3 for 1 days (3 shifts)
http://wklej.to/Wntrr

I need the Sum of Max separately for 1, 2 and 3 shift but from all week (from sunday to saturday).

2

what does the meaning of
100_time , 100_num , 200_time , 200_num ?

3

The controller of machine send to database information about how long he was stopped and how many. He`s controller is reset in the end of ship.

100_time - how long machine was stopped in error number 100
100_num - how many machine was stopped in error number 100
200_time - how long machine was stopped in error number 200
200_num - how many machine was stopped in error number 200
etc.

4

Or another, chceck this:
http://wklej.to/9gcTp

I need the SUM of "this 1" like a shifts 1, "this 2" like a shifts 2, "this 3" like a shifts 3.

5

What is the scale of the "_time" columns? Seconds?

6

Yes it`s seconds.
I was write this code:

SELECT Machine, Data, SHIFTS, MAX("100_num") [100num], MAX("100_time")/60 [100time], MAX("200_num") [200num], MAX("200_time")/60 [200time] FROM
(
SELECT Machine, Data=GETDATE()-1, "100_num", "100_time", "200_num", "200_time",
Shifts=CASE
WHEN (DATEPART(HOUR, Data)>=6 AND DATEPART(HOUR, Data)<14) THEN 1
WHEN (DATEPART(HOUR, Data)>=14 AND DATEPART(HOUR, Data)<22) THEN 2
WHEN (DATEPART(HOUR, Data)>=22 OR DATEPART(HOUR, Data)<6) THEN 3
END FROM TABLE
) x
GROUP BY Machine, Shifts, Data
ORDER BY Machine ASC, Shifts ASC

but it dosn`t work good. There is no errors, but it gives me bad results.

7

I have also this code:
SELECT
CAST(Maszyna AS int) AS Maszyna,
sum(Q100_czas) as p100_czas, sum(Q100_ilosc)as p100_ilosc, sum(Q200_czas)/60 as p200_czas, sum(Q200_ilosc)/60 as p200_ilosc, sum(Q230_czas)/60 as p230_czas, sum(Q230_ilosc)as p230_ilosc, sum(Q240_czas)/60 as p240_czas, sum(Q240_ilosc)as p240_ilosc, sum(Q250_czas)/60 as p250_czas, sum(Q250_ilosc)as p250_ilosc, sum(Q300_czas)/60 as p300_czas, sum(Q300_ilosc)as p300_ilosc, sum(Q350_czas)/60 as p350_czas, sum(Q3jEEJZ@eagPosc)as p350_ilosc, sum(Q410_czas)/60 as p410_czas, sum(Q410_ilosc)as p410_ilosc, sum(Q411_czas)/60 as p411_czas, sum(Q411_ilosc)as p411_ilosc, sum(Q412_czas)/60 as p412_czas, sum(Q412_ilosc)as p412_ilosc, sum(Q413_czas)/60 as p413_czas, sum(Q413_ilosc) as p413_ilosc, sum(Q414_czas)/60 as p414_czas, sum(Q414_ilosc)as p414_ilosc, sum(Q415_czas)/60 as p415_czas, sum(Q415_ilosc)as p415_ilosc, sum(Q416_czas)/60 as p416_czas, sum(Q416_ilosc)as p416_ilosc, sum(Q417_czas)/60 as p417_czas, sum(Q417_ilosc)as p417_ilosc, sum(Q418_czas)/60 as p418_czas, sum(Q418_ilosc)as p418_ilosc, sum(Q419_czas)/60 as p419_czas, sum(Q419_ilosc)as p419_ilosc, sum(Q430_czas)/60 as p430_czas, sum(Q430_ilosc)as p430_ilosc, sum(Q510_czas)/60 as p510_czas, sum(Q510_ilosc)as p510_ilosc, sum(Q520_czas)/60 as p520_czas, sum(Q520_ilosc)as p520_ilosc, sum(Q999_czas)/60 as p999_czas, sum(Q999_ilosc)as p999_ilosc
FROM (
SELECT Maszyna, Z100_czas, Z100_ilosc, Z200_czas, Z200_ilosc, Z230_czas, Z230_ilosc, Z240_czas, Z240_ilosc, Z250_czas, Z250_ilosc, Z300_czas, Z300_ilosc, Z350_czas, Z350_ilosc, Z410_czas, Z410_ilosc, Z411_czas, Z411_ilosc, Z412_czas, Z412_ilosc, Z413_czas, Z413_ilosc, Z414_czas, Z414_ilosc, Z415_czas, Z415_ilosc, Z416_czas, Z416_ilosc, Z417_czas, Z417_ilosc, Z418_czas, Z418_ilosc, Z419_czas, Z419_ilosc, Z430_czas, Z430_ilosc, Z510_czas, Z510_ilosc, Z520_czas, Z520_ilosc, Z999_czas, Z999_ilosc
FROM CDNMyC_MaszynyPrzestoje) AS Z
INNER JOIN (
SELECT XMaszyna, Max(XData) AS MaxData, XRok, XMiesiac, XDzien, XZmiana
FROM (
SELECT YMaszyna, Y100_czas, Y100_ilosc, Y200_czas, Y200_ilosc, Y230_czas, Y230_ilosc, Y240_czas, Y240_ilosc, Y250_czas, Y250_ilosc, Y300_czas, Y300_ilosc, Y350_czas, Y350_ilosc, Y410_czas, Y410_ilosc, y411_czas, Y411_ilosc, Y412_czas, Y412_ilosc, Y413_czas, Y413_ilosc, Y414_czas, Y414_ilosc, Y415_czas, Y415_ilosc, Y416_czas, Y416_ilosc, Y417_czas, Y417_ilosc, Y418_czas, Y418_ilosc, Y419_czas, Y419_ilosc, Y430_czas, Y430_ilosc, Y510_czas, Y510_ilosc, Y520_czas, Y520_ilosc, Y999_czas, Y999_ilosc, YData, DATEPART(YYYY, YData) AS Rok, DATEPART(MM, YData) AS Miesiac, DATEPART(DD, YData) AS Dzien,
(CASE WHEN (DATEPART(hh, YData) BETWEEN 0 AND 7) THEN 1
WHEN (DATEPART(hh, YData) BETWEEN 8AND 15) THEN 2
WHEN (DATEPART(hh, YData) BETWEEN 16 AND 23) THEN 3 END) AS Zmiana
FROM( SELECT ID_MyC_MaszynyPrzestoje, Maszyna, 100_czas, 100_ilosc, 200_czas, 200_ilosc, 230_czas, 230_ilosc, 240_czas, 240_ilosc, 250_czas, 250_ilosc, 300_czas, 300_ilosc, 350_czas, 350_ilosc, 410_czas, 410_ilosc, 411_czas, 411_ilosc, 412_czas, 412_ilosc, 413_czas, 413_ilosc, 414_czas, 414_ilosc, 415_czas, 415_ilosc, 416_czas, 416_ilosc, 417_czas, 417_ilosc, 418_czas, 418_ilosc, 419_czas, 419_ilosc, 430_czas, 430_ilosc, 510_czas, 510_ilosc, 520_czas, 520_ilosc, 999_czas, 999_ilosc,
DATEADD(hh, 6, Data) AS Data FROM CDNMyC_MaszynyPrzestoje WHERE Data BETWEEN {ts '2015-12-28 00:00:00'} AND {ts '2015-12-28 23:59:59'})
AS Y ) AS X
GROUP BY XMaszyna, XRok, XMiesiac, XDzien, XZmiana) AS W
ON ZMaszyna=WMaszyna AND
ZData=DATEADD(hh, 6, WMaxData)
) AS Q GROUP BY QMaszyna

but there i Have error:
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'. - this is the last line in the code: ") AS Q GROUP BY QMaszyna"

8

please do use http://poorsql.com/ to format your code before posting.

you missed the joined condition for Q

FROM
(
) AS Z
INNER JOIN
(
) AS Q
ON Z.??? = Q.???
GROUP BY QMaszyna
9

First, you need to clarify what "bad results" means, please.

Also, in the dump you provided (it would have been REALLY nice if those were INSERTs instead of just a dump) and paying particular attention to rows 146 through 149, you have the following data. I just need to be sure before I put fingers to the keyboard...

3 0 0 61 1 2015-12-21 18:25:53.177
3 0 0 61 1 2015-12-21 18:30:53.263
3 0 0 61 1 2015-12-21 18:35:53.363
3 0 0 61 1 2015-12-21 18:40:53.107

Does that mean that the machine was down for 61 seconds during each of those four 5 minute periods for a total down time of 244 seconds for those 4 rows?

And, yes... please start formatting your code. Like @khtan suggested, at least take the time to run it through the link he posted.