0

I am using control center HFSQL of windev 20 to execute a query. I would like to calculate the time and date difference, between the maximum and the minimum MAX (hour) and MIN (hour) for each service and also the number of days if there is.

I tried the functions DateDifference and DateTimeDifference but unfortunately I have an error.

the following code is just to see the maximum and the minimum hour for each service it works perfectly but not what i want :

SELECT  Service.Libellé AS Libellé,  
Min(DetailCircuitFacture.Heure),MAX(DetailCircuitFacture.Heure) 
FROM detailcircuitfacture
joIN Service on
Service.CodeSce=detailcircuitfacture.CodeSce
group by Service.Libellé

i want to make difference of date and hour between MAX and Min for each service like so :

SELECT Service.Libellé AS Libellé, WL.DateDifférence(Min(DetailCircuitFacture.DATE),
Max(DetailCircuitFacture.DATE)) AS Nombre_jours,
    WL.DateHeureDifférence(Min(DetailCircuitFacture.Heure),Max(DetailCircuitFacture.Heure)) AS Nombre_heurs
    FROM detailcircuitfacture
    JOIN Service on
    Service.CodeSce=detailcircuitfacture.CodeSce
    group by Service.Libellé

I expect the output without error, but the actual output is

Error in the SQL code of the <> request. Initialization of the query impossible. Aggregate functions (COUNT, AVG, SUM, MIN, MAX) are not allowed outside SELECT or HAVING clauses

expect result

expected result

Thanks by advance

jack john
  • 69
  • 1
  • 11

1 Answers1

0

I would say your issue is trying to use the result of an aggregate function as a function parameter.

You could solve this problem with a subquery:

  SELECT Libellé,
         WL.DateDifférence(min_date, max_date) AS Nombre_jours,
         WL.DateHeureDifférence(min_heurs, max_heurs) AS Nombre_heurs
    FROM (
     SELECT Service.Libellé AS Libellé, 
            MIN(DetailCircuitFacture.DATE) AS min_date,
            MAX(DetailCircuitFacture.DATE) AS max_date,
            MIN(DetailCircuitFacture.Heure) AS min_heurs,
            MAX(DetailCircuitFacture.Heure) AS max_heurs
       FROM detailcircuitfacture
       JOIN Service ON Service.CodeSce=detailcircuitfacture.CodeSce
   GROUP BY Service.Libellé
     ) core
 GROUP BY Libellé;

EDIT: since the original question was updated to include that it is about HFSQL, I should say I have no experience using HFSQL. This answer is based on basic SQL syntax shared by more common databases like MySQL, PostgreSQL, and SQL Server.

derek.wolfe
  • 1,086
  • 6
  • 11
  • when i executed your query i have this error : Error in the SQL code of the query Can not initialize the query. Use of aggregate functions with other non-aggregate fields or functions is not allowed without the use of a GROUP BY clause. All non-aggregate fields or functions must be in the GROUP BY clause. – jack john Apr 04 '19 at 17:13
  • Looking back at the query, that makes sense. Since your Différence functions are not aggregate functions, the last group by isnt needed. Remove the last line of that query. – derek.wolfe Apr 04 '19 at 17:16
  • when i have removed the line i have now this error in this picture https://i.stack.imgur.com/hw8vk.png – jack john Apr 04 '19 at 17:19
  • 1
    That error is telling you that the format of one of your dates is invalid. It is expecting a format of AAAAMMJJ. I believe that format would be 20190404 for today. But it looks like the problem in your original question is solved. – derek.wolfe Apr 04 '19 at 17:28
  • yes you are right the origine question is solved i close this question thank you so mush for your help – jack john Apr 04 '19 at 17:44
  • No problem. Glad to help – derek.wolfe Apr 04 '19 at 17:55