Tässä artikkelissa rakennetaan SQL kysely, joka tekee katelaskelman tuotteista annetulta jaksolta.
Tilastointi tehdään laskutustietokannassa (*.wls) olevasta tuoterivit -taulusta ja tuotekortistosta (*.wtk)
Siirry laskutusohjelmassa laskunsyöttö työvaiheeseen. Valitse tilasto / kyselytilastot / luo kyselyitä. Valitse vasemman yläkulman pudotusvalikosta määrittämätön kysely. Kopioi tässä esimerkissä olevat kyselyt vuorollaan kyselyikkunaan testataaksesi kyselyitä.
Aloitetaan kyselyn rakentaminen yksinkertaisella lausella jolloin näemme mitä kenttiä tuoterivit taulussa on (kysely kohdistuu aina oletuksena laskutustietokantaan *.WLS, jolloin sitä ei tarvitse erikseen mainita kyselyssä):
SELECT * FROM TUOTERIVIT
Kysely palauttaa kaikki laskutetut tuoterivit, joista haluamme mukaan vain tietyn jakson aikana laskutetut tuoterivit, joten lisäämme kyselyn loppuun rajaavan ehdon
SELECT * FROM TUOTERIVIT
WHERE CDATE(PÄIVÄMÄÄRÄ)
BETWEEN DATEVALUE('1.2.2005') AND DATEVALUE('28.2.2005')
Päivämäärien käsittelyssä ylläolevassa kysymyksessä jouduimme käyttämään funktioita cdate (tietokannan päivämäärän muuttamisessa) ja datevalue (syötetietojen muuttamisessa), jotta päivämääriä vertailtaisiin päivämäärinä eikä tekstinä (aakkosjärjestyksessä vertaamalla esim 2.12.2005 olisi ennen 3.1.2004).
Kyselyssä olevien kiinteiden päivämäärien sijaan voimme käyttää muuttujia %1 alkupäivänä ja %2 loppupäivänä, jolloin ottaessamme tilastoa, ohjelma kysyy alku- ja loppupäivät.
SELECT * FROM TUOTERIVIT
WHERE CDATE(PÄIVÄMÄÄRÄ)
BETWEEN DATEVALUE('%1') AND DATEVALUE('%2')
Kysely palauttaa jokaisen laskutetun tuoterivin annetulta jaksolta. Tarvitsemme kuitenkin kustakin myydystä tuotteesta vain yhden rivin ja sille laskettuna, paljonko tuotetta on myyty ja millä summalla. Näiden tietojen laskemisessa käytämme SQL:n aggregaattifunktiota SUM, joka laskee valitun kentän yhteen. Aggregaattifunktioita käytettäessä kysely kuitenkin muuttuu rajoitetummaksi: kysely voi palauttaa vain Aggregaattifunktioiden tuloksia ja ryhmittelytietoja.
SELECT TUOTENUMERO,
SUM(MÄÄRÄ) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT
WHERE CDATE(PÄIVÄMÄÄRÄ)
BETWEEN DATEVALUE('%1') AND DATEVALUE('%2')
GROUP BY TUOTENUMERO
Ylläolevassa kyselyssä tulikin paljon uutta, käydään
aluksi läpi mitä rivillä SUM([NETTO
MK]) AS [MYYTY VEROTON] oikein tapahtuu:
SUM([NETTO
MK]) sum
laskee yhteen tuoterivit -taulun netto mk -kentän. Koska netto
mk -kentän nimessä on välilyönti, kentän nimi
pitää kirjoittaa [hakasulkujen] sisään.
AS [MYYTY VEROTON] as
nimeää kentän uudelleen nimelle myyty veroton ja
koska kentän uudessakin nimessä on välilyönti, sekin
pitää kirjoittaa [hakasulkujen] sisään.
Ylläolevassa kyselyssä viimeinen rivi GROUP
BY TUOTENUMERO käskee laskemaan
summat kumulatiivisesti tuotenumeron mukaan.
Seuraavaksi haemme tuotteen ostohinnan tuotekortistosta. Tuotekortisto on tuotekortisto-tietokannassa (yleensä tuote.wtk) oleva tuotteet niminen taulu. Ennen varsinaista tuotehinnan hakua käydään läpi muutama yksinkertaisempi kysely. Toiseen tietokantaan kuin oletuksena olevaan laskutustietokantaan tapahtuva haku tehdään yksilöimällä tietokannan kyselyssä IN tietokannan nimi komennolla. Tuotekortiston ollessa kyseessä voitaisiin käyttää muotoa IN 'C:\WINLS\SAMPLES\TUOTE.WTK'. Tuotekoriston nimen tilalla käytetään kuitenkin muuttujaa %92, joka hakee yrityksen perustietoihin määritetyn tuotekortiston tietokannan nimen, jolloin saman kyselyn voi siirtää toiselle yritykselle, eikä tarvitse muuttaa tuotekortiston hakemistopolkua. Tuotekortistosta kaikki tiedot hakeva kysely saa muodon:
SELECT * FROM TUOTTEET IN '%92'
Nyt tarvitsemme kuitenkin vain tuotteen ostohintaa, jolloin kyselystä tulee seuraava:
SELECT OSTOHINTA FROM TUOTTEET IN '%92'
Ja nyt palaamme alkuperäiseen kyselyyn, johon lisäämme tuotteen ostohinnan hakevan kyselyn:
SELECT TUOTENUMERO,
(SELECT OSTOHINTA FROM TUOTTEET b IN '%92'
WHERE a.TUOTENUMERO = b.TUOTENUMERO) AS OSTOHINTA,
SUM(MÄÄRÄ) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT a
WHERE CDATE(PÄIVÄMÄÄRÄ)
BETWEEN DATEVALUE('%1') AND DATEVALUE('%2')
GROUP BY TUOTENUMERO
Ja loppu onkin sitten matematiikkaa, punasinisillä riveillä lasketaan katetta:
SELECT TUOTENUMERO,
(SELECT OSTOHINTA FROM TUOTTEET b IN '%92' WHERE a.TUOTENUMERO = b.TUOTENUMERO)
AS OSTOHINTA,
SUM(MÄÄRÄ) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV] ,
((SELECT OSTOHINTA FROM TUOTTEET b IN '%92'
WHERE a.TUOTENUMERO = b.TUOTENUMERO)*(100/122)) AS [Veroton ostohinta kpl],
((SELECT OSTOHINTA FROM TUOTTEET b IN '%92'
WHERE a.TUOTENUMERO = b.TUOTENUMERO)*(100/122))* SUM(MÄÄRÄ)
AS [ostettu nto],
SUM([NETTO MK])-(((SELECT OSTOHINTA FROM TUOTTEET
b IN '%92' WHERE a.TUOTENUMERO = b.TUOTENUMERO)*(100/122))* SUM(MÄÄRÄ))
AS [Kate euroa yht], (SUM([NETTO
MK])-(((SELECT OSTOHINTA FROM TUOTTEET b IN '%92' WHERE a.TUOTENUMERO =
b.TUOTENUMERO)*(100/122))* SUM(MÄÄRÄ)) ) / SUM(MÄÄRÄ)
AS [kate euroa kpl],
((SUM([NETTO MK])-(((SELECT OSTOHINTA FROM
TUOTTEET b IN '%92' WHERE a.TUOTENUMERO = b.TUOTENUMERO)*(100/122))* SUM(MÄÄRÄ)))/(((SELECT
OSTOHINTA FROM TUOTTEET b IN '%92' WHERE a.TUOTENUMERO = b.TUOTENUMERO)*(100/122))*
SUM(MÄÄRÄ)) )*100 AS [Kate%],
FROM TUOTERIVIT a WHERE CDATE(PÄIVÄMÄÄRÄ) BETWEEN
DATEVALUE('%1') AND DATEVALUE('%2')
GROUP BY TUOTENUMERO
Ylläolevassa esimerkissä jäi vielä askarruttamaan se, voisiko tuotteen ostohinnan saada jotenkin helpommin haettua tuotekortistosta vain kertaalleen ja käyttää hakutulosta tämän jälkeen muuttujanomaisesti, jotta sitä ei tarvitsisi toistaa kerta toisensa jälkeen ylläolevassa kyselyssä. Mutta tämän mietintä jää myöhemmäksi. Nyt hiihtämään!