Esimerkki SQL Kyselystä: Katelaskelma
(kun tuotekortisto linkitetty laskutustietokantaan)

SQL kyselyitä tehtäessä tulee toisinaan vastaan tarve hakea tietoja kahdesta eri tietokannasta. Esimerkiksi katelaskelmia tehtäessä tarvitaan tuotteiden myyntitiedot haetaan laskutustietokannan (*.wls) tuoterivit -taulusta ja tuotteiden ostohinnat haetaan tuotekortiston (*.wtk) tuotteet-taulusta.

Tietojen hakemiseen kahdesta eri taululusta käytetään JOIN komentoa. JOIN komento ei kuitenkaan toimi kahden tietokannan välillä, vaan vaatii että taulut ovat samassa tietokannassa. Taulut saadaan samaan tietokantaan linkittämällä yhden tietokannan taulu toiseen tietokantaan Microsoft Access 2000 -tietokantaohjelman avulla.

Vaihe 1: Linkitä tuotekortisto laskutustietokantaan

Linkitä tuotekortisto laskutustietokantaan artikkelin SQL kyselyt: Asiakas- ja tuotekortiston linkittäminen laskutustietokantaan (JOIN kyselyissä) ohjeiden mukaan.

Vaihe 2: Rakenna katelaskelmia

• Rakenna katelaskelma valitsemalla laskunsyöttötilassa tilasto/kyselytilastot (sql)/luo kyselyitä
• Valitse vasemman yläkulman pudotuslistalta määrittämätön kysely
• Kopioi alla olevissa esimerkeissä kysely sille varattuun tilaan ja suorita kysely napsauttamalla [Suorita] painiketta
• Jos kysely ilmoittaa "Virhe kyselyssä: Microsoft Jet -tietokantamoduuli ei löydä syötetaulukkoa tai -kyselyä "TUOTTEET". Varmista, että se on luotu ja että kirjoitit sen nimen oikein" on todennäköinen syy siinä, että et ole tehnyt vaiheen 1 mukaista tuotekortiston linkittämistä laskutustietokantaan. Jos et saa linkitystä tehtyä (esim. käytössäsi ei ole Access 2000 tietokantaohjelmaa) ei tämän sivun ohjeitakaan voi hyödyntää. Mutta ei hätää, lue tällöin artikkeli Esimerkki SQL kyselyn rakentamisesta: katelaskelma tuotteittain jossa käytetään alikyselyä ostohintojen hakemiseen tuotekortistosta.

Katelaskelma tuotteittain

Rakennetaan kysely paloittain. Tehdään ensin kysely tuoterivit kantaan, jotta nähdään, minkä nimisiä kenttiä siellä on:

SELECT * FROM TUOTERIVIT 

Seuraavaksi rajataan tuoterivejä päivämäärän mukaan. Kirjoita kysymys 1 kohtaan alkupäivä ja kysymys 2 kohtaan loppupäivä ja kirjoita kysymysten vastauksiksi jotkut sopivat päivät. Kopioi seuraavat rivit kyselyksi ja suorita kysely:

SELECT * FROM TUOTERIVIT 
WHERE CDATE(PÄIVÄMÄÄRÄ) BETWEEN DATEVALUE('%1') AND DATEVALUE('%2')

Tuoterivejä ei kuitenkaan käytetä sellaisenaan, vaan lasketaan tuotenumeroittain tuotteiden myyty kappalemäärä ja myyntihinnat.

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

Otetaanpa tähän väliin esimerkki tietojen hakemisesta kahdesta taulusta JOIN komennon avulla:

SELECT TUOTERIVIT.TUOTENUMERO, TUOTTEET.NIMIKE 
FROM TUOTERIVIT 
INNER JOIN TUOTTEET 
ON TUOTERIVIT.TUOTENUMERO = TUOTTEET.TUOTENUMERO

Ylläolevassa kyselyssä haetaan tuotenumero tuoterivit taulusta ja nimike tuotteet taulusta. kahden taulun tietueet yhdistetään alimman rivin ohjeen ON TUOTERIVIT.TUOTENUMERO = TUOTTEET.TUOTENUMERO mukaan. Kenttien nimen alussa käytetylle taulun nimelle (TUOTERIVIT. tai TUOTTEET.) voidaan antaa lyhyt alias, jolloin kyselyistä tulee lyhyempiä. Tässä esimerkissä TUOTERIVIT saa aliaksen A ja TUOTTEET aliaksen B:

SELECT A.TUOTENUMERO, B.NIMIKE 
FROM TUOTERIVIT A
INNER JOIN TUOTTEET B
ON A.TUOTENUMERO = B.TUOTENUMERO

