RE:Duda con select Group by
***En Peru El IGV es lo q normalmente se conoce como I.V.A en otros paises
Tabla Ventas :
Algunas relaciones como Clientes - cobros ,Cobros - Empleado no estan incluidas aqui
CREATE TABLE [Ventas] (
[CodVenta] [numeric](18, 0) NOT NULL ,
[CodGeneral] [numeric](18, 0) NULL ,
[CodDocumento] [char] (2) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Doc_Serie] [numeric](3, 0) NOT NULL ,
[Doc_Num] [numeric](7, 0) NOT NULL ,
[FEmision] [datetime] NOT NULL ,
[CodCliente] [char] (11) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[CodEmpleado] [char] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Moneda] [char] (1) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Tipo_Cambio] [decimal](18, 4) NULL ,
[Importe_bruto] [decimal](18, 8) NULL ,
[Total_bruto] [decimal](18, 2) NOT NULL ,
[Porc_Descuento] [decimal](6, 2) NULL ,
[Descuento] [decimal](18, 2) NULL ,
[Subtotal] [decimal](18, 2) NOT NULL ,
[Porc_IGV] [decimal](4, 2) NOT NULL ,
[IGV] [decimal](18, 2) NOT NULL ,
[Total] [decimal](18, 2) NOT NULL ,
[Saldo] [decimal](18, 8) NOT NULL ,
[CodLugarOp] [varchar] (6) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Comentario] [varchar] (500) COLLATE Modern_Spanish_CI_AS NULL ,
[Estado] [bit] NOT NULL ,
[Usuario] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
CONSTRAINT [PK_Ventas] PRIMARY KEY CLUSTERED
(
[CodVenta]
) ON [PRIMARY] ,
CONSTRAINT [BDCHK_Ventas_Moneda] CHECK ([MONEDA] = 'D' or [MONEDA] = 'S')
) ON [PRIMARY]
GO
//Tabla Cobros
CREATE TABLE [Cobros] (
[CodCobro] [numeric](18, 0) NOT NULL ,
[CodCliente] [char] (11) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[CodEmpleado] [char] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[CodLugarOp] [varchar] (6) COLLATE Modern_Spanish_CI_AS NULL ,
[CodFormaPago] [char] (3) COLLATE Modern_Spanish_CI_AS NULL ,
[FCobro] [datetime] NULL ,
[Importe] [decimal](18, 8) NOT NULL ,
[Moneda] [char] (1) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Tipo_Cambio] [numeric](8, 4) NOT NULL ,
[CodBanco] [varchar] (4) COLLATE Modern_Spanish_CI_AS NULL ,
[NCheque] [char] (10) COLLATE Modern_Spanish_CI_AS NULL ,
[Anticipo] [decimal](18, 8) NULL ,
[FCobro_Cheque] [datetime] NULL ,
[Comentario] [text] COLLATE Modern_Spanish_CI_AS NULL ,
[Estado] [bit] NULL ,
[Usuario] [varchar] (30) COLLATE Modern_Spanish_CI_AS NOT NULL ,
CONSTRAINT [PK_Cobros] PRIMARY KEY CLUSTERED
(
[CodCobro]
) ON [PRIMARY] ,
CONSTRAINT [FK_Cobros_Cliente] FOREIGN KEY
(
[CodCliente]
) REFERENCES [Cliente] (
[CodCliente]
),
CONSTRAINT [FK_Cobros_Usuario] FOREIGN KEY
(
[Usuario]
) REFERENCES [Usuario] (
[Usuario]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
//
Detalle de Cobros
CREATE TABLE [CobrosDetalle] (
[CodCobro] [numeric](18, 0) NOT NULL ,
[CodVenta] [numeric](18, 0) NOT NULL ,
[CodDocumento] [char] (2) COLLATE Modern_Spanish_CI_AS NULL ,
[Doc_Serie] [numeric](3, 0) NULL ,
[Doc_Num] [numeric](7, 0) NULL ,
[Moneda] [char] (1) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Importe] [decimal](18, 8) NOT NULL ,
CONSTRAINT [FK_CobrosDetalle_Cobros] FOREIGN KEY
(
[CodCobro]
) REFERENCES [Cobros] (
[CodCobro]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_CobrosDetalle_Ventas] FOREIGN KEY
(
[CodVenta]
) REFERENCES [Ventas] (
[CodVenta]
)
) ON [PRIMARY]
GO
Como veras en la tabla cobros hay un campo Importe q indica cuanto se cobro en dicha operacion de cobranza
ademas esta el campo fecha de cobro,
Y en la tabla CobrosDetalle , se guarda q ventas se cobraron en dicha operacion y tambien veras un campo [Importe]
ahi se guarda cuanto se cobro especificamente a dicha factura /boleta
Pero volviendo al tema lo q debo obtener en el select es el
Año--
Mes --
TotVentaEnEseMesMes -
TotSaldoEnEseMes
ImporteTotal_cobradoEnEseMes
Importe_Total_cobrado = Es el monto total cobrado en ese periodo (Año - Mes : 2007 - Enero por ejemplo)
se refiere al campo Importe de la tabla Cobros
Año------Mes---Total_VentasMens---Total_SaldoVMens------Total_ImpCobradoMen
2007---Enero- 100.00--- 75.00------- 25.00
2007---Febero- 20.00-- 80.00------ 20.00
2007--Marzo--- 500.00-- 400.00----- 100.00
Gracias por tu tiempo .