Intrebari generale

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Thu, 14 Jun 2007 09:10:40 GMT

este perfect! multumesc mult!

Re: Modalitate de generare a "Jurnalului de cumparari"...

rsocol — Thu, 14 Jun 2007 08:26:35 GMT

Hai să vedem, care-i problema: tu vrei o "chestie" (query/procedură/funcţie) care să-ţi dea soldul unui cont la o anumită dată (să zicem că e vorba de o singură valoare, dacă e debitor cu +, dacă e creditor cu -), deci:

CREATE FUNCTION SoldCont(@Cont varchar(20), @Data smalldatetime)
RETURNS money AS BEGIN
...

şi ne interesează să luăm soldul iniţial:

...
DECLARE @Sold money
SELECT @Sold=SoldDebitor-SoldCreditor FROM SolduriInitiale
WHERE An=YEAR(@Data) AND Cont=@Cont
...

(atenţie: dacă nu găseşte contul respectiv în soldurile iniţiale, atunci în variabila @Sold rămâne NULL)

apoi vrem să adunăm rulajul debitor, adică valoarea înregistrărilor contabile care au contul respectiv pe debit, de la începutul anului până la data respectivă, inclusiv:

...
SET @Sold=ISNULL(@Sold,0) + ISNULL((
SELECT SUM(Valoare) FROM NoteContabile
WHERE ContDebitor=@Cont AND Data<=@Data AND YEAR(Data)=YEAR(@Data)
),0)
...

iar apoi să scădem rulajul creditor (nu mai scriu cum, că e evident). La sfârşit, returnăm valoarea obţinută:

...
RETURN @Sold
END

Dacă vrem să folosim un singur query, funcţia noastră ar arăta aşa:

CREATE FUNCTION SoldCont(@Cont varchar(20), @Data smalldatetime)
RETURNS money AS BEGIN
RETURN ISNULL((
SELECT SoldDebitor-SoldCreditor FROM SolduriInitiale
WHERE An=YEAR(@Data) AND Cont=@Cont
),0)+ISNULL((
SELECT SUM(Valoare) FROM NoteContabile
WHERE ContDebitor=@Cont AND Data<=@Data AND YEAR(Data)=YEAR(@Data)
),0)-ISNULL((
SELECT SUM(Valoare) FROM NoteContabile
WHERE ContCreditor=@Cont AND Data<=@Data AND YEAR(Data)=YEAR(@Data)
),0)
END

Această funcţie ar putea fi apelată astfel:
SELECT dbo.SoldCont('5311', '20070515')

Dacă-ţi vine cumva ideea să apelezi această funcţie aşa:
SELECT dbo.SoldCont(Cont, '20070630') FROM PlanulDeConturi
să ştii că nu e o idee bună, pentru că se apelează funcţia respectivă în mod repetat şi face mult mai multe citiri ale tabelei NoteContabile decât e necesar. Dacă ai nevoie de soldurile tuturor conturilor, cel mai bine e să faci cu GROUP BY şi OUTER JOIN-uri, aşa cum am făcut mai demult.

Răzvan

PS: n-am testat nimic... te las pe tine să verifici dacă e vreo greşeală de sintaxă sau de altă natură...

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Thu, 14 Jun 2007 07:48:18 GMT

HEEELP!

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Wed, 13 Jun 2007 11:20:24 GMT

Offf, dupa ce am terminat cu balanta, am reluat codul scris (ca tot m-ati obisnuit cu fineturi sql!) sa incerc sa-l mai optimizez putin. Asa am ajuns la o alta problema cu care ma lupt de 2 zile....cum sa obtin soldul pt un cont la o anumita data!
Deci: trebuie sa gasesc soldul contului 5311 la data 15.05.2006. In tabela SOLDURI_INITIALE am la anul 2006 sold debitor la 1 ian pt 5311 = 53 RON. Apoi trebuie sa caut notele din tabela NOTE_CONTABILE care indeplinesc conditia de cont si sa adun/scad la rulaj valoarea initiala. Cum se poate face asta mai elegant? E drept ca abia am ajuns la jumatate cu cititul in 'Beginning SQL Server 2005 Programming', dar nu reusesc sa fac singur interogarea...

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Sun, 10 Jun 2007 10:24:57 GMT

