Query

[Former Member]

Hi All,

I am trying to get a summary query for all invoices and credit notes per branch.

Select [Branch], [Total Invoices] from

(Select case

when Series = '1' then 'DBN'

when Series = '2' then 'CPT'

when Series = '66' then 'JHB'

when Series = '67' then 'LKW'

Else 'PLZ' end [Branch],

DocTotal

from OINV ) A

Pivot

(sum(Doctotal) for [Branch] in ([Total Invoices])) as PVT

I get a syntax error

Msg 207, Level 16, State 1, Line 1

Invalid column name 'Branch'.

How do i correct this?

How do i incorperate the credit notes into this query?

Thanks,

Kiran

[Former Member]

Hi Kiran....

Here Branch means what?

Is that Document Series?

Try this....

Select Distinct T1.SeriesName, (isnull((SELECT SUM((case when upper(t4.Series) =1 then T4.DocTotal else 0 end))
	 FROM OINV T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'DBN Sales',
(isnull((SELECT SUM((case when upper(t4.Series) =2 then T4.DocTotal else 0 end))
	 FROM OINV T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'CPT Sales',
(isnull((SELECT SUM((case when upper(t4.Series) =66 then T4.DocTotal else 0 end))
	 FROM OINV T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'JHB Sales',
(isnull((SELECT SUM((case when upper(t4.Series) =67 then T4.DocTotal else 0 end))
	 FROM OINV T4 WHERE T4.DocEntry=T0.DocEntry ),0)) As 'LKW Sales'
From OINV T0 Inner Join NNM1 T1 On T0.Series=T0.Series

Hope this will work for you......

Regards,

Rahul

[Former Member]

Hi Kiran,

Try this first:

Select case

when T0.Series = '1' then 'DBN'

when T0.Series = '2' then 'CPT'

when T0.Series = '66' then 'JHB'

when T0.Series = '67' then 'LKW'

Else 'PLZ' end Branch,

sum(T0.DocTotal) 'Total Invoices'

FROM OINV T0

Group By T0.Series

Thanks,

Gordon