Post reply

Problem with Query

  • February 26, 2008 at 4:09 pm

    #182739

    Hello comunity

    I have the following query and i want to compare by documents the value on my invoice and the value of the same invoice in my accountancy program.:

    select fo.docname,fo.nrdoc,GEST=sum(fo.eivain),

    CTB=(select sum(edeb-ecre) as valor from ml mll where mll.oristamp=ml.oristamp and mll.origem='FO' and mll.conta like '31%') from fo, ml where fo.fostamp=ml.oristamp and ml.origem='FO' and ml.conta like '31%'

    and year(ml.data)=2007 and month(ml.data)<=12 group by fo.docname,fo.nrdoc,fo.fostamp,ml.oristamp,ml.conta

    to simplify:

    fo.eivain is the total of my invoice (comercial invoice)

    sum(edeb-ecre) is the input value in my account program.

    also, the field that is unique for my invoice is fo.fostamp and is equal per line in my accountancy programa ml.oristamp

    What´s the problem?

    if in my accountancy program , for my invoice, i have 2 movments in my accountancy on the same conta(31%) , because one value about on sector and the other one for an another ,then my query return on column GEST the double value of my CTB column.

    ex:

    total invoice nº 3 : 1000€

    account movment

    conta value sector

    31201 500€ 01.09.01

    31201 500€ 01.08.02

    result of Query

    GEST CTB

    2000€ 1000€

    I don´t know how can solve this problem.

    Someone could give me an idea ?

    Thanks

    Luis Santos

  • February 27, 2008 at 2:25 am

    #782391

    Hello comunity

    Nobody have an idea ???

    Luis Santos

  • February 27, 2008 at 2:39 am

    #782397

    Luis

    Please will you post DDL for your tables in the form of CREATE TABLE statements, sample data in the form of INSERT statements, the results your query is producing, and the results you would like it to produce.

    Thanks

    John

  • February 27, 2008 at 3:07 pm

    #782904

    Hello John

    Thanks for your reply.

    I will attach a WORD file with print screen of my invoice and there movment in accountancy.

    I thinks is better for you see the problem.

    Many thanks

    Luis Santos

  • February 28, 2008 at 1:57 am

    #783044

    You should probably try and get your results per conta in a derived table

    select GEST, CTB

    from

    (

    select

    GEST=sum(fo.eivain),

    fo.fostamp

    from fo

    inner join ml on fo.fostamp=ml.oristamp

    where ml.origem='FO' and ml.conta like '31%'

    and year(ml.data)=2007 and month(ml.data)<=12

    group by ml.conta

    ) TAB1

    INNER JOIN

    (

    select mll.oristamp, CTB = select sum(edeb-ecre) as valor

    from ml mll

    where mll.origem='FO' and mll.conta like '31%'

    group by mll.oristamp

    ) TAB2 on TAB1.fostamp = TAB2.oristamp

    I'm not sure I understood your problem correctly, or table structure, but try this....

  • February 28, 2008 at 1:59 am

    #783046

    Luis

    Thanks for that, but we still need DDL and sample data so that we're not guessing at what's going on. For instance, the data type of the conta column could have a bearing on this.

    John

  • February 28, 2008 at 3:44 pm

    #783497

    Hello John

    Thanks for your reply.

    I wil send to you 4 scripts

    1- generate FO table

    2-generate ML table

    3-INSERT data into FO

    4-INSERT data into ML

    I thinks that´s you will need to solve my problem and if you test my TSQL , you will see that my column name GEST as the double value than my column CTB.

    Many thanks again

    Best regards

    Luis Santos

  • February 29, 2008 at 3:07 am

    #783644

    Luis

    Each one of your insert statements returns "Error converting data type nvarchar to numeric." Sorry but I don't have time to find which of the columns this is happening in.

    John

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply