Jochem

maybe add before the loop: _SQLite_Exec($GasDriving_Log, "begin immediate;") and after the loop _SQLite_Exec($GasDriving_Log, "commit;")

consolewrite(stringtrimright("C:\Users\joesoef pc\Desktop\autoit\program\lijst\lijst2\August", 6) & @LF)

#include <Array.au3> #include <File.au3> global $sFilePath = ("C:\Users\joesoef pc\Desktop\autoit\program\lijst\lijst2\August") global $sDrive = "", $sDir = "", $sFilename = "", $sExtension = "" global $aPathSplit = _PathSplit($sFilePath, $sDrive, $sDir, $sFilename, $sExtension) consolewrite($sDrive & $sDir & @LF)

SQLite expert is a great tool to manage your DB too. You can create also a db with this tool. http://www.sqliteexpert.com/

these are the three tables I am using: CREATE TABLE [AD_group_members] ( [ADGMid] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT COLLATE NOCASE, [ADGMUsId] INTEGER(25) NOT NULL CONSTRAINT [fkUSid] REFERENCES [Users]([USid]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, [ADGMGroupId] INTEGER(25) NOT NULL CONSTRAINT [fkGroupId] REFERENCES [AD_Groups]([ADgroupid]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE); CREATE TABLE [AD_Groups] ( [ADgroupid] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [ADgroupname] CHAR NOT NULL UNIQUE COLLATE NOCASE); CREATE TABLE [Users] ( [USid] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [USname] CHAR(50) NOT NULL UNIQUE COLLATE NOCASE, [USfirstname] CHAR(25) COLLATE NOCASE, [USfamilyname] CHAR(50) COLLATE NOCASE, [USstatus] CHAR(25) COLLATE NOCASE, [USfullname] CHAR(50) COLLATE NOCASE, [USSite] CHAR(25) COLLATE NOCASE DEFAULT unknown);  oops I forgot one table; added it now, the hole db is around 1000 lines. Maybe not a good idea to post here?

Is there something like group_concat(......ODER BY..........) I have a querie and want to order also the grouped values alphabatical SELECT ADgroupname, group_concat((SUBSTR(USfullname, 1,LENGTH(USfullname)-6)) || ' ,' , ' ' ) "users" FROM AD_group_members JOIN users ON ADGMUsId = usid AND USstatus IS 'enabled' JOIN AD_Groups ON ADGMGroupId = ADgroupid GROUP BY ADgroupname ORDER BY ADgroupname

no problem at all, I added a collumn (LUTimeEpoche) instead of change the values in the LUtime, so till now, I have both values. I preffered keeping to much data, instead of making changes while a wasn`t shure if I did it the wright way I forgot to explain this in my first post actually

Yes, that was also the reason that I created the date table.

Well I check once every 5 minutes the license servers output, and the time datetime i use is every five hole minutes, so they are grouped every 5 minutes. Easier to check what software is used on a specific time (or at least, that was the idea) instead of searching a value. but anyway I will introduce the rowid collumn. thanks

just one question again: I am strugling with the: CONSTRAINT "sqlite_autoindex_license_Usage_1" PRIMARY KEY ("LUpackage", "LUuser", "LUtime")) WITHOUT ROWID; because now i see that one software package has a very bad license model, and if somebody opens twice the program it uses two licenses. so the above unique constraint isn`t correct (there needs to be a duplicate values), but i can`t remove it because "without rowid" needs a primary key, And there isn`t any correct unique constraint, with those three collumns. So I think I should recreate the rowid collumn. Am I wrong? Or should I make a collumn with the number of used licences by one person, which is in 99,999999% of the cases just 1. and makes my autoit scipt harder to make. There is only a performance reason to use withoud rowid as I understood

thanks again

thanks

your query workes like a charm. for the license_date I will try to update the date into license_Usage.LUtime without destroying the data. but do you know why i get an error in autoit on "without rowid" (malformed database schema (license_Usage) - near "WITHOUT": syntax error) thanks a lot!

Thanks for the answers yet, I will first work on the no-op constrains. I thought the the "on conflict ignore" option ignores the transaction. Data is a separate table because severall users can use the software at the same time. the sizes I used to get a smaller screen in the record editor in sqlite expert. Sometimes I need to change something in sqlite directly. but first i will remove all the no-op constrains

I have severall tables with the usage of software in our company with some foreign keys Now I want to get a list of the 5 most common users of a software package. I cant get a list of users. Maybe it is easier to do with auto-it code, but I would like to do it with a single sqlite query. I tried a lot of joins, but can`t get it working, I only get the following query working with the id of users. query: SELECT LUuser, COUNT (LUuser) AS cnt from license_usage where LUtime > (SELECT LDid FROM license_date ORDER BY ABS((SELECT strftime('%s','now','-2 month')) - LDdate) LIMIT 1) and LUpackage = (select LPid from license_package where LPname = "package") GROUP BY LUuser ORDER BY cnt DESCLIMIT 5; db: CREATE TABLE [Users] ( [USid] INTEGER NOT NULL ON CONFLICT IGNORE PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, [USname] CHAR(50) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE, [USfirstname] CHAR(25) COLLATE NOCASE, [USfamilyname] CHAR(50) COLLATE NOCASE, [USstatus] CHAR(25) COLLATE NOCASE, [USfullname] CHAR(50) COLLATE NOCASE); CREATE TABLE [license_Usage] ( [LUid] INTEGER NOT NULL ON CONFLICT IGNORE PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, [LUpackage] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_package] REFERENCES [license_package]([LPid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, [LUuser] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_user] REFERENCES [Users]([USid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, [LUtime] INTEGER(10) NOT NULL ON CONFLICT IGNORE CONSTRAINT [FKLU_time] REFERENCES [license_date]([LDid]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED COLLATE NOCASE, UNIQUE([LUpackage], [LUuser], [LUtime]) ON CONFLICT IGNORE); CREATE TABLE [license_package] ( [LPid] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, [LPname] CHAR(50) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE, [LPdesciption] CHAR(50) COLLATE NOCASE); CREATE TABLE [license_date] ( [LDid] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, [LDdate] INTEGER(15) NOT NULL ON CONFLICT IGNORE UNIQUE ON CONFLICT IGNORE COLLATE NOCASE);