eracomp 22-07-2014 11:59

Newbie Posts: 41

Tanya Logic SQL

Gw mau tanya donk ada yang bisa bantuin ga yah ?

Gw punya 2 tabel

Tabel 1
Tanya Logic SQL

Tabel 2

Butuh bantuan query untuk create tabel dengan result sebagai berikut :
Tanya Logic SQL

AmountSale pada tabel result kalau dijumlahkan per product sama dengan total pada tabel 2. Pemotongan dilakukan pada PO diorder berdasarkan tanggal PO

22-07-2014 11:59

Diubah oleh: eracomp 22-07-2014 15:43

Kutip Balas

Urutan Terlama

anditsung 22-07-2014 14:47

Kaskus Addict Posts: 3,184

Lapor Hansip

lagi belajar2 SQL jg
coba begini

"SELECT Table1.PONo, Tabel1.Date, Table1.ProductId, Table1.Price, Table1.Amount, Table2.AmountSale FROM Table1INNER JOIN Table2 ON Table1.ProductId = Table2.ProductId ORDER By Table1.PONo ASC"

semoga membantu

22-07-2014 11:59

Kutip Balas

eracomp 22-07-2014 15:08

Newbie Posts: 41

Lapor Hansip

Quote:

Original Posted By anditsung►lagi belajar2 SQL jg
coba begini

"SELECT Table1.PONo, Tabel1.Date, Table1.ProductId, Table1.Price, Table1.Amount, Table2.AmountSale FROM Table1INNER JOIN Table2 ON Table1.ProductId = Table2.ProductId ORDER By Table1.PONo ASC"

semoga membantu

Thanks sebelumnya gan

Kalau logicnya dijoin saja masih blom bisa gan, Karena amount salenya akan bernilai sama untuk semua PO. Sedangkan saya maunya PO nya dipotong secara FIFO.

Contoh PO 0001 total amount 10.000 jadi maksimal hanya bisa dipotong 10.000

PO 0002 amount salenya memotong sebanyak 25.000 yang merupakan hasil 35.000 - 10.000 (karena 10.000 sudah dipotong di PO 0001)

22-07-2014 11:59

Kutip Balas

mio.chan 22-07-2014 15:29

Kaskus Maniac Posts: 4,857

Lapor Hansip

Quote:

Original Posted By eracomp►

Thanks sebelumnya gan

Kalau logicnya dijoin saja masih blom bisa gan, Karena amount salenya akan bernilai sama untuk semua PO. Sedangkan saya maunya PO nya dipotong secara FIFO.

Contoh PO 0001 total amount 10.000 jadi maksimal hanya bisa dipotong 10.000

PO 0002 amount salenya memotong sebanyak 25.000 yang merupakan hasil 35.000 - 10.000 (karena 10.000 sudah dipotong di PO 0001)

gua nggak paham gimana maksudnya Tanya Logic SQL

22-07-2014 11:59

Kutip Balas

eracomp 22-07-2014 15:41

Newbie Posts: 41

Lapor Hansip

Quote:

Original Posted By mio.chan►

gua nggak paham gimana maksudnya Tanya Logic SQL

Jadi gini,
gw ambil contoh untuk product AS15 yah.

PO nya kan ada 3 yaitu (dari tabel 1)

PO 00001 --> 10.000 - tgl 1 July
PO 00002 --> 100.000 - tgl 2 july
PO 00006 --> 15.000 - tgl 8 July

Dari tabel 2 diketahui amount sale AS15 = 35.000
Jadi 35.000 itu gw maunya disebar sebagai berikut :

10.000 memotong PO 00001 --> berarti stock di PO 00001 habis karena amountnya 10.000

Karena masih ada sisa 25.000 yang belum dipotong, maka akan dipotongkan ke PO 00002 (karena order by tanggal). Karena stock di PO 00002 ada 100.000 , maka dipotongkan 25.000 ke PO 00002.

22-07-2014 11:59

Kutip Balas

mio.chan 22-07-2014 15:57

Kaskus Maniac Posts: 4,857

Lapor Hansip

Quote:

Original Posted By eracomp►