BELEA!!! acum am constatat ca de fapt tot nu e bine.....nu se ia in considerare la calculul soldurilor finale daca contul este pasiv, activ sau bi.... Eu am in tabela PLAN_CONTURI si o coloana TIP in care am aceasta informatie, mai ramine sa vad daca ma descurc singur sa o folosesc!

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Sat, 09 Jun 2007 17:08:39 GMT

da, e ok acum! Multumesc! Deci avem acum descris pas cu pas, de la A la Z cum se poate intocmi o balanta! Poate mai sint si altii interesati!

Re: Modalitate de generare a "Jurnalului de cumparari"...

rsocol — Fri, 08 Jun 2007 19:59:02 GMT

Oops... ai dreptate, mi-a scăpat. În loc de:

[...]
FROM SOLDURI_INITIALE S
[...]

ar fi trebuit să fie:

[...]
     FROM (
         SELECT CONT, SOLD_DEBITOR, SOLD_CREDITOR
         FROM SOLDURI_INITIALE WHERE AN=2006
     ) S
[...]

Răzvan

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Fri, 08 Jun 2007 19:37:41 GMT

este ok acum! am mai bagat un JOIN cu tabela PLAN_CONTURI pt ca imi trebuia si explicatia aferenta fiecarui cont din balanta. Acum arata asa cum vreau! Dar am descoperit o chestie.... Daca am adaugat "where an=2006" inainte de ")X" (pt a putea specifica anul) am observat ca e necesar ca in tabela SOLDURI_INITIALE sa existe anterior toate conturile din planul de conturi care au miscare in acel an....altfel, normal dealtfel!, conturile respective lipsesc din balanta... Exista o alta posibilitate de a alege soldurile de pornire ale unui an si altfel decit am facut eu dar totusi, sa apara toata conturile in balanta chiar daca ele nu se regasesc si in SOLDURI_INITIALE? Sper ca am explicat cit de cit 'citet' ce vreau.....

Re: Modalitate de generare a "Jurnalului de cumparari"...

rsocol — Thu, 07 Jun 2007 05:14:34 GMT

Erai destul de aproape:

SELECT CONT, RULAJ_INITIAL_DEBITOR, RULAJ_INITIAL_CREDITOR,
    RULAJ_CURENT_DEBITOR, RULAJ_CURENT_CREDITOR,
    RULAJ_TOTAL_DEBITOR, RULAJ_TOTAL_CREDITOR,
    CASE WHEN RULAJ_TOTAL_DEBITOR>RULAJ_TOTAL_CREDITOR
        THEN RULAJ_TOTAL_DEBITOR-RULAJ_TOTAL_CREDITOR
        ELSE 0
    END AS SOLD_DEBITOR,
    CASE WHEN RULAJ_TOTAL_CREDITOR>RULAJ_TOTAL_DEBITOR
        THEN RULAJ_TOTAL_CREDITOR-RULAJ_TOTAL_DEBITOR
        ELSE 0
    END AS SOLD_CREDITOR
