|
|
select str_to_date(concat(format(day,0),".",format(month,0),".",replace(format(year,0),',','')),'%d.%m.%Y') as booking_date from flight limit 10; +--------------+ | booking_date | +--------------+ | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | | 2010-01-01 | +--------------+ 10 rows in set, 30 warnings (0.00 sec)
select str_to_date(concat(format(day,0),".",format(month,0),".",replace(format(year,0),',','')),'%d.%m.%Y') as bookingtime, sysdate() as sysdate, timestampdiff(day,sysdate(), str_to_date(concat(format(day,0),".",format(month,0),".",replace(format(year,0),',','')),'%d.%m.%Y')) as timediff from flight limit 100; +-------------+---------------------+----------+ | bookingtime | sysdate | timediff | +-------------+---------------------+----------+ | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-01 | 2010-02-01 18:55:27 | -31 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-05 | 2010-02-01 18:55:27 | -27 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-10 | 2010-02-01 18:55:27 | -22 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-15 | 2010-02-01 18:55:27 | -17 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-01-20 | 2010-02-01 18:55:27 | -12 | | 2010-05-01 | 2010-02-01 18:55:27 | 88 | | 2010-05-01 | 2010-02-01 18:55:27 | 88 | | 2010-05-01 | 2010-02-01 18:55:27 | 88 | | 2010-05-01 | 2010-02-01 18:55:27 | 88 | | 2010-05-01 | 2010-02-01 18:55:27 | 88 | +-------------+---------------------+----------+ 100 rows in set, 600 warnings (0.02 sec)
Die replace-Funktion ist notwendig, da format() für vierstellige Zahlen ein Komma in die Zahl reinmogelt (im US/UK-System sind ja die Tausenderpunkte Kommas).
Die Generierung der Zufallstabellen für Vornamen, Nachnamen und Strasse habe ich nicht in Freebasic, sondern in MS Excel vorgenommen. Egal, die entscheidenden Zeilen wären:
dim as string firstnames(31),names(31),streetnames(31) 'Einlesen... for i=0 to 6787 firstname=firstnames(rnd*31) lastname=names(rnd*31) streetname=streetnames(rnd*31) streetno=rnd*50 next iDas SQL zum Zusammenbasteln der neuen Kundentabelle:
create table customer3 as
select
a.*,
concat(
case
when substr(c.cityname,1,1)='B' then 1
when substr(c.cityname,1,1)='D' then 4
when substr(c.cityname,1,1)='H' then 2
when substr(c.cityname,1,1)='F' then 6
when substr(c.cityname,1,1)='M' then 8
else 0 end,floor(rand()*10000)) as zip,
c.cityname
from
customer2 a,
customer b,
airport c
where
a.customerid=b.id and
b.cityid=c.id;