Jadi gini,
gw ambil contoh untuk product AS15 yah.

PO nya kan ada 3 yaitu (dari tabel 1)

PO 00001 --> 10.000 - tgl 1 July
PO 00002 --> 100.000 - tgl 2 july
PO 00006 --> 15.000 - tgl 8 July

Dari tabel 2 diketahui amount sale AS15 = 35.000
Jadi 35.000 itu gw maunya disebar sebagai berikut :

10.000 memotong PO 00001 --> berarti stock di PO 00001 habis karena amountnya 10.000

Karena masih ada sisa 25.000 yang belum dipotong, maka akan dipotongkan ke PO 00002 (karena order by tanggal). Karena stock di PO 00002 ada 100.000 , maka dipotongkan 25.000 ke PO 00002.

Ok, sekarang gua paham maksudnya Tanya Logic SQL

Query untuk itu sepertinya memungkinkan
Dbms nya kamu pakek apa ?

22-07-2014 11:59

Kutip Balas

eracomp 22-07-2014 16:10

Newbie Posts: 41

Lapor Hansip

Quote:

Original Posted By mio.chan►

Ok, sekarang gua paham maksudnya Tanya Logic SQL

Query untuk itu sepertinya memungkinkan
Dbms nya kamu pakek apa ?

Pakai MS Access

22-07-2014 11:59

Kutip Balas

mio.chan 22-07-2014 16:14

Kaskus Maniac Posts: 4,857

Lapor Hansip

Quote:

Original Posted By eracomp►

Pakai MS Access

yaah gan, diluar harapan gua
maap gan, gua nyerah deh Tanya Logic SQL

22-07-2014 11:59

Kutip Balas

eracomp 22-07-2014 16:20

Newbie Posts: 41

Lapor Hansip

Quote:

Original Posted By mio.chan►

yaah gan, diluar harapan gua
maap gan, gua nyerah deh Tanya Logic SQL

Kalau pakai SQL statement bisa ?

22-07-2014 11:59

Kutip Balas

anditsung 22-07-2014 18:26

Kaskus Addict Posts: 3,184

Lapor Hansip

Menurut gw sih agak susah ya gan. Soalnya mau cek data sebelumnya. Misalnya kalo datanya sama ya bs dilakukan tetapi databasenya agan beda2 urutannya.
Jadi setiap agan ambil data harus compare lagi ke data2 sebelumnya. Hal ini yg gw rasa susah.

22-07-2014 11:59

Kutip Balas

eracomp 22-07-2014 19:26

Newbie Posts: 41

#10

Lapor Hansip

Quote:

Original Posted By anditsung►Menurut gw sih agak susah ya gan. Soalnya mau cek data sebelumnya. Misalnya kalo datanya sama ya bs dilakukan tetapi databasenya agan beda2 urutannya.
Jadi setiap agan ambil data harus compare lagi ke data2 sebelumnya. Hal ini yg gw rasa susah.

Secara Logicnya bisa menggunakan cursor, cuma gw bingung syntax cursor di vba/ access.

Harusnya kalau urutan di tabel pertama kalau menggunakan cursor tidak jadi masalah karena bisa difilter per product id, baru dilooping ke product berikutnya,.

Kira2 gambarannya :

Ambil data pertama dari tabel 2 disimpan ke variabel
product = AS15 , AmountSale = 35.000
Ambil data dari tabel 1 yang productnya = AS15
Looping dari data 1, sampai AmountSale sisanya 0
dengan cara dicheck perbandingan amount vs amount sale

Lakukan hal yang sama sampai semua data di tabel ke 2 habis

FYI, data di tabel 2 tidak perlu disummarize lagi karena sudah merupakan query hasil summary, jadi tiap product cuma muncul 1 kali.

Cuma gw kaga tau gimana bikin statementnya

22-07-2014 11:59

Diubah oleh eracomp 22-07-2014 19:31

Kutip Balas

firsan 23-07-2014 20:49

Aktivis Kaskus Posts: 570

#11

Lapor Hansip

Spoiler for code:

Code:



Dim tdf as DAO.TableDef

Dim db as DAO.Database