FROM (
    SELECT COALESCE(S.CONT, D.CONT, C.CONT, A.CONT, B.CONT) AS CONT,
        ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_INITIAL_DEBITOR,0) AS RULAJ_INITIAL_DEBITOR,
        ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_INITIAL_CREDITOR,0) AS RULAJ_INITIAL_CREDITOR,
        ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS RULAJ_CURENT_DEBITOR,
        ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS RULAJ_CURENT_CREDITOR,
        ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_INITIAL_DEBITOR,0)
            +ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS RULAJ_TOTAL_DEBITOR,
        ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_INITIAL_CREDITOR,0)
            +ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS RULAJ_TOTAL_CREDITOR
    FROM SOLDURI_INITIALE S

    FULL OUTER JOIN (
        SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_INITIAL_DEBITOR
        FROM NOTE_CONTABILE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTDB
    ) D ON S.CONT=D.CONT

    FULL OUTER JOIN (
        SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_INITIAL_CREDITOR
        FROM NOTE_CONTABILE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTCR
    ) C ON C.CONT=ISNULL(S.CONT,D.CONT)

    FULL OUTER JOIN (
        SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_CURENT_DEBITOR
        FROM NOTE_CONTABILE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTDB
    ) A ON A.CONT=COALESCE(S.CONT,D.CONT,C.CONT)

    FULL OUTER JOIN (
        SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CURENT_CREDITOR
        FROM NOTE_CONTABILE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTCR
    ) B ON B.CONT=COALESCE(S.CONT,D.CONT,C.CONT,A.CONT)
) X
ORDER BY CONT

Răzvan

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Wed, 06 Jun 2007 21:43:43 GMT

Da, este bestial! Intre timp, am reusit sa generez toate cele 8 coloane ale balantei, dar mai 'babeste'! Evident, cind am vazut simplitatea si rapiditatea solutiei date de tine am hotarit sa fac asa! Problema e ca am facut asa:
SELECT COALESCE(S.CONT, D.CONT, A.CONT, B.CONT) AS CONT,
    ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_DEBITOR,0) AS RULAJ_TOTAL_DEBITOR,
    ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_CREDITOR,0) AS RULAJ_TOTAL_CREDITOR,
    ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS RULAJ_CURENT_DEBITOR,
    ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS RULAJ_CURENT_CREDITOR,
    ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_DEBITOR,0)+ISNULL(A.RULAJ_CURENT_DEBITOR,0) AS TOTAL_DEBITOR,
    ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_CREDITOR,0)+ISNULL(B.RULAJ_CURENT_CREDITOR,0) AS TOTAL_CREDITOR
FROM SOLDURI_INITIALE S

FULL OUTER JOIN (
SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_DEBITOR
FROM NOTE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTDB
) D ON S.CONT=D.CONT

FULL OUTER JOIN (
SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CREDITOR
FROM NOTE WHERE DATADOC BETWEEN '20070101' AND '20070131' GROUP BY CONTCR
) C ON C.CONT=ISNULL(S.CONT,D.CONT)

FULL OUTER JOIN (
SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_CURENT_DEBITOR
FROM NOTE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTDB
) A ON A.CONT=ISNULL(S.CONT,D.CONT)

FULL OUTER JOIN (
SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CURENT_CREDITOR
FROM NOTE WHERE DATADOC BETWEEN '20070201' AND '20070228' GROUP BY CONTCR
) B ON B.CONT=ISNULL(S.CONT,C.CONT)
ORDER BY CONT

dar imi dubleaza unele inregistrari, adica imi apar de 2 ori conturile care au rulaj curent si pe debit si pe credit...cele care au numai debit sau numai credit ies bine... O alta nelamurire ar fi: cum pot sa atribui un alias cirnatului de operatii (cum este de ex TOTAL_CREDITOR) pt a putea sa fac mai usor operatiile de verificare si scadere necesare generarii coloanelor de sold final debitor/creditor?

Multam pt timpul pierdut cu mine!

Re: Modalitate de generare a "Jurnalului de cumparari"...

rsocol — Wed, 06 Jun 2007 18:23:34 GMT

