LiveCode Forums.

Posted: **Sun May 03, 2020 12:01 am**

Dear all,

I wrote a simple function to create statistics from my SQL Database. It is collecting the count of orders every day. I copy the code e.g. 20 times but I think it can be easier with a repeat loop :-p

I mean for 3 days I can copy paste, no problem. But now I have to collect 50 days, 100 days etc and the code is being larger and larger..

My question: Any Ideas how can I create a repeat function around this stuff to create a variable (var_transfer_7tage, bottom of the code) for a graph without copy and copy and copy the code again?

Thanks
Stef

Code: Select all

#Statistik 3 Days
   
################ create the select
   put "SELECT UserID, COUNT(Bestellnummer) FROM t_daten WHERE Auftragsdatum >= DATE_SUB(CURDATE(), INTERVAL 0 DAY) and UserID = '" & username_client & "' " into sql_stat7days1
################ run the query
  put item 2 of revDataFromQuery(tab,return,dbid_hub,sql_stat7days1) into stat7days1
 
################ now put the results (no of orders for today) into sumup
   put stat7days1 into sumup
   

   
################ Now: the 2nd day:  
   
   put "SELECT UserID, COUNT(Bestellnummer) FROM t_daten WHERE Auftragsdatum >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) and UserID = '" & username_client & "' " into sql_stat7days2
   put item 2 of revDataFromQuery(tab,return,dbid_hub,sql_stat7days2) into stat7days2
   
   ################ Calculate the Orders from this day (stat7days2 - sumup
   put stat7days2 - sumup into stat7days2
   
    ################ Calculate the Orders (total) from 1st day and 2nd day)
   put stat7days2 + sumup into sumup
   
   
   ################ Now: the 3rd day:  
   
   put "SELECT UserID, COUNT(Bestellnummer) FROM t_daten WHERE Auftragsdatum >= DATE_SUB(CURDATE(), INTERVAL 2 DAY) and UserID = '" & username_client & "' " into sql_stat7days3
   put item 2 of revDataFromQuery(tab,return,dbid_hub,sql_stat7days3) into stat7days3
   
    ################ Calculate the Orders from this day (stat7days2 - sumup
   put stat7days3 - sumup into stat7days3
   
   ################ Calculate the Orders (total) from 1st day, 2nd day and 3rd day)
   put stat7days3 + sumup into sumup
   
   ### and again, again, again.... x many days
  
  # Now: Create a graph 
Put "0," & stat7days1 & CR into var_transfer_7tage
Put "1," & stat7days2 & CR after var_transfer_7tage
Put "2," & stat7days3 & CR after var_transfer_7tage

     ### and again, again, again.... x many days

Posted: **Sun May 03, 2020 8:40 am**

Hi,

I suggest you don't repeat at all. This gives you a nice list of the last 100 days, and a job count each for user "Anton":

Code: Select all

SELECT datum, bearbeiter, COUNT(id) AS jobCount, DATEDIFF(DATE(NOW()),datum) AS daySince 
FROM `auftrag` WHERE (DATEDIFF(DATE(NOW()),datum) < 101) AND (bearbeiter LIKE 'Anton%') 
GROUP BY datum, bearbeiter ORDER BY datum, bearbeiter;

My result looks like this:

Code: Select all

datum	bearbeiter	jobCount	daySince
2020-01-24	Anton	18	100
...
2020-04-29	Anton	10	4
2020-04-30	Anton	9	3

Looking up 14K job entries & extracting these takes 0.078s in my mySQL.

Now that you have the data you can utilize LCs text manipulation capabilities to present them in any way you desire.

Have fun!

Posted: **Sun May 03, 2020 9:25 am**

^ what AxWald said

Posted: **Sun May 03, 2020 9:14 pm**

OK... Sounds easy. But it is not :-p

Because: The date is not only JJJJ/MM/TT, it is a date string.

Code: Select all

SELECT Auftragsdatum, UserID, COUNT(Bestellnummer) AS jobCount, DATEDIFF(DATE(NOW()),Auftragsdatum)AS daySince 
FROM `t_daten` WHERE (DATEDIFF(DATE(NOW()),Auftragsdatum) < 10) AND (UserID = '1234') 
GROUP BY Auftragsdatum, UserID ORDER BY Auftragsdatum, UserID

is the result

Auftragsdatum UserID jobCount daySince
2020-04-24 01:25:34 1234 1 9
2020-04-24 01:30:34 1234 1 9
2020-04-24 01:20:11 1234 1 9

(..)

Any Ideas how to change the select (group) by day? I mean it is possible to write also the date (JJJJ/MM/TT) in the DB but it is better so solve this in the SQL select.

Stef

Posted: **Sun May 03, 2020 9:44 pm**

Solved:

Code: Select all

SELECT date(Auftragsdatum), UserID, COUNT(Bestellnummer) AS jobCount, DATEDIFF(DATE(NOW()),Auftragsdatum)AS daySince 
FROM `t_daten` WHERE (DATEDIFF(DATE(NOW()),Auftragsdatum) < 101) AND (UserID = '1234') 
GROUP BY date(Auftragsdatum) ORDER BY Auftragsdatum, UserID

Thanks
Stef

LiveCode Forums.

Repeat around SQL Selects...

Repeat around SQL Selects...

Re: Repeat around SQL Selects...

Re: Repeat around SQL Selects...

Re: Repeat around SQL Selects...

Re: Repeat around SQL Selects...