Good morning all
Sorry for the long email. I'm back to using SQLite after some years away
from it and from databases in general, so a bit rusty. I'be been trying to
figure this out for almost a week now but can't quite get my head around it
although I think I understand the principles.
My problem is I am not sure how to calculate between rows. I have looked
at many examples online and tried hard to adapt them into my scenario but
after a week of it its getting frustrationg.
We have a small db that monitors disk space on 40-odd servers in a
manufacturing situation. We monitor disk growth day by day every 6 hours
so we can project forward what we will need in the coming years and how
long current space will last with some new kit coming on stream.
A vbscript (we have old legacy kit that doesn't understand Powershell) runs
a regular WMI query and simply pulls of the disk data.
I have a simple import table: id, servername, drive, capacity, used_mb,
free_mb, free_pc (%) and a date_time field. I have a large number of csv
files to import and crunch, so I simply need to subtract the previous
Used_mb, free_mb and free_pc values from the curent ones to get the disk
data. I want to do:
new Used_mb - old used mb
old free_mb - new free_mb
old free_pc -new free_pc
(its in Mb because its the best value for what I need. I'll convert it
later as needed)
I understand I can do a self-join and create a virtual table but don't
really understand how it works.
tried something like
SELECT DISTINCT a.*
FROM (SELECT servername, date_time, drive, used_mb
FROM tmp_dspace_import
GROUP BY servername, drive, date_time) a
JOIN (SELECT servername, date_time(getdate) -1, drive, used_mb
FROM tmp_dspace_import
GROUP BY servername, drive, date_time) b
ON a.servername = b.servername AND a.used_mb > b.used_mb AND
a.date_time > b.date_time
just getting really confused now.
I have ammended the script to import direct into the db after generating
the csv file and I want to run the update there either as an UPDATE or a
trigger.
Its in a db because I have other purposes I can use the data for. The
import table is a temp location.
Hope this helps and I'm not waffling here.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
