SQL Aggregate Function as Table using PHP

SQL aggregates as defined by netizens is "perform a calculation on a set of values to return a single scalar value". We often use aggregate functions with GROUP BY, HAVING clause of SELECT statements.

While it is very simple showing tables with just one aggregate or a set of aggregate functions using SELECT and GROUP BY then showing the table as retrieved by the SELECT statement, the table is practically vertical listing in nature. For example

SELECT states, people, SUM(revenue) FROM sales GROUP BY states,people order by states DESC

will show when you format it as table from the query result directly.

People   Revenue   State
Sam       100,000    Selangor
Ken        200,000    Selangor
Joe         100,000    Johor

What if you want to show all the people in the state regardless whether there are sales made by individual and the percentage of their Revenue for the state? It is quite impossible to do it in a single select statement.

The following method shows how to use a single statement to get a list of people and their revenue plus percentage of the group.

First we create an associative array from a list of people and populate it in the array.

$keys=array('Sam','Ken','Joe','April');
$values=array('Revenue'=>0,'Percent'=>0);
$total=0; //totals of the group
$revenue=array_fill_keys($keys,$values)

Retrieve the data using the previous SELECT statement like below. The example uses SQLite.

$db=new SQLite3('revenue.db');
$result=$db->query("select people, sum(revenue) from sales group by people");
while ($row=$result->fetchArray(SQLITE3_NUM))){
$ppl=$row[0];
$revenue[$ppl]['Revenue']=$row[1];
$total +=$row[1];
}
$db->close();
//do calculation and populate table
foreach ($revenue as $key=>$row){
$percent=round($row['Revenue']/$total*100,2);
echo " {$key}
{$row['Revenue']}{$percent}";}

Obviously, the above could be done by 2 queries. One to retrieve the overall total and the other one as above. However, what if you want to list the sales by date and show the people in columns? That is not an easy task representing it in one or two SQL.

You could retrieve the query by revenue grouped by date and people. Your array setting will be

$keys=array('Date','Sam','Ken','Joe','April');
$people=array_fill_keys($keys,$values)

$revenue=Array(); //you could fill the array with every date in the period you choose.

In the while loop, do as follows (assuming date is unix datetime),

$rdate=$row[0]; // the date field

if (!isset($revenue['A'.$rdate])){

$revenue['A'.$rdate] =$people;

$revenue['A'.$rdate]['Date']=date('m/d/Y',$$rdate);

) // set up the array and populate the 'Date' of the array for easy retrieval later

$people = $row[1] //the people field

$revenue['A'.$rdate][$people]=$row[2]; // the revenue field

With this you could populate the whole columns of people with date as first column. Displaying the table is then a breeze using the FOREACH example above.

SQLite syncing with remote data to act as local data

There is a need to get a set of data from a remote server and distribute to a group of local computers. The data must not be deleted then inserted. The data also may not be available on remote server due to it being closed and removed.

If the data cannot be deleted (truncated) then the only way is to do "insert or ignore" so that existing data is either updated if it exists or inserted if it is not. Meanwhile, there is a problem of remote data does not exist while local data exists.

Well, it is quite easy. Two tables are used. One for the main table with a key index field and the other one only the key index field. Both table index uses the same field.

When importing data, both tables are inserted with the remote data. The second table was truncated before the import starts. This means the main table contains new and old data while the second table only contains the new data. At the end of importing, just simply delete from the main table where the index field not exists in second table. Thus only the imported data remains in the main table.

Example of the "insert or ignore" goes like this

Insert or ignore on maintable (f1, f2,f3....) values ('x1', 'x2, 'x3'...);update maintable set f2='x2', f3='x3' where f1='x1'

It seems that the above will insert and then update if the index is not found but it is still better than using two sql statements to insert or update plus one statement to check whether the index exists before doing either insert or update statement.

Example of the delete command goes like this

delete from maintable where  f1 not in (select f1 from secondtable)

The main table can then be access by local users.

Triggers in SQLite

Although triggers are common in databases. I seldom use it. Recently there is a need to pre-check before an insertion whether the number of records in a certain condition is within the quota set. Also, there is a need to check whether the specific field value exists already. Finally, there is a need to check whether one field content is in the list of another.

Under normal condition, I would set up three queries to check for it before doing the insertion of records. It will be untidy to code if this is done the normal way. Triggers then is the more cleaner way to do so.

The setting up is quite straight forward.

Create trigger before insert on for each row begin select

It then followed by the list of conditions

case when ((select....) > ) then raise(abort, "error message")
when ((select from where = NEW. ) is not null) then  raise(abort, "error message")
when ((select from where = NEW.) is not null) then raise(abort, "error message") end;
end;

In the script we just need a normal insert SQL with a exception check, (PHP try and catch) to get the error message. Just get the different message and act accordingly. It is very much cleaner in coding without much coding in the application.

This trigger can be inserted externally using normal SQL procedure. It can also be removed the same way. Obviously, same name trigger must be removed first before insert.

There are much more type of triggers. This one only talks about INSERT BEFORE.

SQLITE query hourly data over a date range

There was a request to get monthly summary data in a half hourly period. This is to allow user to view the month's data by the half hour range. For example, user need to know what is the volume of customer coming in at which period so that they could prepare to have enough staff to cater to the inflow.

The following is assuming the date is stored as UnixTimeStamp.

The first task is to get the half hour period using strftime.

strftime('%H',DateCreated,'unixepoch','localtime') as Hour, case when cast(strftime('%M',DateCreated,'unixepoch','localtime') as integer) <30 as="" else="" end="" minute="" p="" then="">
Don't forget to get the count

,count(Customer) as Count

Of course you must define the table

from Customer

Next thing to set is the date range as month.

where strftime('%Y-%m-%d',DateCreated,'unixepoch') between '2018-08-01' and '2018-09-01'

We also want to limit the time to 10am-10pm

and strftime('%H:%M:%s',DateCreated,'unixepoch','localtime') between '10:00:00' and '23:00:00'

Finally group by the hour and minute

group by Hour, Minute

The result is you get 3 columns Hour, Minute, Count like

10 00 5
10 30 1
11 00 95
,..

Obviously, at a certain time, there will be no data thus the specific time period will be missing. A good way is to create an associative array with all the time defined and the value of Count set as 0. Use Hour, Minute as the key like '1000'. Update the count using the keys from the result. In this way a complete list of the period will be available.

SQLITE Datetime Query

SQLITE does not have date datatype fields. The date information is either entered as text or numbers. Querying and getting the information is a bit tricky.

If it is a text, the date is stored as YYYY-MM-DD HH:MM:SS.SSSS.

If it is a Julian date, the data is stored as REAL, a float type.

If it is a Unix timestamp, it is stored as INTEGER.

Normally we want to get the data formatted according to local date time format. Singapore format is DD/MM/YYYY.  To get the data we use SQLITE function strftime.

If it is a text or Julian Date, we use strftime('%d/%m%Y %H:%M:%S',fieldname).

If it is Unix datetime, we use strftime('%d/%m%Y %H:%M:%S',fieldname,unixepoch).

The above is both applicable in the SELECT and WHERE query. You can use datetime('now') when adding/updating to add current date time.

Often we need to find the difference between two datetime. Using the above method, the difference can be found for text and Julian date. Use strftime and %S, %M, or $H or even %d or %M to find  the difference in the respective area. For Unix timestamp, since it is already in seconds, it is simply one minus the other as they are already integer values.

Now getting an offset of a date time is also not that difficult. Use datetime(fieldname, '+' || offset || ' minutes') to add minutes (can be any part of a datetime).

Conclusion, it is a bit troublesome but manipulating datetime in SQLITE is not that difficult.

FileMaker check for date range overlapping

Normally when we want to check if a datetime falls between a range of dates, we will use a SQL to count where queryDate >= startDateField and queryDate <= stopDateField.

The tricky part is to find if a range of date overlapps other range date. Common sense will say that just modify the query to

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) .

The above method works if the queryStartDate and/or queryStopDate fall within the startDateField and stopDateField. What if queryStartDate and queryStopDate is beyond the date range covered by startDateField and stopDateField? The modified query will not work as both date are outside of the date range.

To cover such condition, there is a need to check whether startDateField and/or stopDateField is in the range of queryStartDate and queryStopDate. So in addition of the modified query, we need to add extra condition like below.

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) or
(startDateField => queryStartDate and startDateField <= queryStopDate)  or
(stopDateField => queryStartDate and stopDateField <= queryStopDate)

