I have problem with executing this query in sqlite. to reconstruct problem
please follow the following steps.
1. create table awal1, akhir1 and hasil1 first.
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), "Begin" datetime);
INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:25');
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),"End" datetime);
INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2',
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1',
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:27');
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"));
2. then execute this 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;
3. check the result
the result that i hope is like this
A1236 MEDIUM FOOD SNACK HOMS 1 2007-5-6 10:48:57 2007-5-6
11:19:25 1828
A1236 MEDIUM FOOD SNACK HOMS 2 2007-5-6 10:48:57 2007-5-6
11:19:21 1824
A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:20:34 2007-5-6
11:31:57 683
A1222 SMALL FOOD WAFER HOMS 2 2007-5-6 11:20:34 2007-5-6
11:31:57 683
A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:31:57 2007-5-6
11:31:57 0
A1221 SMALL FOOD CAKE HOMS 2 2007-5-6 11:31:57 2007-5-6
11:31:57 0
A1220 SMALL FOOD MARGARINE HOMS 1 2007-5-6 11:42:46 2007-5-6
11:42:46 0
A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:42:46 2007-5-6
11:42:46 0
A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:42:46 2007-5-6
11:42:46 0
A1269 SMALL CLOTHES BELT HOMS 3 2007-5-7 17:28:25 2007-5-7
17:28:27 2
how to make result like i hope?
thanks for advanced
note : the result that i hope is mysql result's, so i confuse how to do this in
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows
in mysql server, my computer starts not responding temporary, it tooks 100% cpu
usage but less PF or memory usage.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
