Eminence

Alright. So now, I am trying to select only the dates from SQLite. I've tried this: Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, SUM(CASE WHEN attendance_status LIKE 'absent' THEN 1 ELSE 0 END) AS abs_count, SUM(CASE WHEN late_hours > 0 THEN 1 ELSE 0 END) AS late_count, SUM(CASE WHEN punch_out LIKE 'MISSING EOD' THEN 1 ELSE 0 END) as missing_eod, printf('%2.2f', 100.0 * SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 ELSE 0 END) / SUM(CASE WHEN schedule_status LIKE 'WORK' THEN 1 ELSE 0 END)) AS abs_rate, printf('%2.2f', 100.0 * SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 ELSE 0 END) / COUNT(CASE WHEN schedule_status LIKE 'WORK' THEN 1 ELSE 0 END) * 540) AS abs_in_mins FROM data_table WHERE date >= '2018/8/8' AND date <= '2018/8/17' GROUP BY supervisor" What happens now is it doesn't result to anything. I've read on the SQLite date documentation that SQLite only supports date with the format of 'YYYY/MM/DD', is it possible that it is not showing because my from date's format lacks one digit (2018/8/8) instead of (2018/08/08)?

Thanks, @jchd! That's a great help, learned as well. It is working as intended now.

So I tried this: Dividing count of attendance_status tagged with absent over the count of shedule_status tagged with work. Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS abs_count, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS late_count, (COUNT(CASE WHEN attendance_status IS 'ABSENT' THEN 1 END) / COUNT(CASE WHEN schedule_status IS 'WORK' THEN 1 END) * 100) FROM data_db WHERE supervisor != '' GROUP BY supervisor" It does return a value of 0 though, whereas changing the order to schedule_status over the count of attendance_status it does return a different result. Any ideas?

CREATE TABLE data_db ( id INTEGER PRIMARY KEY AUTOINCREMENT, employee_number STRING, employee_name STRING, supervisor STRING, date DATETIME, schedule_status STRING, attendance_status STRING, operational_minutes STRING, late_hours STRING ); My bad. Here's the DDL. The data comes from a .CSV file generated from an internal web application. So I'm trying to count the total number of employees' schedule_status tagged as WORK and their attendance_status tagged as ABSENT and from there, calculate the Abs. Rate %.

Here's what I have

Could be. I'll look into it. Thanks again @Danp2!

Thanks @Danp2. After running, it says no such column: abs_count, do I also have to create a column for abs_count in SQLite instead?

Below is my current SQL statement. I'm still figuring out how to calculate the percentage of the total count of scheduled vs absent employees though. Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name), COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS count1, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS count1 FROM data_db WHERE supervisor != '' GROUP BY supervisor"

Just another quick question related to this, do I need to have a separated column in the database if I want to calculate the difference of the count of the total employees' schedule status tagged as work and the count of the total employees' attendance status tagged as absent? What I want to do is calculate the absenteeism rate per supervisor based on the formula above however, I don' have a column for the absenteeism rate.

Thanks! Will check on this.

Now I'm trying to count the number of employees per supervisor that has the attendance_status of Absent using this query. Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name), COUNT(attendance_status) FROM data_db WHERE attendance_status = 'ABSENT' GROUP BY supevisor" But what it does is it also affects the second row(total count of the employees name) only showing employees that has the tagging of absent when I just want it to affect the third column.

There we go. Thanks a bunch!

Hello, Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. Any help will be much appreciated. Thank you!

Will look into it. Thanks!

Hello, So basically, I'm on a page on a site where frameset is composed of .js and .swf. I was able to simulate a tab using the Send("{TAB}") function however, using Send("{ENTER}") function does not work and as an alternative I'm about to try and use a mouse click function though there's nothing on the page that I can use as it is a Flash based site. Any of you got an issue same as this one? Here's my code at the moment: #include <IE.au3> Local $oIE = _IECreate("basic") Local $oFrames = _IEFrameGetCollection($oIE, 0) _IEImgClick($oFrames, "directlinktoimage") WinActivate($oIE) Sleep(5500) Send("{TAB 16}") ; => Hit tab 16 times to get to the element to be clicked Send("{ENTER}") ; => Not workinig The url to site is not available as it is only internal.

Oh my bad. Yep, understood now. Thanks @TheXman

Hello, I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date. This is my current code: Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance. *EDIT The date format in the database is by MM/DD/YYYY HH:MM:SS.

Awsm! Thanks to the both of you.

Hello, Does separating a user defined function from the main script onto another .au3 file considered acceptable as a best practice? For example, I have defined functions for a SQLite database connections and stuff on the main file together with other defined functions with different functionalities having about hundreds of line together. I'm thinking of having it separated and create another file to have it included at the top part of the script instead of having it all inside the main file. Basically separating structure from functions. Any thoughts?

My bad, I was under the impression that the help file won't be providing incorrect examples so I eliminated that possibility first. Lesson learned tho. Thank you!

Hello, I'm having an issue on the _IEImgClick function. It basically opens a specified webpage and should click on the image. However, I'm having an error upon running the code. IE.au3 T3.0-2 Warning from function _IEImgClick, $_IESTATUS_NoMatch Below is my current code at the moment. ; Click on IMG by src sub-string #include <IE.au3> Local $oIE = _IE_Example("basic") _IEImgClick($oIE, "autoit_6_240x100.jpg", "src") _IEQuit($oIE) It is an example from the AutoIT help file, I'm not sure why it is stating that error tho as it should be fine. Any of you came up with a solution to this? Thanks in advance.

Oh lol. Thanks! My bad. Will do next time. Thank you!

Hello, Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable? Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array. #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. Any help is appreciated. Thanks in advance.