Database Design of Equipment History Software

Thursday, September 22, 2011 1:46 PM Anonymous No comments

Alhamdulillah, Database Design of Equipment History Software has been successfully created by Ar-Rohman Software House Team in one night after analyzing the customer demand for a week. There is a new experience in the process of it SQL syntax making that we will share to you through this post. The SQL syntax is associated with a feature that requested by the customer. Previously, please look at this table design picture.

Software Table Design

The demanded feature is the damage frequency reports by the equipment damage type at a particular period. The steps to make it SQL syntax so that data can be displayed on the report are as follows :
1. Selecting all data

SELECT * FROM [table name]

2. Modifying the SQL syntax in the first step by selecting the desired column and add the SQL function : COUNT (column that holds the damage data) to display the frequency of damage

SELECT [column names], COUNT(column that holds the damage data) AS frequency FROM [table name]

The SQL AS syntax function is to aliasing the column names that generated by SQL COUNT function

3. Adding the SQL syntax : GROUP BY [name of the column that holds damage data] to display the damage data by it type

SELECT [column names], COUNT(column that holds the damage data) AS frequency FROM [table name] GROUP BY [column that holds the damage data]

4. Adding the SQL syntax : BETWEEN '[yyyy-mm-dd]' AND '[yyyy-mm-dd]' to filter the data at a particular period

SELECT [column names], COUNT(column that holds the damage data) AS frequency FROM [table name] BETWEEN ‘[yyyy-mm-dd]’ AND ‘[yyyy-mm-dd]’ GROUP BY [column that holds the damage data]

The SQL syntax of Equipment History Software Feature Report has been completed. Hopefully this post can increase your insight about the database and the SQL. Please don’t forget to try it ...