In plain English, the query check whether the query date range overlap/within the field date range or the field date range overlap/within the query date range. Took a while for me to sort out the confusion.

Filemaker Chart vs Web Javascript chart

Recently have issues with Filemaker chart thus have to switch to web based charts. There are quite a number of similarities and oddities. The following is the comparison.

Filemaker	Web
Uses tables	Uses variables
Forms data from table with data pre fetched from SQL result	Forms data direct from SQL result
Charts Preformed	Charts can be defined at run time
Number of legends fixed	Number of legends determined from runtime
Max number of legend fixed	No Max number of legend
Max number of data rows not fixed	Max number of rows not fixed
Titles, X,Y labels prefixed	Determined during runtime although can be prefixed
Chart type prefixed	Chart type can be changed runtime
Interactive chart	Interactive chart
Data values can be displayed	Data values can be displayed
Number of chart types limited	Number of chart types depends on the module writer. Some can have 90 plus chart types
No time lapse display of chart data	Have time lapse display of data
Charts module fixed	Can practically create the chart from scratch.
Not resizable during run time	Can auto resize or changed during runtime
Color prefixed	Color changeable during runtime if needed
Options setting prefixed	Options totally changeable runtime.
Events trigger not available	Events trigger available depends on module writer.
Chart data can be added/removed runtime	Chart data can be added/removed runtime
Must use Filemaker to view chart	Charts can be rendered in any web programming language that can work with javascript and have a graphics display capability.
Expensive.	Can be totally free.
Charts formed by fix app setting	Plain javascript text setting.
Easy to do even with novices	A big learning curve especially when you create chart from scratch
Standardized	Way too flexible until it is scary

