Sum of amount per field O.S.s

Question

Sum of amount per field O.S.s

pinheiroinfo

SSC Veteran

Points: 290
More actions
June 7, 2016 at 12:08 pm

#314366

Good afternoon,
I have a SQL code where I can score with 1,2 and 3 the amount of time between the O.S.s , but would like to add the amount of O.S.s in their respective fields . Quantity fields 1hour , 2hours and Exceeding 2 hours . Who can help me? Thank you very much in advance.
The code is this:
SELECT
ORDSERV.TAG AS'OS',
ORDSERV.DATPRO2,
ORDSERV.MAQPAR,
TIPMANUT.DESCRICAO,
SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 'H_ATEND',
(CASE
WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 < 60 THEN 1 ELSE 0 END)'1HORA',
(CASE WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 > 60 AND
SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 < 120 THEN 2 ELSE 0 END)'2HORAS',
(CASE WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 > 120 THEN 3
ELSE 0 END) 'SUPERIOR_2HORAS'
FROM ORDSERV
INNER JOIN TIPMANUT ON ORDSERV.CODEMP_2=TIPMANUT.CODEMP AND ORDSERV.CODTIPMAN=TIPMANUT.CODTIPMAN
WHERE ORDSERV.DATPRO2 BETWEEN '12/05/2016' and '23/05/2016'
AND ORDSERV.CODEMP=76
AND TIPMANUT.TAG IN ('CORR','CHAM')
GROUP BY ORDSERV.TAG,ORDSERV.DATPRO2,ORDSERV.MAQPAR,TIPMANUT.DESCRICAO
ORDER BY 1,2
Result:
OS DATPRO2 MAQPAR DESCRICAO H_ATEND 1HORA 2HORAS SUPERIOR_2HORAS
------- ---------- ------------------- --------- ---------- ----- ------ ---------------
0001887 12/05/2016 12/05/2016 14:30:00 CORRETIVA 5,663333 1 0 0
0001888 12/05/2016 25/05/2016 11:00:00 CORRETIVA 314,086666 0 0 3
0001889 12/05/2016 12/05/2016 12:00:00 CORRETIVA 2,296666 1 0 0
0001890 12/05/2016 16/05/2016 08:00:00 CORRETIVA 87,898611 0 2 0
0001891 12/05/2016 18/05/2016 16:00:00 CORRETIVA 143,879166 0 0 3
0001892 13/05/2016 13/05/2016 09:00:00 CORRETIVA 0,609722 1 0 0
0001893 13/05/2016 04/06/2016 08:00:00 CORRETIVA 527,594444 0 0 3
0001894 13/05/2016 13/05/2016 07:53:00 CORRETIVA -0,539166 1 0 0
0001895 13/05/2016 16/05/2016 15:40:00 CORRETIVA 79,226388 0 2 0
0001896 13/05/2016 23/05/2016 16:38:00 CORRETIVA 248,162222 0 0 3
0001897 13/05/2016 13/05/2016 08:02:00 CORRETIVA -0,452222 1 0 0
0001898 13/05/2016 13/05/2016 08:40:00 CORRETIVA 0,115 1 0 0
0001899 13/05/2016 12/05/2016 16:00:00 CORRETIVA -16,570833 1 0 0
0001900 13/05/2016 13/05/2016 12:00:00 CORRETIVA -2,959444 1 0 0
0001901 13/05/2016 13/05/2016 16:48:00 CORRETIVA 1,815277 1 0 0
0001902 13/05/2016 14/05/2016 10:30:00 CORRETIVA 18,34 1 0 0
0001903 16/05/2016 17/05/2016 16:00:00 CORRETIVA 28,401666 1 0 0

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

pinheiroinfo (6/7/2016)
Good afternoon,
I have a SQL code where I can score with 1,2 and 3 the amount of time between the O.S.s , but would like to add the amount of O.S.s in their respective fields . Quantity fields 1hour , 2hours and Exceeding 2 hours . Who can help me? Thank you very much in advance.
The code is this:
SELECT
ORDSERV.TAG AS'OS',
ORDSERV.DATPRO2,
ORDSERV.MAQPAR,
TIPMANUT.DESCRICAO,
SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 'H_ATEND',
(CASE
WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 < 60 THEN 1 ELSE 0 END)'1HORA',
(CASE WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 > 60 AND
SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 < 120 THEN 2 ELSE 0 END)'2HORAS',
(CASE WHEN SUM(DATEDIFF(SS,(CASE WHEN ORDSERV.DATPRO < ORDSERV.MAQPAR
THEN DATPRO
ELSE ORDSERV.DATPRO
END),
(CASE WHEN ORDSERV.DATPRO > ORDSERV.MAQPAR
THEN MAQPAR
ELSE ORDSERV.MAQPAR
END)))/3600.0 > 120 THEN 3
ELSE 0 END) 'SUPERIOR_2HORAS'
FROM ORDSERV
INNER JOIN TIPMANUT ON ORDSERV.CODEMP_2=TIPMANUT.CODEMP AND ORDSERV.CODTIPMAN=TIPMANUT.CODTIPMAN
WHERE ORDSERV.DATPRO2 BETWEEN '12/05/2016' and '23/05/2016'
AND ORDSERV.CODEMP=76
AND TIPMANUT.TAG IN ('CORR','CHAM')
GROUP BY ORDSERV.TAG,ORDSERV.DATPRO2,ORDSERV.MAQPAR,TIPMANUT.DESCRICAO
ORDER BY 1,2
Result:
OS DATPRO2 MAQPAR DESCRICAO H_ATEND 1HORA 2HORAS SUPERIOR_2HORAS
------- ---------- ------------------- --------- ---------- ----- ------ ---------------
0001887 12/05/2016 12/05/2016 14:30:00 CORRETIVA 5,663333 1 0 0
0001888 12/05/2016 25/05/2016 11:00:00 CORRETIVA 314,086666 0 0 3
0001889 12/05/2016 12/05/2016 12:00:00 CORRETIVA 2,296666 1 0 0
0001890 12/05/2016 16/05/2016 08:00:00 CORRETIVA 87,898611 0 2 0
0001891 12/05/2016 18/05/2016 16:00:00 CORRETIVA 143,879166 0 0 3
0001892 13/05/2016 13/05/2016 09:00:00 CORRETIVA 0,609722 1 0 0
0001893 13/05/2016 04/06/2016 08:00:00 CORRETIVA 527,594444 0 0 3
0001894 13/05/2016 13/05/2016 07:53:00 CORRETIVA -0,539166 1 0 0
0001895 13/05/2016 16/05/2016 15:40:00 CORRETIVA 79,226388 0 2 0
0001896 13/05/2016 23/05/2016 16:38:00 CORRETIVA 248,162222 0 0 3
0001897 13/05/2016 13/05/2016 08:02:00 CORRETIVA -0,452222 1 0 0
0001898 13/05/2016 13/05/2016 08:40:00 CORRETIVA 0,115 1 0 0
0001899 13/05/2016 12/05/2016 16:00:00 CORRETIVA -16,570833 1 0 0
0001900 13/05/2016 13/05/2016 12:00:00 CORRETIVA -2,959444 1 0 0
0001901 13/05/2016 13/05/2016 16:48:00 CORRETIVA 1,815277 1 0 0
0001902 13/05/2016 14/05/2016 10:30:00 CORRETIVA 18,34 1 0 0
0001903 16/05/2016 17/05/2016 16:00:00 CORRETIVA 28,401666 1 0 0

Please post create table and insert statements to create sample data and get to your desired results. Check the links on my signature to learn what we need.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2