Reporte de ventas con Notas de Credito

[Former Member]

buenas tardes me dirijo hacia esta comunidad es que deseohacer un reporte que me traiga las facturas filtradas de una fecha especificas y si ellas tienen notas de credito mi select lo eh echo asi separado me funciona perfecta mente pero cuado deseo unirlo me da error

SELECT T0.[CardCode], T0.[CardName], T1.[DocNum], T2.[PymntGroup], T1.[DocDate], T3.[SlpName], T4.[descript],

T5.[GroupName], T6.[ListName], T1.[DocTotal] + ISNULL(T7.[WTAmnt],0) - T1.[VatSum] as 'Importe'

FROM [dbo].[OCRD]  T0

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

                      INNER JOIN [dbo].[OCTG]  T2 ON T0.GroupNum = T2.GroupNum

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

                      LEFT JOIN [dbo].[OTER]   T4 ON T0.Territory = T4.territryID

                     INNER JOIN OCRG T5 ON T0.GroupCode = T5.GroupCode

                     INNER JOIN OPLN T6 ON T0.ListNum = T6.ListNum

                      LEFT JOIN INV5 T7 ON T1.DocEntry = T7.AbsEntry

WHERE T1.[DocDate] between [%0] and [%1] AND T1.[CardCode] not in ('C3167', 'C3145', 'C0203',

'CONT-ELMAIMSI') AND   T1.[Series]<>'59'

ORDER BY T1.[DocDate]

UNION ALL 

SELECT T0.[CardCode], T0.[CardName], T1.[DocNum], T2.[PymntGroup], T1.[DocDate], T3.[SlpName], T4.[descript], T5.[GroupName], T6.[ListName] ,T1.[DocTotal] + ISNULL (T7.[WTAmnt],0) - T1.[VatSum] as 'DEVOLUCIONES'

FROM [dbo].[OCRD]  T0

                                    INNER JOIN [dbo].[ORIN]  T1 ON T0.CardCode = T1.CardCode

                                    INNER JOIN [dbo].[OCTG]  T2 ON T0.GroupNum = T2.GroupNum

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

                                    LEFT JOIN [dbo].[OTER]  T4 ON T0.Territory = T4.territryID

                                    INNER JOIN [dbo].[OCRG]  T5 ON T0.GroupCode = T5.GroupCode

                                    INNER JOIN [dbo].[OPLN]  T6 ON T0.ListNum = T6.ListNum

                                    LEFT JOIN RIN5 T7 ON T1.DocEntry = T7.AbsEntry

WHERE T1.[DocDate] between [%0] and [%1] AND T1.[CardCode] not in ('C3167', 'C3145', 'C0203' , 'CONT-ELMAIMSI')

AND   T1.[Series]<>'59'

ORDER BY T1.[DocDate]

[Former Member]

Que mas Gervacio....

Debes quitarle el GROUP BY al primer select antes de que realices el UNION ALL y solo dejas uno solo...

ASI:

SELECT T0.[CardCode], T0.[CardName], T1.[DocNum], T2.[PymntGroup], T1.[DocDate], T3.[SlpName], T4.[descript],

T5.[GroupName], T6.[ListName], T1.[DocTotal] + ISNULL(T7.[WTAmnt],0) - T1.[VatSum] as 'Importe'

FROM [dbo].[OCRD]  T0

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

                      INNER JOIN [dbo].[OCTG]  T2 ON T0.GroupNum = T2.GroupNum

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

                      LEFT JOIN [dbo].[OTER]   T4 ON T0.Territory = T4.territryID

                     INNER JOIN OCRG T5 ON T0.GroupCode = T5.GroupCode

                     INNER JOIN OPLN T6 ON T0.ListNum = T6.ListNum

                      LEFT JOIN INV5 T7 ON T1.DocEntry = T7.AbsEntry

WHERE T1.[DocDate] between [%0] and [%1] AND  T1.[CardCode]not in ('C3167', 'C3145', 'C0203',

'CONT-ELMAIMSI') AND   T1.[Series]<>'59'

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T1.[DocNum], T2.[PymntGroup], T1.[DocDate], T3.[SlpName], T4.[descript], T5.[GroupName], T6.[ListName] ,T1.[DocTotal] + ISNULL (T7.[WTAmnt],0) - T1.[VatSum] as 'DEVOLUCIONES'

FROM [dbo].[OCRD]  T0

                                    INNER JOIN [dbo].[ORIN]  T1 ON T0.CardCode = T1.CardCode

                                    INNER JOIN [dbo].[OCTG]  T2 ON T0.GroupNum = T2.GroupNum

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

                                    LEFT JOIN [dbo].[OTER]  T4 ON T0.Territory = T4.territryID

                                    INNER JOIN [dbo].[OCRG]  T5 ON T0.GroupCode = T5.GroupCode

                                    INNER JOIN [dbo].[OPLN]  T6 ON T0.ListNum = T6.ListNum

                                    LEFT JOIN RIN5 T7 ON T1.DocEntry = T7.AbsEntry

WHERE T1.[DocDate] between [%0] and [%1] AND T1.[CardCode] not in ('C3167', 'C3145', 'C0203' , 'CONT-ELMAIMSI')

AND   T1.[Series]<>'59'

ORDER BY T1.[DocDate]