Într-adevăr, la calculul balanţei e recomandabil să existe într-o tabelă măcar soldurile din fiecare an, dacă nu chiar fiecare balanţă de pe lunile anterioare (ca să nu se recalculeze totdeauna de la începutul timpurilor). Aş recomanda ca tabela cu soldurile iniţiale la începutul fiecărui an să fie o tabelă separată (în loc să ai nişte coloane în planul de conturi, cum ai zis tu). Astfel, pornind de la tabela NOTE_CONTABILE definită mai sus, să considerăm că avem şi următoarea tabelă:

CREATE TABLE SOLDURI_INITIALE (
    CONT varchar(20),
    AN smallint,
    SOLD_DEBITOR money NOT NULL,
    SOLD_CREDITOR money NOT NULL,
    PRIMARY KEY (CONT, AN),
    CHECK (SOLD_DEBITOR=0 OR SOLD_CREDITOR=0)
)

INSERT INTO SOLDURI_INITIALE VALUES ('371', 2007, 5000, 0)
INSERT INTO SOLDURI_INITIALE VALUES ('401', 2007, 0, 5000)

Atunci o tentativă de balanţă poate fi calculată cu următoarea funcţie:

CREATE FUNCTION PSEUDO_BALANTA(@Data smalldatetime)
RETURNS TABLE AS RETURN
SELECT COALESCE(S.CONT, D.CONT, C.CONT) AS Cont,
    ISNULL(S.SOLD_DEBITOR,0)+ISNULL(D.RULAJ_DEBITOR,0) AS RULAJ_TOTAL_DEBITOR,
    ISNULL(S.SOLD_CREDITOR,0)+ISNULL(C.RULAJ_CREDITOR,0) AS RULAJ_TOTAL_CREDITOR
FROM SOLDURI_INITIALE S FULL OUTER JOIN (
    SELECT CONTDB AS CONT, SUM(LEI) AS RULAJ_DEBITOR
    FROM NOTE_CONTABILE WHERE DATADOC<=@Data GROUP BY CONTDB
) D ON S.CONT=D.CONT FULL OUTER JOIN (
    SELECT CONTCR AS CONT, SUM(LEI) AS RULAJ_CREDITOR
    FROM NOTE_CONTABILE WHERE DATADOC<=@Data GROUP BY CONTCR
) C ON C.CONT=ISNULL(S.CONT,D.CONT)

Caracteristica esenţială a unei balanţe este faptul că totalul sumelor debitoare este egal cu totalul sumelor creditoare, lucru care se poate verifica executând:

SELECT SUM(RULAJ_TOTAL_DEBITOR), SUM(RULAJ_TOTAL_CREDITOR)
FROM PSEUDO_BALANTA('20070131')

Am spus totuşi că e o pseudo-balanţă pentru că are doar o singură pereche de rulaje (o singură egalitate). În general, se foloseşte balanţa cu 4 egalităţi (dar există şi cu 3 sau cu 5). De obicei, în balanţa cu 4 egalităţi (pentru o lună oarecare, alta decât ianuarie) se regăsesc următoarele perechi de coloane:
-rulaje iniţiale (soldurile de la începutul anului plus cu rulajele înregistrate în lunile precedente)
-rulajele curente (suma operaţiunilor din cursul lunii respective)
-rulajele totale (rulajele iniţiale plus rulajele curente)
-soldurile finale (dacă rulajul total debitor e mai mare decât rulajul total creditor, atunci soldul debitor este rulajul total debitor minus rulajul total creditor, iar soldul creditor este zero; în caz contrar, invers)

Te las pe tine să faci funcţia care returnează o balanţă adevărată (pornind de la parametrii @DataInitiala şi @DataFinala), ca exerciţiu...

Răzvan

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Mon, 04 Jun 2007 20:11:36 GMT

