Kysymys: Tehdessäni kirjanpitoa minulla on tarve kuukauden lopuksi laskea kustannuspaikkakohtaisesti kuukauden aikana myyntitilille kirjatuista myynneistä provisiot jokaiselle kustannuspaikalle. Lähtötiedot ovat seuraavat:
- Tarvitsen raportin, jossa näkyy kustannuspaikoittain kuukauden aikana myyntitilille kirjattu myynti
- Myynnit on kirjattu tilille 3000
- Yhdellä kustannuspaikalla voi olla yksi tai useampia kirjauksia myyntitilille kuukauden aikana
- Lisäksi tarvitsen provisiolaskelman myyntien perusteella.
- Provisio lasketaan kullekin kustannuspaikalle kuukauden myynnin perusteella.
- Provision laskenta on kolmiportainen, siten, että myynnin ollessa alle 5.000 euroa provisio on 2%, 5.000-10.000 euroa provisio on 4% ja yli 10.000 euron myynnistä provisio on 8%.
Provisioista tulisi saada laskelma, josta käy ilmi kustannuspaikka, sen myynti ja provisio. Lisäksi tarvitaan automaattikirjaus, jolla voin kirjata kustannuspaikkakohtaiset provisiot kirjanpitoon.
Automaattikirjauksen tulisi tehdä päiväkirjan loppuun tosite kustannuspaikkalaskelman perusteella kirjaten jokaisen kustannuspaikan omana rivinään.
Vastaus: Asteri kirjanpito-ohjelmassa on mahdollisuus tehdä SQL-kyselyitä kirjaustilassa olevaan päiväkirjaan.
SQLkyselyn avulla voidaan laatia raportteja kirjaustilassa olevan päiväkirjan vienneistä. Esittämäsi toive on toteutettavissa SQLkyselyn avulla.
Esimerkki, miten kustannuspaikkojen myynnit on kirjattu
Valitse tulosta /Kyselytilastot (SQL)/Luo kyselyitä
Tee provisiolaskelmasta automaattikirjaus seuraavasti:
Myynti kustannuspaikoittain
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
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
Automaattikirjaus provisiolaskelmasta
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
Automaattikirjaus provisiolaskelmasta täysin tiedoin
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
Kuva: kustprov2.gif
Seuraavassa selitetään lyhyesti ylläolevassa kyselyssä olevat rivit:
- Vapaan tositenumeron hakeminen
- 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.2021” 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:
”” ASkustp,
- 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,
- 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 GROUPBY -lauseella:
GROUP BY kustannuspaikka
- Ja näytetään kustannuspaikat aakkosjärjestyksessä:
ORDER BY kustannuspaikka
