Rick Guizawa <guizaw...@gmail.com> wrote:
> INSERT INTO a
> SELECT 'Item',
> x.Item,
> (SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
> MAX(TheDate) FROM b WHERE Item = x.Item)),
> strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
> TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b WHERE
> Item = x.Item)))),
> ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
> WHERE item = x.Item) AS REAL) * 100) , 4),
> y.frequency,
> (SELECT SUM(Frequency) from c WHERE item = x.Item)
> FROM b x, c y
> WHERE x.Item = y.Item AND
> y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
> BY x.Item;
Your statement is equivalent to this simpler one:
INSERT INTO a
SELECT 'Item',
x.Item,
strftime('%Y-%m-%d',max(TheDate)),
strftime('%Y-%m-%d', max(TheDate),
(select skippedweeks from c
where Item = x.Item and Frequency = max(y.Frequency)) * 7 || ' days'),
ROUND(CAST (max(frequency) AS REAL)/CAST (SUM(Frequency) AS REAL) * 100, 4),
max(frequency),
SUM(Frequency)
FROM b x, c y WHERE x.Item = y.Item
GROUP BY x.Item;
You want indexes on b(Item) and either c(Item) or c(Item, Frequency) (the
latter in case you have lots of records in c with the same item but different
frequencies; the former if you have just a few records for each Item).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
