I'm having problem with a SUm, because I need a Sum after a Group by.
This is the query:
SELECT isnull (sum(overtime),0) as overtime, Operatore, Descrizione, Datalog, Ip, IpPc, NomePc from lst_activitiLog l inner join lst_activitiMachine m on l.IpPc=m.Ip where m.Operatore = '1002408' and overtime is not null and convert(varchar,l.datalog, 112) = '20151002' group by Operatore, Descrizione, Datalog, Ip, IpPc, NomePc order by Operatore,Datalog
I need a subquery which summarize the first column 113 +31 = 144.. I'm using MS SQL 2000 and I can't use Over, so I need another way to do this sum. Help me!!!
The following query should work, although I'm not sure in which table the field "ip" belongs. I'm guessing the lst_activitylog table. (the other fields, I found the table names from your previous post).
select l2.overtime
,m.operatore
,l.descrizione
,l.datalog
,l.ip
,l.ippc
,l.nomepc
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and m.overtime is not null
and l.datalog>=cast('20151002' as datetime)
and l.datalog<cast('20151003' as datetime)
group by m.operatore
) as l2
on l2.operatore=m.operatore
where m.operatore = '1002408'
and m.overtime is not null
and l.datalog>=cast('20151002' as datetime)
and l.datalog<cast('20151003' as datetime)
order by m.operatore
,l.datalog
;
Almost done. There is a little problem, the query give me all the inactivties. As seem as don't take the "l.datalog>=cast('20151002' as datetime)"
select t.overtime
,m.operatore
,l.descrizione
, left (convert(varchar,l.datalog, 112),10) as data
,m.ip
,l.ippc
,l.nomepc
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and overtime is not null
and convert(varchar,l.datalog, 112) = '20151002'
group by m.operatore
) as t
on t.operatore=m.operatore
select t.overtime
,m.operatore
,l.descrizione
,l.datalog
,left (convert(varchar,l.datalog, 112),10) as data
,m.ip
,l.ippc
,l.nomepc
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and overtime is not null
and convert(varchar,l.datalog, 112) = '20151002'
group by m.operatore
) as t
on t.operatore=m.operatore
where convert(varchar,l.datalog, 112) = '20151002'
and l.descrizione LIke '%Ripresa%'
Thank you so much! Probably the query it could be lighter, but the important thing is that it works now.
Ok! The last problem. I have some case which the "l.descrizione LIke '%Ripresa%' is before the "Avvio", in this case I want only the "l.descrizione LIke '%Ripresa%' is between Avvio and Stop, if is before it mustn't do the sum, if the Ripresa is after is ok, if is before, don't work.
I can carry with me the ID and do a compare, I think is the easyest way.
Last query:
select l.id,
t.overtime
,m.operatore
,l.descrizione
,l.datalog
,left (convert(varchar,l.datalog, 112),10) as data
,right(convert(varchar,l.datalog, 108),10) as datatt,
case
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4
END as test
,m.ip
,l.ippc
,l.nomepc
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select
m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and
CASE
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4
END between 1 and 4
and overtime is not null
and convert(varchar,l.datalog, 112) = '20150930'
group by m.operatore
) as t
on t.operatore=m.operatore
where convert(varchar,l.datalog, 112) = '20150930'
order by l.datalog
Filtering on datetime the way I showed, should work if your field type is datetime (looks like it is from your sample data). Also, the way I showed is much faster on tables with many rows.
To test, you could try running this:
select *
from lst_activitilog
where datalog>=cast('20150930' as datetime)
and datalog<cast('20150930' as datetime)
;
or this:
select *
from lst_activitilog
where datalog>=cast('20150930 00:00:00.000' as datetime)
and datalog<cast('20151001 00:00:00.000' as datetime)
;
or this:
select *
from lst_activitilog
where datalog>=convert(datetime,'20150930')
and datalog<convert(datetime,'20151001')
;
or this:
select *
from lst_activitilog
where datalog>=convert(datetime,'20150930 00:00:00.000')
and datalog<convert(datetime,'20151001 00:00:00.000')
;
It should show data for 30-09-2015 only. Can you confirm this? If not, please show table definition (create statement).
Now, the last output you show - what should the correct output look like?
