Repeat around SQL Selects...

[ace16vitamine]

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 
   

[AxWald]

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!

[SparkOut]

^ what AxWald said

[ace16vitamine]

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

[ace16vitamine]

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