LATIHAN FUNGSI
DIKETAHUI SEBUAH DATABASE SIPP, berisi data penjualan dan pembelian.
1. Seorang operator ingin mengetahui nomor pelanggan terakhir dan nomor pelanggan yang baru. Cari di table Pelanggan. Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Jawaban model 1 :
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select top 1 @NomorAkhir=kodepelanggan
from pelanggan
order by kodepelanggan desc
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,5) + 1
print @x
Set @NomorBaru = 'C-'+ right('00000' + CONVERT(varchar,@X),5)
PRINT @NomorBaru
Jawaban Model 2 (menggunakan max):
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select @NomorAkhir=max(kodepelanggan)
from pelanggan
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,5) + 1
print @x
Set @NomorBaru = 'C-'+ right('00000' + CONVERT(varchar,@X),5)
PRINT @NomorBaru
2. Seorang operator ingin mengetahui nomor bukti penjualan terakhir dan nomor yang baru. Cari di table FJ. Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int
select @NomorAkhir=max(noFJ)
from FJ
PRINT @NomorAkhir
Set @x = right(@NomorAkhir,7) + 1
print @x
Set @NomorBaru = 'FJ-'+ right('0000000' + CONVERT(varchar,@X),7)
PRINT @NomorBaru
3. Munculkan semua transaksi penjualan yang hari selasa
a. Tampilkan semua transaksi tersebut
b. Ada Berapa jumlah transaksi
c. Berapa totaltransaksi (dilihat dari TotalFaktur)
--Day of Week : 1=Minggu, 2=Senin, 3=Selasa
select NoFJ, TglFJ, TotalFaktur
from FJ
Where datepart(dw,tglfj)=3
Declare @JumlahFJ money, @TotalFJ money
select @JumlahFJ=Count(*), @TotalFJ=sum(TotalFaktur)
from FJ
Where datepart(dw,tglfj)=3
Select @JumlahFJ as JumlahFaktur, @TotalFJ as TotalFaktur
4. Seorang operator ingin mengetahui INISIAL PELANGGAN (Diambil dari huruf pertama dari setiap kata pada NamaPelanggan
Jawaban : mencari posisi spasi menggunakan charindex
select namapelanggan, left(namapelanggan,1) +
substring( namapelanggan, charindex(' ', namapelanggan)+1, 1)
from pelanggan
5. Tampilan TotalPenjualan Tanggal 1, 2, 3, 4, 5, 6, dan 7 Juli 2005. Simpan di variable @Total1, @Total2, @Total3, @Total4, @Total5, @Total6 dan @Total7
Declare @Total1 money, @Total2 money, @Total3 money, @Total4 money, @Total5 money, @Total6 money, @Total7 money
Select @Total1=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/01/2005' and '07/01/2005 23:59:59'
Select @Total2=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'
Select @Total3=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/03/2005' and '07/03/2005 23:59:59'
Select @Total4=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/04/2005' and '07/04/2005 23:59:59'
Select @Total5=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/05/2005' and '07/05/2005 23:59:59'
Select @Total6=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/06/2005' and '07/06/2005 23:59:59'
Select @Total7=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'
Select @Total1 as Tg1, @Total2 as Tg2, @Total3 as Tg3, @Total4 as Tg4, @Total5 as Tg5, @Total6 as Tg6, @Total7 as Tg7
No comments:
Post a Comment