However this does appear to have worked! Seems a bit long-winded to me but
it worked. I think - going to compare to the same thing in Excel and just
check the data before I do a happy-dance.
UPDATE tmp_dspace_import
SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb, 0)
FROM tmp_dspace_import AS prev WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb, 0)
FROM tmp_dspace_import AS prev WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
diff_free_pc = (SELECT tmp_dspace_import.free_pc - ifnull(prev.free_pc,
0) FROM tmp_dspace_import AS prev WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1);
On Tue, Aug 22, 2017 at 11:35 AM, Matthew Halliday <fj1200...@gmail.com>
wrote:
> Hi Clemens, thanks for that.
>
> Unfortunately is comlains after "SET diff_used," and I get "near ",":
> syntax error: "
>
> If I seperate it out into stand-alone statemeonts like this:
>
> UPDATE tmp_dspace_import
> SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb,
> 0) FROM tmp_dspace_import AS prev WHERE prev.date_time <
> tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
> diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb,
> 0) FROM tmp_dspace_import AS prev WHERE prev.date_time <
> tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
> diff_free_pc = (SELECT tmp_dspace_import.free_pc -
> ifnull(prev.free_pc, 0) FROM tmp_dspace_import AS prev WHERE
> prev.date_time < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT
> 1);
>
> then it comes back "no error:" but doesn't update anything. Not sure if
> the abokve is ok - looks ok to me. The only question in my mind is that it
> won't diferentiate between servers and drives.SOme only have a C drive but
> most hace a D and E, some have an F. All local drives btw, not mapped.
>
> On Tue, Aug 22, 2017 at 10:32 AM, Clemens Ladisch <clem...@ladisch.de>
> wrote:
>
>> Matthew Halliday wrote:
>> > I used the SQLite Studio to create the table so used the DATETIME data
>> type
>> > for that, and although I used yyyy-mm-dd hh:mm:ss in the script
>>
>> That is correct.
>>
>> > it seems to have reverted it to dd/mm/yy hh:mm:ss.
>>
>> That would not be usable.
>> Check the actual format with the sqlite3 command-line shell.
>>
>>
>> Assuming the timestamp format is usable, you can compute the differences
>> with a statement like this:
>>
>> -- add three columns; then:
>> UPDATE MyTable
>> SET (diff_used, diff_free, diff_free_pc)
>> = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0),
>> MyTable.free_mb - ifnull(prev.free_mb, 0),
>> MyTable.free_pc - ifnull(prev.free_pc, 0)
>> FROM MyTable AS prev
>> WHERE prev.date_time < MyTable.date_time
>> ORDER BY date_time DESC
>> LIMIT 1);
>>
>> This is much faster if there is an index on date_time.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
