Funzione Between o similare

di
chinoknot
il
5 risposte
C
chinoknot
Pochi posts
Iscritto da
nov, 2015
Messaggi:
5

Funzione Between o similare

Ciao a tutti,

questo è il mio primo post spero di trovare una soluzione. Ho una serie di START,STOP e INATTIVITÀ' in un DB e ho la necessità che l'informazione INATTIVITÀ' sia sempre contenuta tra START e STOP, in quanto io eseguo una SUM dentro questo DB e questa SUM deve essere eseguita solo ed esclusivamente se l'inattività è contenuta fra START e STOP, altrimenti il valore che mi esce è sbagliato.

Esempio:

Inattività ore 11 - 20 minuti
Start ore 11.15
Inattività ore 11.45 - 15 minuti
Stop Ore 11.15

Ciò di cui io ho bisogno è che l'inattività sia solo 15, e non 35 minuti.

Questo è il codice
select 
	   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
Il risultato per farvi un'idea è:

5 Risposte

  • C
    chinoknot
    Pochi posts
    Iscritto da
    nov, 2015
    Messaggi:
    5

    Re: Funzione Between o similare

    Ho la possibilità di portarmi dietro anche l'id dell'operazione, pertanto mi basta semplicemente verificare che l'id sia più grande del precedente.
  • E
    elle0087
    Utente Attivo
    Iscritto da
    ott, 2010
    Messaggi:
    173

    Re: Funzione Between o similare

    Ciao,
    riesci a metter un esempio della tua tabella di input?
  • C
    chinoknot
    Pochi posts
    Iscritto da
    nov, 2015
    Messaggi:
    5

    Re: Funzione Between o similare

    Ciao, grazie della risposta.

    Come puoi vedere dalla query ci sono diverse join che fanno sì che le tabelle interagiscono fra loro.
    Questo è un stralcio di tutte le attività fatte da diversi operatori, poi nel caso specifico io mi vado a cercare un singolo operatore attraverso una maschera che ho fatto in PHP, il risultato è appunto le ore lavorative, meno i minuti di overtime. Per semplificarmi la vita faccio una SUM dei minuti di inattività e faccio poi lo sottrazione sulle ore globali date da datalog MIN della giornata e datalog MAX della giornata. Nel php la condizione che deve essere verificata è che l'inattività deve essere compresa tra l'avvio e lo stop, il problema sorge quando ci sono delle inattività che vengono eseguite prima (purtroppo è un errore dovuto ad uno script che calcola i minuti dopo che il muose è rimasto fermo per 10 minuti), nella query pertanto faccio sì la somma di questi periodi di inattività, ma si crea un problema con la condizione PHP che in tutti gli effetti non è verificata, pertanto non viene effettuata la sottrazione alle ore lavorate. Quindi dovrei verificare nella query SQL che la datalog dell'inattività sia maggiore dello start e solo in questo caso fare la somma.

  • sspintux
    sspintux
    Molto attivo
    Iscritto da
    dic, 2012
    Messaggi:
    501

    Re: Funzione Between o similare

    Guarda che ti hanno chiesto gli script (create table, insert) per poter provare.

    Come puoi immaginare,pochissimi potrebbero avere
    voglia e tempo per ricrearsi sul db la tua situazione a scopo di test
  • E
    elle0087
    Utente Attivo
    Iscritto da
    ott, 2010
    Messaggi:
    173

    Re: Funzione Between o similare

    Ciao,
    provo a farti un esempio.
    presa questa tabella di esempio:
    
select 's'descr,convert(date,GETDATE())datalog into ##test where 1=0

insert ##test(descr,datalog)
values('S',GETDATE()+1),
	  ('S',GETDATE()+2),
	  ('I',GETDATE()+3),
	  ('E',GETDATE()+4),
	  ('I',GETDATE()+5),
	  ('I',GETDATE()+6),
	  ('S',GETDATE()+7),
	  ('S',GETDATE()+8),
	  ('I',GETDATE()+9),
	  ('I',GETDATE()+10),
	  ('E',GETDATE()+11),
	  ('I',GETDATE()+12),
	  ('S',GETDATE()+13),
	  ('I',GETDATE()+14),
	  ('I',GETDATE()+15),
	  ('I',GETDATE()+16),
	  ('E',GETDATE()+17),
	  ('S',GETDATE()+18)
    dove 'S'=start; 'I'=inattività; 'E'=end

    la query potrebbe essere:
    
with tmp as
(
	select ROW_NUMBER()over(order by datalog)r1,
		   *
	from ##test
	--order by datalog
)
(
	select *
	from
	(
		--estraggo le inattività strettamente vicine a uno start...
		select --t1.r1,
			   max(t2.r1-t1.r1) d1,
			   max(t1.datalog)data1,
			   max(t1.descr)descr1,
			   max(t2.datalog)data2,
			   max(t2.descr)descr2,
			   min(t3.datalog)data3,
			   min(t3.descr)descr3
		from (select * from tmp where descr='S') t1
		join (select * from tmp where descr='I') t2
		  on t2.r1>t1.r1
		join (select * from tmp where descr='E') t3
		  on t2.r1<t3.r1
		where t2.r1-t1.r1=1
		group by t2.r1-t1.r1,--t1.r1,
				 t2.datalog
	)t1
	--una volta trovate le inattività vicine uno start ricavo 
	-- tutte le altre che sono comprese tra la sua data e sua data
	-- di termine
	left join (select * from tmp where descr='I') t2
	  on t2.datalog>=t1.data2
	 and t2.datalog<t1.data3
)
order by d1--,d2--t1.datalog,t2.datalog,t3.datalog
    non penso sia chiarissima la logica, x cui fammi pure delle domande che cerco di risponderti nel miglior modo possibile...
Devi accedere o registrarti per scrivere nel forum
5 risposte