SQL Help - OINV less VAT minus ORIN

[Former Member]

Hi Experts - I really need help with a query. I'm trying to write a report to show: Total invoiced sales by Sales employee and region (state) minus VAT on OINV and minus any credit notes.

So far I have:

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State], T3.[SlpName], T1.[DocNum], T1.[VatSum], T1.[DocTotal], T2.[DocNum], T2.[VatSum], T2.[DocTotal]

FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN ORIN T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE T1.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C'

Can anyone help with the minus VAT and minus credit notes? Also i would like to be able to show a total for the sales employee not each line.

Thanks in advance

[KennedyT21 · Accepted solution]

Hi Tim...

Try This

select t.CardCode,t.CardName,t.CardType,t.State,t.SlpName,sum(t.SDocTotal) as Sales , sum(t.SVatSum) as Vat,

sum(t.RDocTotal)as Creditnote, sum(t.rvatsum) CreditVat,SUM(t.SDocTotal-t.SVatSum -t.RDocTotal + (t.rvatsum) ) as Net from (

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName], T1.[DocNum]as [SDocNum], T1.[VatSum]as [SVatSum] , T1.[DocTotal]as  [SDocTotal]

,0 as [RDocNum],0 as [RVatSum], 0 as [RDocTotal]

FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE T1.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C'

union all

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName],

0 as [SDocNum],0 as [SVatSum], 0 as [SDocTotal],

T2.[DocNum] as [RDocNum], T2.[VatSum]  as [RVatSum], T2.[DocTotal]  as [RDocTotal]

FROM OCRD T0 INNER JOIN orin T2 ON T0.[CardCode] = T2.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE T2.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C' 

  ) as T

  group by t.CardCode,t.CardName,t.CardType,t.State,t.SlpName

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

Thanks - I had errors when using Group By. so tried this which worked. Any idea how I can get just 1 line per Sales Employee as a total?

Thanks for your help

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName], T1.[DocNum]as [SDocNum], T1.[VatSum]as [SVatSum] , T1.[DocTotal]as  [SDocTotal]

,0 as [RDocNum],0 as [RVatSum], 0 as [RDocTotal]

FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE T1.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C'

union all

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName],

0 as [SDocNum],0 as [SVatSum], 0 as [SDocTotal],

T2.[DocNum] as [RDocNum], T2.[VatSum]  as [RVatSum], T2.[DocTotal]  as [RDocTotal]

FROM OCRD T0 INNER JOIN orin T2 ON T0.[CardCode] = T2.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE T2.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C'

[KennedyT21 · Accepted solution]

Hi Tim

What is the error?

Rgds

Kennedy

[KennedyT21 · Accepted solution]

Try This

/* select Docdate from Oinv t1 */

declare @d1 as Datetime

declare @d2 as Datetime

set @d1 =/* t1.docdate */'[%0]'

set @d2 =/* t1.docdate */'[%1]'

select t.CardCode,t.CardName,t.CardType,t.State,t.SlpName,sum(t.SDocTotal) as Sales , sum(t.SVatSum) as Vat,

sum(t.RDocTotal)as Creditnote, sum(t.rvatsum) CreditVat,SUM(t.SDocTotal-t.SVatSum -t.RDocTotal + (t.rvatsum) ) as Net

from (

SELECT T1.[DocDate],T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName], T1.[DocNum]as [SDocNum], T1.[VatSum]as [SVatSum] , T1.[DocTotal]as  [SDocTotal]

,0 as [RDocNum],0 as [RVatSum], 0 as [RDocTotal]

FROM OCRD T0 INNER JOIN [dbo].OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

WHERE   T0.[CardType] = 'C'

union all

SELECT T2.[DocDate], T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State],

T3.[SlpName],

0 as [SDocNum],0 as [SVatSum], 0 as [SDocTotal],

T2.[DocNum] as [RDocNum], T2.[VatSum]  as [RVatSum], T2.[DocTotal]  as [RDocTotal]

FROM OCRD T0 INNER JOIN [dbo].orin T2 ON T0.[CardCode] = T2.[CardCode]

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

  INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

   WHERE    T0.[CardType] = 'C'

  ) as T

    where DocDate between @d1  and @d2

  group by t.CardCode,t.CardName,t.CardType,t.State,t.SlpName

Rgds

Kennedy

[former_member205766]

Hi Tim

Try This

SELECT T0.[CardCode], T0.[CardName], T0.[CardType], T4.[State], T3.[SlpName], T1.[DocNum],T1.[BaseAmnt], T1.[VatSum], T1.[DocTotal], T2.[DocNum], T2.[VatSum], T2.[DocTotal]

OCRD T0  INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN ORIN T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode] INNER JOIN INV1 T5 ON T1.[DocEntry] = T5.[DocEntry] WHERE T1.[DocDate] BETWEEN [%0] AND [%1] AND  T0.[CardType] = 'C' and T5.TargetType Like '%%-1%%'

With Regards

Balaji Sampath

[Former Member]

Thanks - You missed out FROM!

It gives results but not 1 line per sales employee