Jatketaan katelaskelman tekemistä. Aiempana tehtyä tuotteiden myyntimäärät laskevaa kyselyä käytetään seuraavassa alikyselynä ja sen saa aliaksen A, jolloin kyselyn palauttamiin kenttiin viitataan A.kentännimi. Tuotekortisto saa puolestaan aliaksen B, jolloin tuotekortiston kenttiin viitataan B.kentännimi.

SELECT A.TUOTENUMERO, B.NIMIKE , B.OSTOHINTA, A.[MYYTY KPL], A.[MYYTY VEROTON], A.[MYYTY SIS ALV] 
FROM (
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
)  A
INNER JOIN TUOTTEET B ON A.TUOTENUMERO = B.TUOTENUMERO 

Ylläolevassa kysely palauttaa tarvittavat tiedot tuotteiden katteen laskemiseksi. Seuraavaksi jääkin vain tehtäväksi katteiden laskeminen, joista seuraavassa muutamia esimerkkejä.

Verollinen summa saadaan muutettua verottomaksi kertomalla summa 100/122:lla. Esimerkiksi verollinen ostohinta saadaan muutetuksi verottomaksi B.OSTOHINTA/(100/122)

B.OSTOHINTA*(100/122) AS [veroton ostohinta]

Näissä esimerkeissä ostohinta sisältää 22% veroa, joka vähennetään kertomalla ostohinta 100/122:lla. Jos tuotekortistossasi ostohinnat ovat verottomat, jätä pois näistä kaavoista alv:n vähentävä laskutoimitus, jolloin ostohinta B.OSTOHINTA*(100/122) muuttuu muotoon B.OSTOHINTA, ts ota pois merkit *(100/122).

Verottomat kate-eurot saadaan laskettua laskemalla ensin, mikä oli myyntierän hankintahinta (myyty määrä * ostohinta) ja vähentämällä näin saatu summa myyntihinnasta

A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)) AS [veroton hankintahinta]
A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))) AS [kate-eurot yhteensä]

Kate-eurot saadaan siistittyä FORMAT funktion avulla

FORMAT(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))) ,'### ### ##0.00') AS [kate-eurot yht]

Kate-eurot per myyty tuote saadaan jakamalla ylläoleva luku myytyjen tuotteiden kappalemäärällä

(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))))/A.[MYYTY KPL] AS [kate-eurot per myyty tuote]

Kate-eurot saadaan siistittyä 2 desimaalin tarkkuuteen FORMAT funktion avulla:

FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))))/A.[MYYTY KPL],'### ##0.00') AS [kate per tuote]

Kateprosentti saadaan jakamalla kate-eurot tuote-erän myyntihinnalla

(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)))) / A.[MYYTY VEROTON] * 100 AS [Kate%]

Kateprosentin desimaaleja saadaan siistittyä 2 desimaalin tarkkuudella FORMAT -funktion avulla:

FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)))) / A.[MYYTY VEROTON] ,'0.00%') AS [KateP%]

Kysely täydentyy katteita laskevilla riveillä:

SELECT A.TUOTENUMERO, B.NIMIKE , B.OSTOHINTA, A.[MYYTY KPL], A.[MYYTY VEROTON], A.[MYYTY SIS ALV], 
B.OSTOHINTA*(100/122) AS [veroton ostohinta],
A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)) AS [veroton hankintahinta],
A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))) AS [kate-eurot yhteensä],
FORMAT(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))) ,'### ### ##0.00') AS [kate-eurot yht],
(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))))/A.[MYYTY KPL] AS [kate-eurot per myyty tuote],
FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))))/A.[MYYTY KPL],'### ##0.00') AS [kate per tuote],
(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)))) / A.[MYYTY VEROTON] * 100 AS [Kate%],
FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)))) / A.[MYYTY VEROTON] ,'0.00%') AS [KateP%]
FROM (
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
)  A
INNER JOIN TUOTTEET B ON A.TUOTENUMERO = B.TUOTENUMERO 

Katetta laskettaessa ylläolevista kentistä voi jättää varsinaiseen kyselyyn vain tarvittavat, esimerkiksi:

SELECT A.TUOTENUMERO, B.NIMIKE , A.[MYYTY KPL], A.[MYYTY VEROTON], 
B.OSTOHINTA*(100/122) AS [veroton ostohinta],
FORMAT(A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))) ,'### ### ##0.00') AS [kate-eurot yht],
FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122))))/A.[MYYTY KPL],'### ##0.00') AS [kate per tuote],
FORMAT((A.[MYYTY VEROTON] - (A.[MYYTY KPL] * (B.OSTOHINTA*(100/122)))) / A.[MYYTY VEROTON] ,'0.00%') AS [KateP%]
FROM (
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
)  A
INNER JOIN TUOTTEET B ON A.TUOTENUMERO = B.TUOTENUMERO

Ja näin olemme lopulta saaneet katelaskelman määritettyä.