Skip to Content

Query Help Plz

Hi experts:

I have a problem with my query, I have 2 series of billing

If I want to get a report of a single series that I only get the full amount without detail.

I am not throwing well this amount. In fact is bigger

It's just that simple add up the total of all invoices of that series, omitting the invoices canceled (with the targettype <> 14) who comes to me for being alive only bills that are not within any notes credit

This is my query:

SELECT TIPO = 'SELLOS', (SUM(T0.DOCTOTAL)) MONTO FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'SELLOS' AND T0.CANCELED = 'N' and T2.TargetType <>'14'

Best regards.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 15, 2008 at 08:08 PM

    Try this one:

    SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total, 
    Count(T0.Docnum) 'No. Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
    WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName  = 'LASER' 
    AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND
    T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseEntry is not NULL)
    

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 15, 2008 at 05:29 PM

    It is a bit hard to understand your question...there a few basic things that you can follow...

    You need to not INNER JOIN the NNM1 series table..you can simple use the Series Code which is in the Invoice..

    Secondly to get the Total, I would suggest using SUM( T2.LineTotal) as the DocTotal might contain Freight and additional expenses.

    Please redo your query and let me know

    Suda

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 15, 2008 at 06:18 PM

    Thanakz Suda Sampath !

    I have to join The name of series for print in papers.

    Secondly a test in my query your sugestions (T2.Linetotal)and the result is lower compared with the correct report becuse my report is based in total of invoices(but thankz! )

    here other test, in this test i count the # of docnums in which

    summarize my total, here is my error (i belive) because the query shows 37, and in my other report (is correct) i have 32 rows (invoices)

    SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total,count(T0.Docnum) 'No Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'LASER' AND T0.CANCELED = 'N' and T2.TargetType <>'14'

    The result of my query:

    TIPO Total No. Invoices

    Laser 32,186.94 37

    I think that my query is having some bug in the validation (and T2.TargetType '14

    Why? Simple, I have five bills that were canceled.

    37(invoices) - 5 (cancelled) =32 Invoices But not work

    Best regards

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 15, 2008 at 06:54 PM

    Suda:

    Only Item Type invoices in this case.(but thankz again)!

    I agree WHERE T0.DocType = 'I' The same result

    Gordon:

    SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) Total, 
    
    Count(T0.Docnum) 'No Invoices' FROM OINV T0 INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
    INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry 
    
    WHERE T0.DocDate BETWEEN [%0] AND [%1] AND  T1.SeriesName  = 'LASER' 
    AND T0.CANCELED != 'Y' AND T2.TargetType != '14' and T0.DocType = 'I' 

    The same result

    TIPO TOTAL No. Invoices

    LASER 36,186.94 37 <---- What is wrong? =(

    Supposedly the query is asking that all add up the totals excluding the bills are in credit note.

    But I see this all adding up the totals.

    Best regards

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 15, 2008 at 08:51 PM

    Suda, Gordon!

    Exellent!! Both validation Works Fine!

    Now i agree some extras

    Many thanks! For all the help!

    Best regards!

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2008 at 05:34 PM

    Hi!

    Testing Querys, and i modified this query which Suda mentioned the total of lines without freight (Linetotals)

    and i think i need this query too.

    But other question, when change the Sum(T0.Doctotal) to Sum(T3.LineTotal) in the part Count(T0.Docnum)

    SELECT Distinct TIPO = 'LASER', (SUM(T3.LineTOTAL)) 'Total S/IVA',

    (Count(T0.Docnum)) 'No.Invoices',t2.slpname salesman FROM OINV T0

    INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES

    INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE

    INNER JOIN INV1 T3 ON T0.DocEntry = T3.DocEntry

    WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T1.SeriesName = 'LASER'

    AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND T2.Slpname=[%2] and

    T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseEntry is not NULL) group by t2.slpname

    I have result :

    TIPO No. Invoices salesman

    LASER 10<--(i have 3) Juan Perez

    But i dont 10 invoices(i have 3 invoices) 10 is the total of rows of all invoices.

    Appreciate any help! Thanks!

    best regards

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Try this one:

      SELECT Distinct TIPO = 'LASER', (SUM(T0.DocTOTAL)) 'Total S/IVA', 
      Count(T0.Docnum) 'No.Invoices',t2.slpname salesman 
      FROM OINV T0 
      INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
      INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE
      WHERE T0.DocDate BETWEEN [%0] AND [%1] 
      AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND T2.Slpname='[%2]' and 
      T0.DocEntry NOT IN (Select BaseEntry FROM RIN1 WHERE BaseType != -1) 
      GROUP BY t2.slpname
      

      If you need to take out the freight and tax, you can change SUM(T0.DocTOTAL) to SUM(T0.DocTOTAL-T0.VatSum-T0.TotalExpns)

      Thanks,

      Gordon

  • Oct 17, 2008 at 02:12 PM

    thanks gordon

    IF I were to deduct the total discount i change SUM (T0.DocTOTAL-T0.VatSum-T0.TotalExpns-T0.DiscSum)

    It is right?

    If correct, would also have to take into account the discount on line, because if you do not take off globally, would also have to consider that the discount in the query

    is correct?

    Thanks

    Alessandro

    Add comment
    10|10000 characters needed characters exceeded