Little problem with Group By and Min and Max

chinoknot · October 23, 2015, 4:33pm

Hello,

I need your help, I have a table with many "start" and "stop" for each day, I need to select the min and the max, grouping by day, I saw differtn topics about this, but maybe my case are different.

 SELECT log.id, Convert(varchar, log.DataLog,103) as Data,o.nome, log.NomePc, log.IpPc, log.Descrizione, 
log.idoperatore, log.Overtime, DataLog, CONVERT (datetime,log.DataLog,120 ) as datafunk, min (DataLog) as minima, max (datalog) as massima,
m.operatore, uog.idgruppolavorazione, 
CASE 
WHEN Descrizione LIKE 'Avvio%' THEN 1 
WHEN Descrizione LIKE 'Inattivi%' THEN 2 
WHEN Descrizione LIKE 'Ripresa%' THEN 3 
WHEN Descrizione LIKE 'Stop%' THEN 4	END as azione 
from dbo.lst_activitiLog log 
left join dbo.lst_activitiMachine m on log.IpPc=m.Ip 
inner join main.dbo.operatori o on convert(varchar,o.idoperatore)=convert(varchar,m.operatore) 
left join Main.dbo.Un_Op_Gruppi uog on uog.IdOperatore = o.idoperatore 
where log.descrizione not like 'Inattivit%' and m.Operatore='1002408'  
group by Convert(varchar, log.DataLog,103), log.id, o.nome, log.NomePc, log.IpPc, log.Descrizione, 
log.idoperatore, log.Overtime, DataLog, CONVERT (datetime,log.DataLog,120 ), m.operatore,  uog.idgruppolavorazione
order by Nome,DataLog

The result is

I need 2015-06-19 14:42:03.217 like Min and 2015-06-19 21:17:02.123 like Max.

How can I do?

djj55 · October 23, 2015, 5:01pm

Can we have your table definitions?

And as you do not call out the table for DataLog it is really hard to guess what you need.

SELECT log.id
	,Convert(VARCHAR, log.DataLog, 103) AS Data
	,o.nome
	,log.NomePc
	,log.IpPc
	,log.Descrizione
	,log.idoperatore
	,log.Overtime
	,DataLog
	,CONVERT(DATETIME, log.DataLog, 120) AS datafunk
	,min(DataLog) AS minima
	,max(datalog) AS massima
	,m.operatore
	,uog.idgruppolavorazione
	,CASE 
		WHEN Descrizione LIKE 'Avvio%'
			THEN 1
		WHEN Descrizione LIKE 'Inattivi%'
			THEN 2
		WHEN Descrizione LIKE 'Ripresa%'
			THEN 3
		WHEN Descrizione LIKE 'Stop%'
			THEN 4
		END AS azione
FROM dbo.lst_activitiLog log
LEFT JOIN dbo.lst_activitiMachine m ON log.IpPc = m.Ip
INNER JOIN main.dbo.operatori o ON convert(VARCHAR, o.idoperatore) = convert(VARCHAR, m.operatore)
LEFT JOIN Main.dbo.Un_Op_Gruppi uog ON uog.IdOperatore = o.idoperatore
WHERE log.descrizione NOT LIKE 'Inattivit%'
	AND m.Operatore = '1002408'
GROUP BY Convert(VARCHAR, log.DataLog, 103)
	,log.id
	,o.nome
	,log.NomePc
	,log.IpPc
	,log.Descrizione
	,log.idoperatore
	,log.Overtime
	,DataLog
	,CONVERT(DATETIME, log.DataLog, 120)
	,m.operatore
	,uog.idgruppolavorazione
ORDER BY Nome
	,DataLog

chinoknot · October 24, 2015, 3:43pm

Table definition? In which sense?

jcelko · October 25, 2015, 12:29am

I need your help, I have a table with many "start" AND "stop" for each day, I need to select the min AND the max, grouping by day, I saw different topics about this, but maybe my case are different. <<

Please follow basic Netiquette AND post the DDL we need to answer this. Follow industry AND ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

and you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

What little you posted is wrong. Never use the old Sybase CONVERT() string function that we used to mimic 1960's COBOL. We do not use the “ORDER BY ..” in query when posting on a forum. SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer.

There is no generic “id” in RDBMS. The “_id” is called an “attribute property” and it is part of an ISO-11179 data element name.

