view.sql

This application i made for handle simple finance this project using VB 6.0, SQL Server 2000 wit

use Finance;
go


-- ### AccountNoHeaderView #########################################################

if (exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where (TABLE_NAME = 'AccountNoHeaderView')))
  drop view AccountNoHeaderView;
go

create view AccountNoHeaderView as
(
  select Kode, NamaAccount
  from Account
  where
    (Kode not like '%0')
)
go


-- ### TransactionView #############################################################

if (exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where (TABLE_NAME = 'TransactionView')))
  drop view TransactionView;
go

create view TransactionView as
(
  select
    NoBukti, Dibayarkan, TanggalEntry, dbo.PayMethodString(SistemBayar) as [SistemBayar],
    NamaBank, NoCek, TanggalCek, dbo.CurrencyString(MataUang) as [MataUang]
  from
    MainTransaction
  where
    (NoBukti != '00.00.00.000')
)
go


-- ### AreaView ####################################################################

if (exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where (TABLE_NAME = 'AreaView')))
  drop view AreaView;
go

create view AreaView as
(
  select IdArea, NamaArea from Area
  where
    (IdArea != 0)
)
go


-- ### AccountView #################################################################

if (exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where (TABLE_NAME = 'AccountView')))
  drop view AccountView;
go

create view AccountView as
(
  select Kode, NamaAccount, SaldoAwal
  from Account
  where
    (Kode != '000000')
)
go


-- ### AccountMutasiView ###########################################################

if (exists (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where (TABLE_NAME = 'AccountMutasiView')))
  drop view AccountMutasiView;
go

create view AccountMutasiView as
(
  select
    ac.Kode as [Kode], ac.NamaAccount as [NamaAccount], mt.TanggalEntry as [TanggalEntry],
    case t.Mutasi when 0 then t.Jumlah else 0 end as [Debet],
    case t.Mutasi when 1 then t.Jumlah else 0 end as [Kredit]
  from Account as [ac]
    inner join TransactionDetail as [t] on (ac.Kode = t.IdAccount)
    inner join MainTransaction as [mt] on (t.IdMainTrans = mt.NoBukti)
)
go