i have query that don't work in sqlite but in mysql work and make good result.
sqlite :
table definition
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), "begin" datetime);
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),"end" datetime);
CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50),
Product varchar(60), Location varchar(50), Begin datetime, End datetime,
Difference integer, PRIMARY KEY
(Code,Level,Category,Product,Location,Begin,End));
query :
insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1.Begin, akhir1.End,
strftime("%s",akhir1.End)-strftime("%s",awal1.Begin) as Difference from awal1,
akhir1 where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and
awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and
akhir1.End >= awal1.Begin group by awal1.Begin, awal1.Code, awal1.Category,
awal1.Product, awal1.Location
data i attached in zip file.
mysql
table definition
CREATE TABLE `awal1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`Begin` datetime NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `akhir1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`End` datetime NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `hasil1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`Begin` datetime NOT NULL default '0000-00-00 00:00:00',
`End` datetime NOT NULL default '0000-00-00 00:00:00',
`Difference` int(8) NOT NULL,
PRIMARY KEY (`Code`,`Level`,`Category`,`Product`,`Location`,`Begin`,`End`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
query
insert ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1.Begin, akhir1.End,
time_to_sec(timediff(akhir1.End, awal1.Begin)) as Difference from awal1, akhir1
where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and
awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and
akhir1.End >= awal1.Begin group by awal1.Begin, awal1.Code, awal1.Category,
awal1.Product, awal1.Location;
sory if my post messed up, sory for my bad english.
i attached data and this file or u can download at
http://rapidshare.com/files/167784565/up.zip.html
thanks 4 ur advanced
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