Set myDB = CurrentDb



Function createTableDao()

 Set tdf = db.CreateTableDef("tblResult")

 With tdf

  Set fld = .CreateField("PONo",dbText,30)

  .Fields.Append fld

  .Fields.Append .CreateField("Date",dbDate)

  .Fields.Append .CreateField("ProductId",dbLong)

  .Fields.Append .CreateField("Price",dbDouble)

  .Fields.Append .CreateField("Amount",dbLong)

  .Fields.Append .CreateField("AmountSale",dbLong)

  .Fields.Append .CreateField("QtyLeft",dbLong)

  .Fields.Append .CreateField("Value",dbLong)

        End With

 db.TableDefs.Append tdf

 Application.RefreshDatabaseWindow

End Function



Function insertIntoResult(PONo,Date,ProductId,Price,Amount,AmountSale,QtyLeft,Value)

 Set myRS = myDB.OpenRecordset("tblResult")

 myRS.AddNew

 myRS!PONo = PONo

 myRS!Date = Date

 myRS!ProductId = ProductId

 myRS!Price = Price

 myRS!Amount = Amount

 myRS!AmountSale = AmountSale

 myRS!QtyLeft = QtyLeft

 myRS!Value = Value

 myRS.Update

 MyRS.Close

 Set MyRS = Nothing

End Function



