Kumpulan tanggal dari dua tanggal

Terkadang kita ingin melihat daftar tanggal dari range tanggal tertentu. Misalkan kita ingin menampilkan tanggal berapa saja yang ada pada range dari tanggal 20-02-2014 s/d 01-03-2014.

Hasil tampilan :

 

 

 

 

 

 

 

Dengan perintah SQL, kita dapat menuliskannya sebagai berikut :

    SELECT aDate FROM (
SELECT @maxDate – INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY aDate FROM
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a, /*10 day range*/
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b, /*100 day range*/
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c, /*1000 day range*/
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d, /*10000 day range*/
(SELECT @minDate := ‘2014-02-20’, @maxDate := ‘2014-03-01’) e
) f
WHERE aDate BETWEEN @minDate AND @maxDate
ORDER BY aDate

Pada bagian bawah query, masukkan tanggal awal pada @minDate dan tanggal akhir pada @maxDate

Terima kasih,

Semoga bermanfaat.

MySQL : Concat String return null

SELECT p.*
FROM 2_crm_persons p
WHERE CONCAT(p.name, p.name2) LIKE ‘%blackphp%’

That’s query above give a null result because field name or field name2 is null.

The solution is, check field value with IFNULL and give result empty string if null : IFNULL(p.name,”).

Query will be :

SELECT  p.*
FROM 2_crm_persons p
WHERE CONCAT(IFNULL(p.name,”),IFNULL(p.name2,”)) LIKE ‘%blackphp%’

Regard.

Blackphp

MySQL – Casting String to other type

How to casting or convert a data type to the other with MySQL.
1. String to Double

SELECT
n,
CONVERT( n, DECIMAL(10,2) )
FROM tabel
Result :

n	CONVERT( n, DECIMAL(10,2) )
19.8	19.80
2	2.00

2. String to Integer

SELECT
n,
CONVERT( n, SIGNED )
FROM tabel


n	CONVERT( n, SIGNED )
19.8	19
2.16	2

Thanks, i hope helpfull 😀

Catatan Kecil Query MySQL

Setelah melihat brangkas saya yang dulu, ada salah satu catatan kecil berupa catatan query dengan database MySQL, daripada file ini nantinya menjamur lebih baik saya share saja.
Semoga dapat digunakan sebagaimana mestinya :D.

Blackphp@

The meaning of VIEW, TRIGGER and STORED PROCEDURE in SQL

View :

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don’t. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables. [1]

1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Trigger :

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries. [2]

Stored Procedure :

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires the execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another. The maximum level of nesting is 32. [3]

[1] http://www.sql-tutorial.com/sql-views-sql-tutorial/

[2] http://en.wikipedia.org/wiki/Database_trigger

[3] http://en.wikipedia.org/wiki/Stored_procedure

[4] http://imdjkoch.wordpress.com/2010/06/21/viewsbasic-concepts/