You can see that Filemaker charts are for easy creation but is fixed in most features. It is not flexible at all compared to Web Javascript charts. Both caters to specific group of people and interest.

How to use a script to create data for a Filemaker Chart

In my previous post on Filemaker Charts, I touched on how to create a table and use it to create charts. In daily operation, the data are actually daily transactions. it is never meant for charts. How then could we transform the data into charts. It is easily done in Excel where user can create summaries to be used in charts. There is a summary in Filemaker too but it cannot be used for charts.

Here I propose using scripts to populate a summary table from a details table. The example code will use Filemaker Charts as an example.

The first thing to fill is "Months" field. Create a calculated text field in the main table and call it "YearMonth". It is necessary to add year as the data may span a number of years. Use the formula below as calculation assuming the field that shows the date of record creation is called CreateDate.

Year(CreateDate) & Case(Month(CreateDate) < 10;"0" & Month(CreateDate); Month(CreateDate)

The above will generate a text like "201604" for current month. It is necessary to insert "0" for single digit month so that the sequence of the data created will be correctly done.

Create the summary table and chart according to examples shown in Filemaker Charts. Create a script called "Summary".

Usually a summary table will pull the data for the entire last year. So the script will start with getting the year.

Set Variable($year;Year(Get(CurrentDate)-1

With the year, append the month value in a loop like below

Set Variable($mth;1)
Loop
   If($mth < 10)
      Set Variable($ym; $year & "0" & $mth)
   else
      Set Variable($ym; $year & $mth)
   End If
New Record/Request
...
Set Variable($mth;$mth+1)
Exit Loop If($mth>12)
End Loop

The above script only set the loop to get the correct YearMonth value in 12 loops. We need to fill the "Month" field in the summary table. The following script assume you are already in the summary table layout using summary table as database. The subsequent scripts are to be inserted in the line before "Set Variable($mth;$mth+1)" represented by "..."

Set Field(Summary::Month;$ym)

The next task is to fill SiteA, SiteB, and SiteC with the correct summary data from the sales data field "TotalSales". Well, you guess it right, we will use ExecuteSQL function.

Set Field(Summary::SiteA; ExecuteSQL("SELECT Sum(TotalSales) FROM MainTable WHERE SiteLocation = 'SiteA' AND YearMonth=?";"";"";$ym)

Repeat it for SiteB and SiteC.

Run the script and you will get 12 records with the completed sales figure for last year. Your chart will then be able to show the yearly sales figure for the three sites.

A reminder: You need to delete all records of previous data in summary table. Filemaker Charts does not have the facility to define which data to use in the table.  Its useless to set a filter as it only applies while the table is in view.

A suggestion is to copy the data into a history record of the yearly performance table. Extract the necessary data back into summary table to show the yearly figure for the year you want to see the chart.

One last thing, The SQL is a very simple one. You could extract the whole year data for all three sites in just one ExecuteSQL

Set Variable($data; ExecuteSQL("SELECT YearMonth,SiteLocation,Sum(TotalSales) FROM MainTable WHERE YearMonth LIKE '" & $year & "*' GROUP BY YearMonth, SiteLocation" ;"";",";)

Use a loop to extract the various rows using GetValue($data;$loop) then extract each item by replacing "," with "¶" then use GetValue to extract the items.

The complex SQL method only useful if you extract just the sales figure. In many situations you need to get more result than that. Usually they use different criteria like TotalSales > 1000000, Total Sales >100000000. One SQL will not be able to extract such criteria and it will be difficult to update the table if you use more than one SQL. Since the first SQL will fill all the 12 records already. You will then have to use filter to choose the right record then add the subsequent SQL result into the correct record.

It is impossible to use ExecuteSQL to update the records. ExecuteSQL does not allow Insert or Update (3rd party SQL plugin does have this capability).  It is easier to use simple SQL and then create a record to fill the appropriate fields with the correct SQL result with the resultant multiple SQL that loads the system runtime. The choice is up to you.

   

Filemaker ExecuteSQL in a table view field

There is a need to verify the quantity of part number in the main table against the total quantity in another table. It is impossible to use summary type field as it requires break field but summary type can only provide running total.

In the table view on a layout the subtotal can be computed using the sub-summary feature but it cannot be use as a link to a related table.

The only other option is to use ExecuteSQL. The fist thought is to use the function directly as a calculation. The syntax of the function is as follows

ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo)

The result is "?". According to documentation, it is the result returned when the ExecuteSQL encounters SQL error. However, there is absolutely no error in the SQL statement. It turned out that another function "Let" must be used in conjunction with ExecuteSQL. The modified calculation is as below

Let($query=ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo);$query)

Unfortunately, it failed again. In the field options, there is a setting to not store the result in the table. It must be set so that the calculation must be done every time the field is shown. Only then the result of the calculation can be shown.

The next step is to link this result with another table together with the part number. It again failed. The reason is due to the previous paragraph setting.

There is no way the calculated field can be used as a link to a related table. The only way is to run a script to add the result into a text field then use that text field to link to the related table. It cannot be used for dynamic update but it worked.

One worry is the fact that ExecuteSQL runs a query for every field that is shown. This means that there will be considerable delay since it cannot be stored. If there are millions of records to be updated this  way, it will take forever.

The conclusion is that it can be done but not real time and it causes considerable loading to the process. How I wish FM add a summary function to show total with breaking field instead of running total.

SQL COALESCE

I find it difficult to use this function. Its use is really limited.

The function coalesce(esp1,esp2....) is actually an expansion of NVL(esp, replacement) in some language.

Both function checks for NULL. NVL() is translated to if "esp" is null then use "replacement" otherwise use "esp". Coalesce() extends the "if" to many evaluations and has no "replacement" unless you specifically define one expression that is always true.

What then is the use of coalesce()? Basically, it allows you to return a value from a list of expressions in the order that you defines it. It will pick up the value of the first expression that return a NOTNULL.

In a more English term, the function allows you to choose the first field from a list of fields that is not empty.

In practice, I can't find a real use of it. At least not for the field of work I am in. However, I can imagine a practical use. For example, I have a list of prices for a given part. The prices are select in the order of preferences refurbished, local, regional and worldwide. If the higher preference price is not available then the next lower price preference is used. Usually, the different prices are not compiled. Rather, it is taken from joins that refers to different tables which are updated independently. Thus it is normally unknown if a particular preference has a value. Obviously, there must be another coalesce to indicate where the source of the price is from.

Another example is as follows.

I have a list of free gifts which I need to keep track of the stock. When customer indicates a choice. It is updated into the database at the particular choice field. I am then able to simply "count" the different choices made by customers. At the same time, I am able to view the choices made by individual customer. Two actions with just one update.

Choosing dates in SQL

If there is a datetime field in your database, chances is that you would want to choose a period between dates. There are more than one way to choose a period.

First I would show the wrong way.

Select mydate from mytable where datefield > '1/1/1900' and datefield < '1/31/1900'

This SQL is not wrong by itself. However, without time qualification, the default time is 00:00:00. It then posts a problem. ">" actually means greater than. This means that the date 1/1/1900 00:00:00 is excluded. "<" refers to less than. This means that the date 1/31/1900 00:00:00 and above will not be included too. In English, this condition means "exclusive" term.

A more correct way is as follows.

select my date from mytable where datefield >= '1/1/1900' and datefield < '2/1/1900'

Another way to select a period is as follows.

select my date from mytable where datefield between '1/1/1900' and '1/31/1900'

I am not sure if that is correct too since the time definition on the second date is missing. To be more exact the second date should be 1/31/1900 23:59:59.

SQL Server Replication issues

Still having problem doing the replication.

One thing I notice is that the user had to be both OS admin user and SQL Server Admin user. Just add servername\adminuser as user and allow sysadmin right to it. Subsequently, use this user for all replication.

Another thing is that error 2812 can be caused by a number of reasons. One blogger suggest to use "SP_removedbreplication 'databasename' (not the distribution database rather the actual database to be replicated). I tried that and it actually works.

It is a bad idea to remove the "distribution" database. SQL server does not cleanup after you. The publisher and distributor somehow still retains the information.

Brioquery does not refresh table structure

When you use BrioQuery, be aware that the tables in the sections does not refresh itself. If the server database alters the table structure, it will not be updated in your section. You need to delete the table and re-insert the same table to get updates.

SP not found

When you try to setup Replication in SQL Server and you encounter Error 2812 "Stored Procedure" not found. Basically you will be lost for answers.

The actual problem could be in the creating of the distributor. For example, my labtop is a XP when I create a distributor, it promps a "XP agent" error 15281.

The creation process is then just terminated without it being fully completed. It is possible then the necessary SP is not setup properly. I did not actually test the setup of the publisher in such condition. I actually make change to the "SQL Server Surface Area Configuration" on "Features" and checked "enable OPENROWSET..." immediately before reconfiguring the distributor.

The reconfiguration of the distributor is quite straight forward. Right click on the Replication icon and then choose "Distributor properties". Click on "Publisher" and un-check what ever you have set earlier. Add the same publisher to it and you can create local publication now.

Oracle Client

Oracle client is a facility where you can use to query oracle databases. If you are only interested to just install the ODBC part of the client, then you don't need to install the whole package. Just download the basic package and the odbc package from Oracle.

Create a new folder and unzip the two packages into the directory. You will need two extra file from Microsoft to be present in the same directory also. They are mfc71.dll and msvcr71.dll. Somehow the two files are required but never available in the packages.

Run odbc_install in the directory in dos prompt. It is important to run it in dos prompt as you can see the run result of the installation.

Go to system environment and add ORACLE_HOME and TNS_ADMIN variable. The value of the variables are the directory of the installed packages.

Don't forget to create/copy the sqlnet.ora and tnsnames.ora into the same directory also.

Now you can create an ODBC for the database.

I will not go into detail of the ODBC DSN setup as some users like DSNLESS way to query databases.

Make db offline for SQL Server.

Have trouble take some db offline. According to some blogger, they say it is some other sql manager connection to the db. However, I manage to take two of bd off line on the same server. There could be some one else connected to the db but I cannot be sure.

One blogger suggests the following command

ALTER DATABASE db SET OFFLINE WITH ROLLBACK IMMEDIATE 

Another blogger says do this to kick out other users.

ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Yet to try it but both looks promising. Will update this if successful over the weekend.

Not a trusted SQL Connection

When you set up a sqlserver, there is an option that you must always set before any remote administrator can access to the sqlserver using "Sql Server Management Studio".

At the server where you install the sql server. Start "Sql Server Management Studio". (Assuming you have already setup sql server).

Goto menu bar View>Registered Server

Choose the local instance that you have setup previously and double click on it.

You can skip the above two steps if you can see the local instance in the object explorer already.

Right click on the instance in the object explorer and choose properties.

Select Security.

Under "Server Authentication", check "Sql Server and Windows Authentication mode".

Click "OK"

You should now be able to connect to the sql server from remote sql server management tool.

MsAccess utility to calculate offset time.

There is a requirement from user to calculate time difference in minutes between two date. The requirement is that you will not count the time from 6pm to the next day 9am.

Since we know that stored procedures and user defined functions cannot work in MsAccess if you want to use the database in web pages, you will have to do it in the views itself.

The formula is as follows.

tat: IIf(DatePart("d",[CREADATETIME]) <> DatePart("d",[ACKDATETIME]),IIf(DatePart("w",[CREADATETIME])=7,DateDiff("n",[creadatetime],[ackdatetime])-2520,IIf(DatePart("h",[creadatetime]) >18,DateDiff("n",Format([ackdatetime],"m/d/yyyy"),[ackdatetime])-540,DateDiff("n",[creadatetime],[ackdatetime])-900)),DateDiff("n",[creadatetime],[ackdatetime]))

It looks awful to concatenate the iifs but it works.

The script has a few conditions.

1. If it is Saturday, it will offset the time from 3pm to the next Monday 9am (2520 min).

2. If it is weekday and greater than 6pm, calculate the time from 9am (540 min) of the second date.

3. If is normal hours and span more than one day then offset by 15 hours (900 min)

4. If both date are on the same day then just calculate the difference between the two date.

MsAccess user function

MsAccess can add user function in modules and used in MsAccess. However, never try it if you want to use it on web sites. It will fail with the message "function xxx not found". Even if you put the function in View, it will still fail.

Deleting whole table with Identity Field

If you delete the whole table data by using "delete from mytable" SQL command or manually deleting all records through SQL Server Manager, The Identity field sequential number will not reset.

If you want to reset the Identity field sequential number, you should use "truncate table mytable" instead.