You have more CONVERT() in one statement than I would use in an entire career. No, really – today we use CAST() for data type problems and we never convert temporal data to string like you do.

85-90% of the real work in SQL is done in the DDL. Once you get the constraints, keys, DRI, correct data types in place then the DML is usually simple.

But there is another fundamental problem. Temporal values are based on intervals, {start_timestamp, end_timestamp}. Download the Rick Snodgrass book on temporal data in SQL. It is taught in most SQL classes if they are any good.

If you will follow the rules, we will try to repair your DDL

chinoknot · October 25, 2015, 10:37am

As you imagine I'm very new to this language and I found the query in this way, they asked to me for repairing the query or better, extract the dates they need: the first activity of the day and the last for each day, so I tought that a min and max in the datalog could help me, than I will group by day and the work is done, because I have little time. So, meanwhile, I thank you for the basic notions, I'll try to rewrite the query using CAST, hoping I will succeed , because I must deliver this work until tomorrow, and I am very worried.

JamesK · October 25, 2015, 11:52am

Ignore the rant about replacing CONVERT's with CAST. While you should try to do conversions and formatting in the presentation layer than in the SQL code, your immediate problem is not that.

The issue djj55 pointed out, and I am facing is that we don't know the nature of your data, and the rules/logic you want to use to get the desired results. It is very difficult to discern that from the query and screenshot that you posted. It would be very helpful if you can construct a simpler problem that shows the issue you are facing, and post it in such a way that someone can copy and paste your code and run it. See here for suggestions on how to do that.

One thing you might try is to use a window function. So instead of

min(DataLog) AS minima,
max(datalog) AS massima,

try using

min(DataLog) over (partition by m.operatore) AS minima,
max(datalog) over (partition by m.operatore)  AS massima,

I am only showing an example. it may not be "m.operatore" that you want to partition by. You should partition by the column for which you want to find the min and max of DataLog for a constant value in that column. Experiment with changing m.operatore to other columns to see what gives you the desired result.

JamesK · October 25, 2015, 4:41pm

@jcelko , please don't give wrong advice. The website you are pointing to categorically states that one should use the 'YYYY-MM-DD' format for specifying date literals. In SQL Server, sometimes it does not work, and sometimes gives ambiguous results. See the queries below:

SET LANGUAGE french
GO
SELECT CAST('2013-02-27' AS datetime); -- Fails with error message
GO

SET LANGUAGE french
GO
SELECT CAST('2013-02-11' AS date); -- converts to Feb 11, 2013
SELECT CAST('2013-02-11' AS datetime); -- converts to Nov 2nd, 2013.
GO

The correct way to specify dates in SQL Server is YYYYMMDD which will always be interpreted unambiguously.

