Ilmu Sql

select a.approvalid, approvalname, tipe + ' - ' + name tipe,
a.approver1, b.position_name_en approvername1,
a.approver2, c.position_name_en approvername2,
a.approver3, d.position_name_en approvername3,
a.approver4, e.position_name_en approvername4,
a.approver5, f.position_name_en approvername5
from MApprovalCapex a
inner join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) b on (a.approver1=b.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) c on (a.approver2=c.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) d on (a.approver3=d.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) e on (a.approver4=e.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) f on (a.approver5=f.position_id)
inner join MApprovalCapexTipe g on (a.tipeid=g.id)

CREATE table trcapa_detail_action_verification_history as

select * from trcapa_detail_action_verification

create function [dbo].[umur](@tanggallahir datetime, @tipe as tinyint)
   RETURNS varchar(100)
   with ENCRYPTION
   --select dbo.umur('12/17/1982', 1)
AS
Begin
DECLARE @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int
Declare @hasil as varchar(30)
   
    set @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @tanggallahir, getdate()), @tanggallahir)
    set @years = DATEDIFF(year, @tanggallahir, getdate()) - (CASE WHEN @thisYearBirthDay > getdate() THEN 1 ELSE 0 END)
    set @months = MONTH(getdate() - @thisYearBirthDay) - 1
    set @days = DAY(getdate() - @thisYearBirthDay) - 1
   
    if @tipe=1--full
    begin       
        set @hasil = cast(@years as varchar(2)) + ' tahun, ' + cast(@months as varchar(2)) + ' bulan, ' + cast(@days as varchar(3)) + ' hari'
    end
    else if @tipe=2--Tahun bulan
    begin
        set @hasil = cast(@years as varchar(2)) + ' tahun, ' + cast(@months as varchar(2)) + ' bulan'
    end
    else if @tipe=3--Tahun aja
    begin
        set @hasil = cast(@years as varchar(2))
    end
    else if @tipe=4--bulan aja
    begin
        set @hasil = cast(@months as varchar(2))
    end
    else if @tipe=5--hari aja
    begin
        set @hasil = cast(@days as varchar(3))
    end
   
    return @hasil
end

select birthdate, dbo.umur(birthdate,1) umur from employees where birthdate is not null

 Gambar 1

 Gambar 2

Perintah diatas digunakan untuk menghidupkan server database

 

Gambar 3

Perintah diatas digunakan  untuk login ke database

Gambar 4

Untuk melihat database yang ada dapat dilakukan dengan cara mengetikan perintah di atas

Gambar 5

Perintah diatas digunakan untuk memilih database yang akan kita gunakan

Gambar 6

 Gambar 7

Perintah di atas digunakan untuk melihat tabel-tabel atau view-view yang ada di dalam di database

Gambar 8

Perintah di atas digunakan untuk menampilkan structure table

Gambar 9

Perintah diatas digunakan untuk melihat structure index

Gambar 10

Silahkan dicoba sendiri :D