Quiz 2

CREATE FUNCTION JTotalFaktur
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN

DECLARE @Result money


select @Result = sum(totalfaktur) from FJ
where tglFJ between @tglAwal and @tglAkhir


RETURN @Result

END
GO

-------------------------------------------------------------------------------

CREATE FUNCTION JPelanggan
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN

DECLARE @Result integer


select @Result = count(distinct kodepelanggan) from FJ
where tglFJ between @tglAwal and @tglAkhir


RETURN @Result

END
GO

-------------------------------------------------------------------------------

CREATE FUNCTION JBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS integer
AS
BEGIN

DECLARE @Result integer


select @Result = sum(fd.qty)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir


RETURN @Result

END
GO

-------------------------------------------------------------------------------

CREATE FUNCTION JNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN

DECLARE @Result money


select @Result = sum(fd.qty * fd.harga)
from FJDet fd, FJ f
where (f.NoFJ = fd.NoFj) and f.tglFJ between @tglAwal and @tglAkhir


RETURN @Result

END
GO

-------------------------------------------------------------------------------

select dbo.JTotalFaktur('6/1/2005','6/30/2005 23:59:59') as JumlahTotalFaktur,
dbo.JPelanggan('6/1/2005','6/30/2005 23:59:59') as JumlahPelanggan,
dbo.JBarang('6/1/2005','6/30/2005 23:59:59') as JumlahBarang,
dbo.JNilaiBarang('6/1/2005','6/30/2005 23:59:59') as JumlahNilaiBarang