Of,of...nu stiam in ce ma bag.... Am vorbit cu contabila si mi-a spus ca programul de conta pe care il foloseste la firma mea (si pe care vreau sa-l schimb, desi contabila se impotriveste!) nu face balanta bine, si face balarii daca se incearca o generare de balanta pe o luna din anii trecuti.... Atunci mi-am zis ca hai sa incerc sa fac eu balanta. Deci am pus mina pe o balanta listata si am incercat sa-i dau de cap...si cu ajutor, am reusit sa inteleg ce este pe acolo. Problema e cum naiba sa generez balanta pornind de la tabela NOTE_CONTABILE de mai sus! Se poate concepe o interogare complexa care sa genereze 'in bloc' balanta? Sau trebuie sa folosesc ceva tabele temporare? Ma ajuta si pe mine cineva cu ceva idei in directia asta? Multumesc anticipat!

Later edit: de fapt, nu am idee cum ar fi mai bine: sa fac o tabela in care sa salvez rezultatul fiscal la sfirsitul fiecarui an si la generarea unei balante sa pornesc de la rezultatul anterior la care adaug rulajul curent sau de fiecare data sa incerc sa generez pornind de la intregul pachet de note contabile? Desi, cred ca e o prostie, asa cred (varianta 2) ca functioneaza actualul program de conta... Voi ce ziceti? Multam inca o data!

Later-later edit: as vrea sa fac un select care sa-mi faca o suma dupa un cimp din tabela NOTE si la aceasta suma sa adauge o valoare citita dintr-o alta tabela (soldurile la 1 ianuarie)... Nu imi dau seama insa daca se poate... eu am incercat ceva de genul:
select sum(lei) + planconturi.[2006] as lei from NOTE join planconturi on note.condb=planconturi.cont where condb='401.agora' and datadoc between '2007-01-01' and '2007-02-28' si nu vrea.... Cum pot sa fac asta?

Re: Modalitate de generare a "Jurnalului de cumparari"...

Diana — Mon, 04 Jun 2007 06:46:17 GMT

Eu ti-as recomanda si articolele lui Itzic Ben Gan din SQL Server Magazine, si "Inside Microsoft SQL Server 2005: T-SQL Programming" (a aceluiasi autor). Din pacate nu am "apucat" inca sa citesc cartea, dar articolele sunt super...

Re: Modalitate de generare a "Jurnalului de cumparari"...

rsocol — Mon, 04 Jun 2007 05:52:34 GMT

Apropos de cărţi... nu ştiu ce să zic: eu n-am citit prea multe cărţi de SQL, sunt mai mult genul "citeşte documentaţia şi încearcă singur". Dar am răsfoit într-o librărie cartea lui Ken Henderson "The Guru's Guide to Transact-SQL" şi mi s-au părut interesante multe chestii, de genul celor pe care nu le găseşti în documentaţie. A apărut şi în limba română, la Editura Teora, dar trebuie să ţii cont că e totuşi o carte destul de veche (din anul 2000), deci nu tratează noutăţile din SQL Server 2005, de exemplu. Pe de altă parte, e o carte de nivel avansat şi nu ştiu dacă ai ajuns acolo, dar poate mai au şi alţii alte recomandări. Până atunci, oricum, întreabă cu încredere pe aici, că încercăm să te ajutăm.

Răzvan

Re: Modalitate de generare a "Jurnalului de cumparari"...

ThePuiu — Sun, 03 Jun 2007 22:48:04 GMT

Multumesc de raspuns! Este exact ce vroiam!
O alta rugaminte ar fi daca poti sa-mi recomanzi o carte pe care sa o gasesc undeva pe net sau in librarii si din care sa pot sa invat si mai mult decit cuvintele cheie din sql....pt ca se pare ca nu ajunge. Nu vreau sa tot deranjez in stinga si in dreapta cu intrebari stupid de simple... Deja imi trec idei marete prin cap! Daca tot am ajuns aici cu 'programelul' de ce sa nu incerc sa-l mai dezvolt si sa imi genereze si un fel de balanta care sa ma ajute sa urmaresc mai bine clientii si furnizorii....si uite asa povestea nu se mai termina! O sa iasa un ERP pina la urma