Now that I am looking at your website (is it your website, or someone else's?) I see that it is supposed to be sarcastic "A website for romance,sarcasm, math". If the advice to use YYYY-MM-DD format was meant to be sarcastic that is a very bad form of sarcasm, especially when you are pointing people to that page without telling them that it is misleading.

jcelko · October 25, 2015, 8:52pm

You never heard of https://xkcd.com? REALLY? This is like being a geek and not knowing about BIG BANG THEORY, STAR TREK, DILBERT or other popular culture sites for us.

Again, the one and only display format in ANSI/ISO Standard SQL is the ISO-8601 “yyyy-mm-dd HH:mm:ss.sssssss”, where the number of decimal seconds is implementation defined. Under the FIPS-127 standard, it had to be five; today, most hardware can do seven places (nanoseconds).

This is more of the old Sybase/UNIX legacy that Microsoft is trying to repair. This is why they added DATE, TIME and DATETIME2(n) data types to eventually replace DATETIME and SMALLDATETIME. Actually, the DATETIME2(n) should be TIMESTAMP(n), but Microsoft used that keyword for a proprietary feature.

Did you ever wonder why DATETIME has a limit of a weird subset of three decimal places? The answer is DEC PDP-11 hardware! This minicomputer is where T-SQL started on UNIX; the hardware uses internal clock ticks to get DATETIME and that is how it rounds.

There is no concept of language settings in ANSI/ISO Standard SQL at the system as a whole. You can play with Unicode and collations in text inside SQL, but that is all. That is a OS level thing that belongs to Microsoft; a good SQL programmer will respect the tiered architecture concept of modern programming and not write code that depends on local dialects.

In fact, ISO goes out of its way to avoid any human language dependencies. This is why Unicode requires a subset of ASCII in all languages.

In SQL Server, sometimes it does not work, and sometimes gives ambiguous results .. The correct way to specify dates in SQL Server is YYYYMMDD which will always be interpreted unambiguously. <<

And so can YYYY-MM-DD, which is why we voted it into SQL when I was on the committee. But it does not play well with ISO Standards where the punctuated version is the most common version. This is why MS is moving that way. Again look at the DATE, TIME and DATETIME2(n) specs.

JamesK · October 26, 2015, 12:52pm

Let the record show that:

Whereas I posted a code fragment that would fail and/or work incorrectly if you follow the advice given by @jcelko The code is not unusual or contrived; in fact it is something that you would probably find on a very large percentage of SQL Server databases.

Whereas in response @jcelko posted a narrative which among other things gives a history of Sybase and DEC PDP-11, and how Microsoft has not caught up with the times.

Now therefore, I rest my case

chinoknot · October 27, 2015, 9:46am

Ok. I saw how to post a question. Here is my datatable.

Than I launch the query (i will not create a table, just the query for extract records):

SELECT log.id, Convert(varchar, log.DataLog,103) as Data,o.nome, log.NomePc,
log.IpPc, log.Descrizione, log.idoperatore, log.Overtime, DataLog,
CONVERT (datetime,log.DataLog,120 ) as datafunk, m.operatore,
orario as
CASE
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'Inattivi%' THEN 2
WHEN Descrizione LIKE 'Ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4 END as azione
from dbo.lst_activitiLog log
left join dbo.lst_activitiMachine m on log.IpPc=m.Ip
inner join main.dbo.operatori o on convert(varchar,o.idoperatore)=convert(varchar,m.operatore)
left join Main.dbo.Un_Op_Gruppi uog on uog.IdOperatore = o.idoperatore
where log.descrizione
not like 'Inattivit%' and m.operatore = '1002408' and m.operatore = log.idoperatore
order by Nome,DataLog

Translation for you:
Data = Date
nome = user (name and surname)
NamePc = NomePc
IpPc = IpPc
Descrizione = Description
Idoperatore = idUser
Overtime = overtime
DataLog = Datalog (the exact hour user press start or stop)
Datafunk = Datafunk (i use this record for php code)
Operatore = operatore (the person)
Azione = Action (1 - Start 4 - Stop)

When the user press start, recording the datalog in the table. Take the case 23/06/2015

23/06/2015 Action 1 = Time: 15.05.00
23/06/2015 Action 1 = Time: 16.29.28
23/06/2015 Action 1 = Time: 16.32.13
23/06/2015 Action 1 = Time: 17.59.42
23/06/2015 Action 4 = Time: 21.32.39

I want to extract only the first action 1 and the last action 4, I thought using min and max for the datalog was ok, but I have problem with the group by, error with the syntax.
I tried also the OVER instruction but I have error:

Incorrect syntax near the keyword 'over'.

the code is

SELECT log.id, Convert(varchar, log.DataLog,103) as Data,o.nome, log.NomePc,
log.IpPc, log.Descrizione, log.idoperatore, log.Overtime, DataLog, uog.idgruppolavorazione,
CONVERT (datetime,log.DataLog,120 ) as datafunk, m.operatore,
min(DataLog) over (partition by m.operatore) AS minima,
max(datalog) over (partition by m.operatore) AS massima,
CASE
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'Inattivi%' THEN 2
WHEN Descrizione LIKE 'Ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4 END as azione,
CASE
WHEN idgruppolavorazione ='66' THEN 1
WHEN idgruppolavorazione ='79' THEN 2 END as gruppo
from dbo.lst_activitiLog log
left join dbo.lst_activitiMachine m on log.IpPc=m.Ip
inner join main.dbo.operatori o on convert(varchar,o.idoperatore)=convert(varchar,m.operatore)
left join Main.dbo.Un_Op_Gruppi uog on uog.IdOperatore = o.idoperatore
where log.descrizione
not like 'Inattivit%' and m.Operatore='1002408'
order by Nome,DataLog

Now I see the manual for the OVER, PARTITION and CAST.

Meanwhile I hope you understand my question.

Ok. the basic information. I'm usgin MSSQL, theresn't OVER or DATE function, I must use CONVERT.

chinoknot · October 27, 2015, 11:36am

I think that the solution is using a subquery, but I can't find the right Syntax

JamesK · October 27, 2015, 2:02pm

What is the version of SQL Server you are using? The OVER clause should work in SQL 2005 or later. You can find your version using the following query

SELECT @@version;

chinoknot · October 27, 2015, 2:36pm

I'm in trouble!!!

bitsmed · October 27, 2015, 4:23pm

You should seriously consider upgrading.

I think the below query might help you. I'm unable to test in on version 2000, but I think it might work for you (maybe with a Little tweaking):

select log.id
      ,convert(varchar,log.datalog,103) as data
      ,o.nome
      ,log.nomepc
      ,log.ippc
      ,log.descrizione
      ,log.idoperatore
      ,log.overtime
      ,datalog
      ,uog.idgruppolavorazione
      ,convert(datetime,log.datalog,120) as datafunk
      ,m.operatore
      ,m1.minima
      ,m1.massima
      ,case
          when descrizione like 'Avvio%'    then 1
          when descrizione like 'Inattivi%' then 2
          when descrizione like 'Ripresa%'  then 3
          when descrizione like 'Stop%'     then 4
       end as azione
      ,case idgruppolavorazione
          when '66' then 1
          when '79' then 2
       end as gruppo
  from dbo.lst_activitiLog as log
       left outer join dbo.lst_activitimachine as m
                    on m.ip=log.ippc
       inner join main.dbo.operatori as o
               on convert(varchar,o.idoperatore)=convert(varchar,m.operatore)
       left outer join main.dbo.un_op_gruppi as uog
                    on uog.idoperatore=o.idoperatore
       left outer join (select m.operatore
                              ,min(datalog) as minima
                              ,max(datalog) as massima
                          from dbo.lst_activitiLog as log
                               left outer join dbo.lst_activitimachine as m
                                            on m.ip=log.ippc
                               inner join main.dbo.operatori as o
                                       on convert(varchar,o.idoperatore)=convert(varchar,m.operatore)
                         where log.descrizione not like 'Inattivit%'
                           and m.operatore='1002408'
                         group by m.operatore
                       ) as m1
                    on m1.operatore=m.operatore
 where log.descrizione not like 'Inattivit%'
   and m.operatore='1002408'
 order by nome
         ,datalog
;

chinoknot · October 27, 2015, 4:55pm

Thanks for the answer. The query works, but the results aren't good. It takes the min of all the start and the max of all the stop.
So I have the min and the max repetead for 278 rows (all the action of user).
Maybe it can be a start point.

SELECT Convert(varchar, log.DataLog,103) as Data,
o.nome,
CASE
WHEN Descrizione LIKE 'Avvio%' THEN 'Avvio'
WHEN Descrizione LIKE 'Inattivi%' THEN 'Inattivo'
WHEN Descrizione LIKE 'Ripresa%' THEN 'Ripresa'
WHEN Descrizione LIKE 'Stop%' THEN 'Stop'
END as Descrizione,
log.idoperatore, sum(log.Overtime),
m.operatore,
CASE
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'Inattivi%' THEN 2
WHEN Descrizione LIKE 'Ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4
END as azione,
CASE
WHEN Descrizione LIKE 'Avvio%' THEN MIN(log.DataLog)
WHEN Descrizione LIKE 'Ripresa%' THEN MIN(log.DataLog)
WHEN Descrizione LIKE 'Stop%' THEN MAX(log.DataLog)
END as datafunk
--CONVERT (datetime,log.DataLog,120 ) as datafunk
from dbo.lst_activitiLog log
left join dbo.lst_activitiMachine m on log.IpPc=m.Ip
inner join main.dbo.operatori o on convert(varchar,o.idoperatore)=convert(varchar,m.operatore)
left join Main.dbo.Un_Op_Gruppi uog on uog.IdOperatore = o.idoperatore
where log.descrizione not like 'Inattivit%'
and convert(varchar,log.datalog, 112) >='20151001'
and convert(varchar,log.datalog, 112) <='20151002'
and m.Operatore='1002408'
group by Convert(varchar, log.DataLog,103),o.nome, log.Descrizione, log.idoperatore,
m.operatore, CASE
WHEN Descrizione LIKE 'Avvio%' THEN 1
WHEN Descrizione LIKE 'Inattivi%' THEN 2
WHEN Descrizione LIKE 'Ripresa%' THEN 3
WHEN Descrizione LIKE 'Stop%' THEN 4
END ,log.Overtime
order by Nome,datafunk

This query works, but probably I must rearrange it.