Thursday, September 22, 2011

Database Design of Equipment History Software

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 ...

0 comments:

Post a Comment

If you have something to say about this post, please fell free to write it in the comment form below. We will reply to all comments as soon as possible. Thanks ...