Arsip: Bewara SQL Lagi...

 
user image
more 13 years ago

reminder2k1

Temen aku minta tlg sintax SQL untuk menghitung saldo rekening. Jadi begini: Aku punya table TRekening, TJurnal dan TDetailJurnal bisa didonlot disini http://www.esnips.com/doc/3944dbef-e632-4454-aaff-f594493ee9f2/Jurnal. File Access dengan password 112233. Nah permasalahan spt ini: aku pengen tampilkan laporan buku besar rekening dengan format sebagai berikut http://www.esnips.com/doc/24ce0089-eaa0-4a10-b7f4-2408114cb709/BukuBesar... Aku dah coba dengan syntax SQL sbb: 1. dengan sintax SQL:

DECLARE @TAwal smalldatetime
DECLARE @TAkhir smalldatetime
DECLARE @KodeRekening varchar(13)
SET @TAwal = '2008-01-01'
SET @TAkhir = '2008-01-31'
SET @KodeRekening = '1.111.112.001'
SELECT LAB.NoBukti, LAB.NoTransaksi, LAB.Tanggal, LAB.Keterangan, SUM(LAB.Debet) AS Debet,
SUM(LAB.Kredit) AS Kredit, SUM(LAB.Saldo) AS Saldo
FROM (SELECT '-' AS NoBukti, '-' AS NoTransaksi, @TAwal-1 AS Tanggal, 'SALDO AWAL' AS Keterangan,
0 AS Debet, 0 AS Kredit, 0 AS Saldo
UNION
SELECT '-' AS NoBukti, '-' AS NoTransaksi, @TAwal-1 AS Tanggal, 'SALDO AWAL' AS Keterangan,
0 AS Debet, 0 AS Kredit,
CASE TR.Saldo
  WHEN 'D' THEN SUM(TDJ.Debet - TDJ.Kredit)
  WHEN 'K' THEN SUM(TDJ.Kredit - TDJ.Debet)
END AS Saldo
FROM (TJurnal AS TJ INNER JOIN TDetailJurnal AS TDJ ON TJ.NoBukti=TDJ.NoBukti AND TJ.IDLokasi=TDJ.IDLokasi)
INNER JOIN TRekening AS TR ON TDJ.KodeRekening = TR.KodeRekening
WHERE TDJ.KodeRekening = @KodeRekening
AND TJ.Tanggal < @TAwal
GROUP BY TDJ.KodeRekening, TR.Saldo
UNION
SELECT TJ.NoBukti, TJ.NoTransaksi, TJ.Tanggal, TJ.Keterangan, TDJ.Debet, TDJ.Kredit,
(SELECT SUM(SA1) AS SALDO
FROM (SELECT CASE TR2.Saldo
  WHEN 'D' THEN SUM(TDJ2.Debet - TDJ2.Kredit)
  WHEN 'K' THEN SUM(TDJ2.Kredit - TDJ2.Debet)
END AS SA1
FROM TJurnal AS TJ2 INNER JOIN TDetailJurnal AS TDJ2 ON TJ2.NoBukti = TDJ2.NoBukti AND TJ2.IDLokasi = TDJ2.IDLokasi
INNER JOIN TRekening AS TR2 ON TDJ2.KodeRekening = TR2.KodeRekening
WHERE TDJ2.KodeRekening = @KodeRekening
AND (TJ2.Tanggal <= TJ.Tanggal
OR TJ2.NoBukti <= TJ.NoBukti)
GROUP BY TR2.Saldo) AS SA) AS Saldo
FROM TJurnal AS TJ INNER JOIN TDetailJurnal AS TDJ ON TJ.NoBukti = TDJ.NoBukti AND TJ.IDLokasi = TDJ.IDLokasi
INNER JOIN TRekening AS TR ON TDJ.KodeRekening = TR.KodeRekening
WHERE TDJ.KodeRekening = @KodeRekening
AND TJ.Tanggal >= @TAwal
AND TJ.Tanggal <= @TAkhir) AS LAB
GROUP BY LAB.NoBukti, LAB.NoTransaksi, LAB.Tanggal, LAB.Keterangan
ORDER BY LAB.Tanggal, LAB.NoBukti 
tapi loading minta ampun lambatnya... 2. Kombinasi SQL & syntax program simpan ke temporary table... Sm cara ini pun lambat, walaupun lebih akurat... Nah buat temen2 yg punya ide & solusi supaya ni laporan bisa di sajikan dengan cepat & akurat mohon petunjuknya... Thx sebelumnya... :)
user image
more 13 years ago

reminder2k1

oh iya tambahan... SQL ini aku jalankan pake MSSQL 2000.... Thx b4...
user image
more 13 years ago

saysansay

@Mode Sunda On Xixixixixixixxi kerjaan Tahun Barunna teu acan kelar kasep?Naha iiiih bewara teh teu aya hadiah na? kudu aya DDL tablenya kasep,tong SQL na hungkul.. kumaha keur ngetes.... Sok Sertakeun DDL tablena...mun ayak data na sekalian.....Kebanyakan SubQuery Seh....
user image
more 13 years ago

saysansay

Kasep...tong upload di esnip atuh....aya panjangga na ooey didieu(WebFIlter)..ke wordprees aje,,,,uploapnya jang
user image
more 13 years ago

mas_kofa

kasep ...............ulah make join2 nan geura.....join nyebabken lambat pisan keneh <maap baru belajar sunda>
user image
more 13 years ago

Manz

kasep ... mendingan nganggo link dina where weh wungkul nya ... ulah di joinkeun kawas mas_kofa nyarios ... Tah, anu @Mode Sunda On .. masih keneh pabeulit jeung kadut :mrgreen: Tah sadaya nu bade diajar basa sunda ...mangga nyanggakeun ... jisim kuring sareng si-kasep anu nuju lieur sql sigana tiasa ngabantos ... sanes kitu ... kasep ... :mrgreen:
user image
more 13 years ago

Manz

tambihan ... sakudak sakedik mah tina basa sunda ... :mrgreen: ulah masihan anu lalieur nya' huehehehehehehehe
user image
more 13 years ago

DelphiExpert

momod: mohon menggunakan bahasa yg dapat diterima dan di-mengerti oleh khalayak umum... bahasa Indonesia...
user image
more 13 years ago

deLogic

Bewara SQL menjadi bewara Sunda.. :)
user image
more 13 years ago

reminder2k1

wakakakakak... kok jadi sukuisme kieu??? :mrgreen: @saysansay itu kan aku upload di esnips... tgl donlot disana aja atuh... @mas_kofa & @manz_delphi thx atas sarannya...
more ...
  • Pages:
  • 1
  • 2
Share to
Local Business Directory, Search Engine Submission & SEO Tools FreeWebSubmission.com SonicRun.com