Nye egenskaper i Microsoft SQL 2005 gjør regnskapsprogrammering enkelere
BAKGRUNN
Regnskap anses av mange av å bestå av bilagbunter som man taster inn i systemet og av regnskapsrapporter man får ut av det samme programmet. En av de viktigste rapportene er saldoliste hovedbok. Denne viser saldoer for regnskapets kontoer. Ofte inneholder også rapporten sumkontoer som "Årets resultat" (viser summen av resultatkontoene), "Eiendeler" og "Gjeld og egenkapital" (viser summen av kontoer for eiendeler, egenkapital og gjeld).
TEKNISK LØSNING
Sumkontoene kan befinne seg i en egen tabell eller i kontotabellen. Jeg har her valgt å la de være en del av kontotabellen. For enkelhets skyld kaller jeg kontotabellen kto og bilagstabellen bilag.
SQL 2005 innfører Common Table Expression eller CTE som en videreutvikling av midlertidige tabeller. CTE er spesielt egnet for det som fint kalles rekursiv programmering - dvs programm som kaller deler av seg selv.
Jeg har laget et "view" som kalles ktoSummer. KtoSummer viser både vanlige kontoer og sumkontoer, hver med sin saldo. I "viewet" brukes det en midlertidig tabell, en CTE, som kalles "Rollup". Rollup er en vanlig engelsk betegnelse på sumkontoer.
I CTE er det to spørringer som leverer identiske kolonner, og slår disse sammen med UNION. Den første spørringen kalles ankerspørring. Den andre legger til data til den midlertidige tabellen. Det tredje elementet er en del av join som fortsetter spørringen inntil kontotreet er løpt til ende.
Jeg hadde god hjelp i utdraget av "T-SQL Enhancements in SQL Server 2005". Og jeg syntes hierarkiske spørringer ble mer forståelig av å lese denne artikkelen på theServerside.
Hele teksten under kan kopieres og kjøres i SQL 2005 til Microsoft. Lykke til!
-- lag en liten norsk kontoplan med sumkontoer
CREATE TABLE kto(ID nvarchar(7) PRIMARY KEY, forelder nvarchar(7), ktoNavn nvarchar(15), ktoType nvarchar(8))
INSERT INTO kto VALUES('1910', '1', 'Kasse', '')
INSERT INTO kto VALUES('2700', '2', 'Mva', '')
INSERT INTO kto VALUES('3010', '2090', 'Salg', '')
INSERT INTO kto VALUES('2090', '2', 'Årets resultat', 'sum')
INSERT INTO kto VALUES('1', '0-sjekk', 'Eiendeler', 'sum')
INSERT INTO kto VALUES('2', '0-sjekk', 'Gjeld og EK', 'sum')
INSERT INTO kto VALUES('0-sjekk', '', 'Nullsjekk-kto', 'sum')
-- så en liten bilagsbunt med to kontante salg med moms
CREATE TABLE Bilag(id INT PRIMARY KEY, ktoID nvarchar(7) , beløp FLOAT)
INSERT INTO Bilag VALUES(1, '1910', 100)
INSERT INTO Bilag VALUES(2, '2700', -20)
INSERT INTO Bilag VALUES(3, '3010', -80)
INSERT INTO Bilag VALUES(4, '1910', 1000)
INSERT INTO Bilag VALUES(5, '2700', -200)
INSERT INTO Bilag VALUES(6, '3010', -800)
GO
-- og deretter et view som kan summere det hele
CREATE VIEW ktoSummer
AS
-- sett opp en midlertidig tabell (CTE) som heter rollup
WITH Rollup(id, forelder, ktoNavn, ktotype, saldo)
AS
(
-- Intitier spørring¨-
-- hent alle kontoene, også de som ikke har posteringer, og knytt disse
-- til bilagene
SELECT kto.id, kto.forelder, ktoNavn, kto.ktoType, sum(bilag.beløp) as saldo
FROM kto
left outer join bilag on kto.id = bilag.ktoID
group by kto.id, kto.forelder, kto.ktoNavn, kto.ktoType
UNION ALL
-- og repeter spørring
SELECT kto.id, kto.forelder, kto.ktoNavn, kto.ktoType, rollup.saldo
FROM kto
JOIN Rollup
-- til det ikke finnes fler forelder
ON kto.id = rollup.forelder
)
-- og summer og vis det hele når viewet blir kalt.
SELECT id, forelder, ktotype, ktoNavn, SUM(saldo) as saldo
FROM Rollup
GROUP BY ID, forelder, ktotype, ktoNavn
GO
-- Så kommer testen på om vi har klart å gjøre det riktig!
-- vis balansen sortert etter id og i fallende rekkefølge
select * from ktoSummer where id <'3'order by id DESC -- vis resultat og balanse sortert etter id og i fallende rekkefølge select * from ktoSummer order by id DESC -- Dette så bra ut! -- Så da er det bare å rydde opp. drop table kto drop table bilag drop view ktoSummer
Regnskap anses av mange av å bestå av bilagbunter som man taster inn i systemet og av regnskapsrapporter man får ut av det samme programmet. En av de viktigste rapportene er saldoliste hovedbok. Denne viser saldoer for regnskapets kontoer. Ofte inneholder også rapporten sumkontoer som "Årets resultat" (viser summen av resultatkontoene), "Eiendeler" og "Gjeld og egenkapital" (viser summen av kontoer for eiendeler, egenkapital og gjeld).
TEKNISK LØSNING
Sumkontoene kan befinne seg i en egen tabell eller i kontotabellen. Jeg har her valgt å la de være en del av kontotabellen. For enkelhets skyld kaller jeg kontotabellen kto og bilagstabellen bilag.
SQL 2005 innfører Common Table Expression eller CTE som en videreutvikling av midlertidige tabeller. CTE er spesielt egnet for det som fint kalles rekursiv programmering - dvs programm som kaller deler av seg selv.
Jeg har laget et "view" som kalles ktoSummer. KtoSummer viser både vanlige kontoer og sumkontoer, hver med sin saldo. I "viewet" brukes det en midlertidig tabell, en CTE, som kalles "Rollup". Rollup er en vanlig engelsk betegnelse på sumkontoer.
I CTE er det to spørringer som leverer identiske kolonner, og slår disse sammen med UNION. Den første spørringen kalles ankerspørring. Den andre legger til data til den midlertidige tabellen. Det tredje elementet er en del av join som fortsetter spørringen inntil kontotreet er løpt til ende.
Jeg hadde god hjelp i utdraget av "T-SQL Enhancements in SQL Server 2005". Og jeg syntes hierarkiske spørringer ble mer forståelig av å lese denne artikkelen på theServerside.
Hele teksten under kan kopieres og kjøres i SQL 2005 til Microsoft. Lykke til!
-- lag en liten norsk kontoplan med sumkontoer
CREATE TABLE kto(ID nvarchar(7) PRIMARY KEY, forelder nvarchar(7), ktoNavn nvarchar(15), ktoType nvarchar(8))
INSERT INTO kto VALUES('1910', '1', 'Kasse', '')
INSERT INTO kto VALUES('2700', '2', 'Mva', '')
INSERT INTO kto VALUES('3010', '2090', 'Salg', '')
INSERT INTO kto VALUES('2090', '2', 'Årets resultat', 'sum')
INSERT INTO kto VALUES('1', '0-sjekk', 'Eiendeler', 'sum')
INSERT INTO kto VALUES('2', '0-sjekk', 'Gjeld og EK', 'sum')
INSERT INTO kto VALUES('0-sjekk', '', 'Nullsjekk-kto', 'sum')
-- så en liten bilagsbunt med to kontante salg med moms
CREATE TABLE Bilag(id INT PRIMARY KEY, ktoID nvarchar(7) , beløp FLOAT)
INSERT INTO Bilag VALUES(1, '1910', 100)
INSERT INTO Bilag VALUES(2, '2700', -20)
INSERT INTO Bilag VALUES(3, '3010', -80)
INSERT INTO Bilag VALUES(4, '1910', 1000)
INSERT INTO Bilag VALUES(5, '2700', -200)
INSERT INTO Bilag VALUES(6, '3010', -800)
GO
-- og deretter et view som kan summere det hele
CREATE VIEW ktoSummer
AS
-- sett opp en midlertidig tabell (CTE) som heter rollup
WITH Rollup(id, forelder, ktoNavn, ktotype, saldo)
AS
(
-- Intitier spørring¨-
-- hent alle kontoene, også de som ikke har posteringer, og knytt disse
-- til bilagene
SELECT kto.id, kto.forelder, ktoNavn, kto.ktoType, sum(bilag.beløp) as saldo
FROM kto
left outer join bilag on kto.id = bilag.ktoID
group by kto.id, kto.forelder, kto.ktoNavn, kto.ktoType
UNION ALL
-- og repeter spørring
SELECT kto.id, kto.forelder, kto.ktoNavn, kto.ktoType, rollup.saldo
FROM kto
JOIN Rollup
-- til det ikke finnes fler forelder
ON kto.id = rollup.forelder
)
-- og summer og vis det hele når viewet blir kalt.
SELECT id, forelder, ktotype, ktoNavn, SUM(saldo) as saldo
FROM Rollup
GROUP BY ID, forelder, ktotype, ktoNavn
GO
-- Så kommer testen på om vi har klart å gjøre det riktig!
-- vis balansen sortert etter id og i fallende rekkefølge
select * from ktoSummer where id <'3'order by id DESC -- vis resultat og balanse sortert etter id og i fallende rekkefølge select * from ktoSummer order by id DESC -- Dette så bra ut! -- Så da er det bare å rydde opp. drop table kto drop table bilag drop view ktoSummer
