Kysymys: Tehdessäni kirjanpitoa minulla on tarve kuukauden lopuksi laskea kustannuspaikkakohtaisesti kuukauden aikana myyntitilille kirjatuista myynneistä provisiot jokaiselle kustannuspaikalle.Lähtötiedot ovat seuraavat:
Vastaus: Asteri kirjanpito-ohjelman versiossa 4.5.2006 alkaen on mahdollisuus tehdä SQL-kyselyitä kirjaustilassa olevaan päiväkirjaan. SQL kyselyn avulla voidaan laatia raportteja kirjaustilassa olevan päiväkirjan vienneistä. Esittämäsi toive on toteutettavissa SQL kyselyn avulla.
Myynti kustannuspaikoittain saadaan kyselyllä:
SELECT kustannuspaikka, SUM( iif( kredit = '3000',summa,0) - iif( debet = '3000',summa,0) ) as Myynti FROM [%93] WHERE (debet = '3000' OR kredit = '3000') AND kustannuspaikka <> '' GROUP BY kustannuspaikka ORDER BY kustannuspaikka |
Provisio kustannuspaikoittain saadaan kyselyllä
SELECT kustannuspaikka, SUM( iif( kredit = '3000',summa,0) - iif( debet = '3000',summa,0) ) as Myynti, IIF( Myynti <5000 , Myynti*0.02 , IIF( Myynti < 10000 , Myynti*0.04 , Myynti*0.08 )) AS Provisio FROM [%93] WHERE (debet = '3000' OR kredit = '3000') AND kustannuspaikka <> '' GROUP BY kustannuspaikka ORDER BY kustannuspaikka |
Automaattikirjauksen tekemiseksi provisiolaskelmaan lisätään kenttiä niin, että ne vastaavat päiväkirjan kirjaustilan kenttiä:
SELECT "" AS Tosite, "" AS pvm, "" AS Laji, kustannuspaikka, "" AS Lsno, "" AS Selite1, "" AS Selite2, "" AS DebetTili, "" AS KreditTili, IIF( Myynti <5000 , Myynti*0.02 , IIF( Myynti < 10000 , Myynti*0.04 , Myynti*0.08 )) AS Provisio, SUM( iif( kredit = '3000',summa,0) - iif( debet = '3000',summa,0) ) as Myynti FROM [%93] WHERE (debet = '3000' OR kredit = '3000') AND kustannuspaikka <> '' GROUP BY kustannuspaikka ORDER BY kustannuspaikka |
Täydentämällä ylläolevaa kyselyä lisäkenttiin saadaan valmiiksi haettua sopivat arvot:
SELECT (select max(Tositenumero)+1 FROM [%93] ) AS Tosite, (select max([Paivamaara]) FROM [%93] ) AS pvm, "" AS Laji, kustannuspaikka, "" AS Lsno, "Provisio " & (select Selite from Kustannuspaikat IN 'c:\winkp\malli\kpaikat.oma' WHERE kustannuspaikat.tunnus = [%93].Kustannuspaikka) AS Selite1, "Myynti " & format(myynti ,"0.00") & "e, provisio " & IIF( myynti <5000 ,'2%' , IIF( myynti <10000 , '4%', '8%' )) AS Selite2, "4000" AS DebetTili, "2430" AS KreditTili, IIF( Myynti <5000 , Myynti*0.02 , IIF( Myynti < 10000 , Myynti*0.04 , Myynti*0.08 )) AS Provisio, SUM( iif( kredit = '3000',summa,0) - iif( debet = '3000',summa,0) ) as Myynti FROM [%93] WHERE (debet = '3000' OR kredit = '3000') AND kustannuspaikka <> '' GROUP BY kustannuspaikka ORDER BY kustannuspaikka |
Tositenumero haetaan etsimällä päiväkirjasta suurin tositenumero ja lisäämällä siihen
(select max(Tositenumero)+1 FROM [%93] ) AS Tosite,
Päivämääräksi haetaan päiväkirjasta suurin käytetty päivämäärä:
(select max([Paivamaara]) FROM [%93] ) AS pvm,
.. tai päivämäärä voidaan parametrisoida, jolloin ohjelma kysyy päivämäärän ennen kyselyn suorittamista:
"%1" AS pvm,
.. päivämäärä vodaan antaa vakiona kyselyssä lainausmerkkien sisällä:
"4.5.2006" AS pvm,
Tositelaji jää tässä kyselyssä tyhjäksi.
"" AS Laji,
... jos tositelaji on käytössä, lainausmerkkien sisään voidaan kirjoittaa haluttu tositelaji:
"" AS Laji,
Kustannuspaikkakenttään valitaan kustannuspaikan tunniste:
kustannuspaikka,
... kustannuspaikkakenttä voidaan jättää myös tyhjäksi:
"" AS kustp,
Laskunnumero jää tyhjäksi:
"" AS Lsno,
Selite1 kentän alkuun tulee vakioteksti Provisio ja sen jälkeen haetaan kustannuspaikan selite kustannuspaikkatietokannasta. Kustannuspaikkatietokanta on yrityksen kirjanpitohakemistossa kpaikat.oma niminen tietokanta, ja kyselyyn täytyy kirjoittaa koko hakemistopolku (esimerkissä c:\winkp\malli\kpaikat.oma täytyy korvata sen yrityksen hakemistopolulla, jonka kirjanpitoa olet tekemässä)
"Provisio " & (select Selite from Kustannuspaikat IN 'c:\winkp\malli\kpaikat.oma' WHERE kustannuspaikat.tunnus = [%93].Kustannuspaikka) AS Selite1,
... tai Selite1 kentässä voidaan käyttää myös vakiotekstiä
"Provisio" AS Selite1,
... tai Selite1 kentässä voidaan käyttää myös vakiotekstiä ja kustannuspaikan tunnistetta
"Provisio" & kustannuspaikka AS Selite1,
Selite2 kenttään haetaan vakioteksti Myynti jonka jälkeen tulee kustannuspaikan myynti ja provisioprosentti.
"Myynti " & format(myynti ,"0.00") & "e, provisio " & IIF( myynti <5000 ,'2%' , IIF( myynti <10000 , '4%', '8%' )) AS Selite2,
... tai selite2 kenttä voidaan jättää tyhjäksi
"" AS Selite2,
debet ja kredit tilit syötetään lainausmerkkien sisään:
"4000" AS DebetTili, "2430" AS KreditTili,
... jos käytetään vain kulutiliä (toispuoleista kirjausta) laita puuttuvan tilin tilalle neljä tähteä:
"4000" AS DebetTili, "****" AS KreditTili,
Summaksi lasketaan myyynnistä kaavan mukainen prosentti. Apuna käytetään IIF -funktiota, jossa suluissa on ehto, tosi-arvo ja epätosiarvo. Tässä esimerkissä
IIF( Myynti <5000 , Myynti*0.02 , IIF( Myynti < 10000 , Myynti*0.04 , Myynti*0.08 )) AS Provisio,
Kustannuspaikan myynti on tämän kyselyn tärkein tieto. Kustannuspaikan myynniksi lasketaan ne rivit, joissa debet- tai kredit-tilinä on 3000:
SUM( iif( kredit = '3000',summa,0) - iif( debet = '3000',summa,0) ) as Myynti
Viennit haetaan kirjaustilassa olevasta päiväkirjassta
FROM [%93]
Huomoidaan vain ne rivit, joissa debet tai kredit tili on 3000 ja kustannuspaikka on annettu
WHERE (debet = '3000' OR kredit = '3000') AND kustannuspaikka <> ''
Summataan kustannuspaikan Myynnit yhteen GROUP BY -lauseella:
GROUP BY kustannuspaikka
Ja näytetään kustannuspaikat aakkosjärjestyksessä:
ORDER BY kustannuspaikka