on 04-16-2013 9:30 PM
Hola buena Tarde
Tengo dudas haber si alguien me puede ayudar , quiero un Query similar al de Analisis de Ventas pero Por Grupo de Clientes, Seria por Mes Con un rango de fechas
Este seria Algo asi Monterrey Enero Febrero . . . . . . . .Diciembre total
2500 3000 2000 7500
Soy principiante en esto de los Querys y busquedas Ojala me puedan Apoyar.
Saludos Cordiales
Intenta con este, ajustalo a lo que vallas necesitando, si tienes dudas nos comentas
SELECT P.GroupName,
ISNULL([1],0) as [Enero],
ISNULL([2],0) as [Febrero],
ISNULL([3],0) as [Marzo],
ISNULL([4],0) as [Abril],
ISNULL([5],0) as [Mayo],
ISNULL([6],0) as [Junio],
ISNULL([7],0) as [Julio],
ISNULL([8],0) as [Agosto],
ISNULL([9],0) as [Septiembre],
ISNULL([10],0) as [Octubre],
ISNULL([11],0) as [Noviembre],
ISNULL([12],0) as [Diciembre]
FROM (SELECT T0.GroupCode,T2.GroupName,(T1.Doctotal-T1.VatSum-T1.TotalExpns+T1.DpmAmnt) AS Monto,
MONTH(T1.Docdate) as [month]
FROM dbo.OCRD T0
LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2013
INNER JOIN OCRG T2 ON T0.GroupCode = T2.GroupCode
UNION
SELECT T0.GroupCode,T2.GroupName,-(T1.Doctotal-T1.VatSum-T1.TotalExpns+T1.DpmAmnt) AS Monto,
MONTH(T1.Docdate) as [month]
FROM dbo.OCRD T0
LEFT JOIN dbo.ORIN T1 ON T1.CardCode = T0.CardCode
INNER JOIN OCRG T2 ON T0.GroupCode = T2.GroupCode
AND Year(T1.Docdate) = 2013 ) S
PIVOT (SUM(S.Monto) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
Order By P.GroupName
Saludos.
Alessandro.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
98 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.