Function calculateResult(productId,AmountSale)

 Dim sisaAmountSale as Long

 sisaAmountSale = AmountSale

 Set myRS = myDB.OpenRecordset("SELECT * FROM TABLE1 

        WHERE ProductId =" & productId & " Order By Date ASC")

 myRS.MoveFirst

 Do Until sisaAmountSale = 0

  amount = myRS['Amount']

  If sisaAmountsale >= amount then

   sisaAmountsale = sisaAmountSale - amount;

     amountSale = amount;

     qtyLeft = 0

  ElseIf sisaAmountsale < amount then

      qtyLeft = amount - sisaAmountSale

      amountSale = sisaAmountSale

      sisaAmountSale = 0

  End If



  insertIntoResult(MyRS['PONo'],

    MyRS['Date'],

    MyRS['ProductId'],

    MyRS['Price'],

    MyRS['Amount'],

    amountSale,

    qtyLeft,

    value)

  myRS.MoveNext

 Loop

End Function

Maaf gan ane gk punya msaccess jadi code nya mungkin banyak salah udah lama juga gak pegang access. Namun alurnya adalah, agan bikin table temporary tblResult nanti data dari table1 dipindah ke tblResult untuk kolom
perhitungannya ada di function calculateResult. Nanti tblResult di hapus setelah data sudah selesai ditampilkan, jika ingin melihat data result lagi kudu create table tblResult lagi. Terus Itu logic amount vs amountSale nya mungkin ane masih salah gan kudu di test lagi.

22-07-2014 11:59

Diubah oleh firsan 23-07-2014 20:50

Kutip Balas

eracomp 23-07-2014 21:12

Newbie Posts: 41

#12

Lapor Hansip

Quote:

Original Posted By firsan►

Spoiler for code:

Code:



Dim tdf as DAO.TableDef

Dim db as DAO.Database

Set myDB = CurrentDb



Function createTableDao()

 Set tdf = db.CreateTableDef("tblResult")

 With tdf

  Set fld = .CreateField("PONo",dbText,30)

  .Fields.Append fld

  .Fields.Append .CreateField("Date",dbDate)

  .Fields.Append .CreateField("ProductId",dbLong)

  .Fields.Append .CreateField("Price",dbDouble)

  .Fields.Append .CreateField("Amount",dbLong)

  .Fields.Append .CreateField("AmountSale",dbLong)

  .Fields.Append .CreateField("QtyLeft",dbLong)

  .Fields.Append .CreateField("Value",dbLong)

        End With

 db.TableDefs.Append tdf

 Application.RefreshDatabaseWindow

End Function



Function insertIntoResult(PONo,Date,ProductId,Price,Amount,AmountSale,QtyLeft,Value)

 Set myRS = myDB.OpenRecordset("tblResult")

 myRS.AddNew

 myRS!PONo = PONo

 myRS!Date = Date

 myRS!ProductId = ProductId

 myRS!Price = Price

 myRS!Amount = Amount

 myRS!AmountSale = AmountSale

 myRS!QtyLeft = QtyLeft

 myRS!Value = Value

 myRS.Update

 MyRS.Close

 Set MyRS = Nothing

End Function



Function calculateResult(productId,AmountSale)

 Dim sisaAmountSale as Long

 sisaAmountSale = AmountSale

 Set myRS = myDB.OpenRecordset("SELECT * FROM TABLE1 

        WHERE ProductId =" & productId & " Order By Date ASC")

 myRS.MoveFirst

 Do Until sisaAmountSale = 0

  amount = myRS['Amount']

  If sisaAmountsale >= amount then

   sisaAmountsale = sisaAmountSale - amount;

     amountSale = amount;

     qtyLeft = 0

  ElseIf sisaAmountsale < amount then

      qtyLeft = amount - sisaAmountSale

      amountSale = sisaAmountSale

      sisaAmountSale = 0

  End If



  insertIntoResult(MyRS['PONo'],

    MyRS['Date'],

    MyRS['ProductId'],

    MyRS['Price'],

    MyRS['Amount'],

    amountSale,

    qtyLeft,

    value)

  myRS.MoveNext

 Loop

End Function

Thx gan , ane coba test dulu yah

22-07-2014 11:59

Kutip Balas

firsan 24-07-2014 00:56

Aktivis Kaskus Posts: 570

#13

Lapor Hansip

Quote:

Original Posted By eracomp►
Thx gan , ane coba test dulu yah

iya gan. o iya di kode pertama ternyata ada salah gan, dia gak masukin semua product di table1 ke tblResult setelah kondisi loop sisaAmountSale=0
jadi misal product AS15 di table1: yang diinsert cuma AS15|10000 dan AS15|100000
yang AS15|15000 gak masuk ke tblResult karena kondisi loop sisaAmountSale=0.
Ini bagian revisi codenya, alur nya tetap sama create temporary table tblResult dulu, namun looping tidak berdasarkan kondisi sisaAmountSale.

Spoiler for revisi code:

Code:



Dim tdf as DAO.TableDef

Dim db as DAO.Database

Set myDB = CurrentDb



Public Type KalkulasiField

        SisaAmount As Long

        AmountSale As Long

        QtyLeft As Long

        Value As Long

End Type



Function calculate(amount As Long, amountSale As Long) As KalkulasiField

 Select Case amountSale

   Case Is >= amount

     calculate.SisaAmount = amountSale - amount

      calculate.AmountSale = amount

      calculate.QtyLeft = 0

      //calculate = KalkulasiField

   Case Is < amount

      calculate.QtyLeft = amount - amountSale

         calculate.SisaAmount = 0 

      calculate.AmountSale = amountSale

      //calculate = KalkulasiField

   End Select

End Function



Function generateResult(productId,amountSale)

 Dim sisaAmountSale As Long

 Dim calcField As KalkulasiField

 sisaAmountSale = amountSale

 Set myRS = myDB.OpenRecordset("SELECT * FROM TABLE1 

             WHERE ProductId =" & productId & " Order By Date ASC")

 myRS.MoveFirst

 Do Until MyRS.EOF

  calcField = calculate(MyRS['Amount'],sisaAmountSale)

  sisaAmountSale = calcField.SisaAmount

  insertIntoTableResult(MyRS['PONo'],MyRS['Date'], MyRS['ProductId'], MyRS['Price'], 

                         MyRS['Amount'], calcField.AmountSale, calcField.QtyLeft, 

                         calcField.Value)

  MyRS.MoveNext

 Loop

End Function



Function insertIntoTableResult(PONo,Date,ProductId,Price,Amount,AmountSale,QtyLeft,Value)

 // sama dengan yang di posting code pertama

End Function

Penggunaan :
-> createTableDefs("tblResult") // code di post code pertama
-> generateResult(AS15,35000)

22-07-2014 11:59

Diubah oleh firsan 24-07-2014 01:05

Kutip Balas

eracomp 24-07-2014 16:56

Newbie Posts: 41

#14

Lapor Hansip

Quote:

Original Posted By firsan►
iya gan. o iya di kode pertama ternyata ada salah gan, dia gak masukin semua product di table1 ke tblResult setelah kondisi loop sisaAmountSale=0
jadi misal product AS15 di table1: yang diinsert cuma AS15|10000 dan AS15|100000
yang AS15|15000 gak masuk ke tblResult karena kondisi loop sisaAmountSale=0.
Ini bagian revisi codenya, alur nya tetap sama create temporary table tblResult dulu, namun looping tidak berdasarkan kondisi sisaAmountSale.

Spoiler for revisi code:

Code:



Dim tdf as DAO.TableDef

Dim db as DAO.Database

Set myDB = CurrentDb



Public Type KalkulasiField

        SisaAmount As Long

        AmountSale As Long

        QtyLeft As Long

        Value As Long

End Type



Function calculate(amount As Long, amountSale As Long) As KalkulasiField

 Select Case amountSale

   Case Is >= amount

     calculate.SisaAmount = amountSale - amount

      calculate.AmountSale = amount

      calculate.QtyLeft = 0

      //calculate = KalkulasiField

   Case Is < amount

      calculate.QtyLeft = amount - amountSale

         calculate.SisaAmount = 0 

      calculate.AmountSale = amountSale

      //calculate = KalkulasiField

   End Select

End Function



Function generateResult(productId,amountSale)

 Dim sisaAmountSale As Long

 Dim calcField As KalkulasiField

 sisaAmountSale = amountSale

 Set myRS = myDB.OpenRecordset("SELECT * FROM TABLE1 

             WHERE ProductId =" & productId & " Order By Date ASC")

 myRS.MoveFirst

 Do Until MyRS.EOF

  calcField = calculate(MyRS['Amount'],sisaAmountSale)

  sisaAmountSale = calcField.SisaAmount

  insertIntoTableResult(MyRS['PONo'],MyRS['Date'], MyRS['ProductId'], MyRS['Price'], 

                         MyRS['Amount'], calcField.AmountSale, calcField.QtyLeft, 

                         calcField.Value)

  MyRS.MoveNext

 Loop

End Function



Function insertIntoTableResult(PONo,Date,ProductId,Price,Amount,AmountSale,QtyLeft,Value)

 // sama dengan yang di posting code pertama

End Function

Penggunaan :
-> createTableDefs("tblResult") // code di post code pertama
-> generateResult(AS15,35000)

Gan,

saat ini saya sudah ada logic untuk create tabel resultnya dengan logic inner join tabel 1 dan tabel 2 utk productid yang sama. Nama tabelnya TblStockRinci

Bisa ga Function nya hanya untuk calculate Amountsale dan kemudian diupdate ke tabel tersebut. Untuk field qtyleft dan field value nya tidak perlu karena itu bisa diupdate belakangan setelah semua amountsale terisi.

22-07-2014 11:59

Kutip Balas

firsan 25-07-2014 19:21

Aktivis Kaskus Posts: 570

#15

Lapor Hansip

Maksud agan TblStockRinci hanya dibuat satu kali, lalu nanti tinggal diupdate field amountSale nya setiap kali kita ingin melihatnya?. Mungkin bisa gan, jadi nanti hasil record dari openrecordset(select join table1,table2) dicocokin dengan tblStockRinci berdasarkan PONo, lalu update amountSalenya, cmiiw.

O iya, kalo ane gak salah tanggep, agan bikin inner join table1,table2. Itu nanti untuk product yang belum ada penjualannya bagaimana gan, gak ditampilin ya di TblStockRinci?

Spoiler for revisi calculate:

Code:



Function calculate(amount As Long, amountSale As Long) As Long

 Dim sisaAmount As Long

 Select Case amountSale

 Case Is >= amount

  sisaAmount = amountSale - amount

  calculate = sisaAmount

 Case Is < amount

  calculate = 0

 End Select

End Function

22-07-2014 11:59

Diubah oleh firsan 25-07-2014 20:36