on 09-09-2014 4:16 PM
ESTIMADOS TODOS,
TENGO EL SIGUIENTE REPORTE QUE EMITE LOS SALDOS VENCIDOS DE 2 EMPRESAS.
ME LO EMITE POR DIA Y ME TOTALIZA POR DIA
SOLO QUISIERA AGRUPARLO POR SEMANA Y TOTALIZARLO ME PODRIAS AYUDAR POR FAVOR
GRACIAS
SELECT t0.docnum, t0.U_fechavenccontra as 'docduedate', t0.cardname, CASE T0.DocCur WHEN 'MXP' THEN t0.doctotal - t0.PaidToDate ELSE t0.doctotalFC - T0.PaidFC END as 'Total Deuda', t0.doccur, 'Empresa1' as 'Sociedad'
from oinv t0
where docstatus = 'O' and t0.CardCode != '0663-000-P'And t0.CardCode != '0663-001-D' and t0.U_fechavenccontra is not null and t0.U_fechavenccontra >= {?Fecha1} and t0.U_fechavenccontra <= {?Fecha2}
union
SELECT t0.docnum,t0.U_fechavenccontra as 'docduedate', t0.cardname, CASE T0.DocCur WHEN 'MXP' THEN t0.doctotal - t0.PaidToDate ELSE t0.doctotalFC - T0.PaidFC END as 'Total Deuda', t0.doccur, 'Empresa2' as 'Sociedad'
from [SFProduccion].[dbo].[oinv] t0
where docstatus = 'O' And t0.CardCode != '0067-001-D' And t0.CardCode != '0067-000-P' and t0.U_fechavenccontra is not null and t0.U_fechavenccontra >= {?Fecha1} and t0.U_fechavenccontra <= {?Fecha2}
UNION
SELECT t0.docnum, t0.U_fechavenccontra as 'docduedate', t0.cardname, CASE T0.DocCur WHEN 'MXP' THEN t0.doctotal - t0.PaidToDate ELSE t0.doctotalFC - T0.PaidFC END as 'Total Deuda', t0.doccur, 'Empresa1' as 'Sociedad'
from ODPI t0
where docstatus = 'O' and t0.CardCode != '0663-000-P'And t0.CardCode != '0663-001-D' and t0.U_fechavenccontra is not null and t0.U_fechavenccontra >= {?Fecha1} and t0.U_fechavenccontra <= {?Fecha2}
union
SELECT t0.docnum, t0.U_fechavenccontra as 'docduedate', t0.cardname, CASE T0.DocCur WHEN 'MXP' THEN t0.doctotal - t0.PaidToDate ELSE t0.doctotalFC - T0.PaidFC END as 'Total Deuda', t0.doccur, 'Empresa2' as 'Sociedad'
from [SFProduccion].[dbo].[ODPI] T0
where docstatus = 'O' And t0.CardCode != '0067-001-D' And t0.CardCode != '0067-000-P' and t0.U_fechavenccontra is not null and t0.U_fechavenccontra >= {?Fecha1} and t0.U_fechavenccontra <= {?Fecha2}
Jorge, a tu query agregué una columna que te indica el número de Semana. Así quedaría para el primer SELECT. Lo puedes ver en la fila 04 antes del FROM. Solo es cuestión de que lo repliques al resto de los SELECTs agregándolo también como última columna.
SELECT t0.docnum, t0.U_fechavenccontra as 'docduedate', t0.cardname
, CASE T0.DocCur WHEN 'MXP' THEN t0.doctotal - t0.PaidToDate ELSE t0.doctotalFC - T0.PaidFC END as 'Total Deuda'
, t0.doccur, 'Empresa1' as 'Sociedad'
, datepart(ww,t0.U_fechavenccontra) [Semana]
from oinv t0
where docstatus = 'O'
and t0.CardCode != '0663-000-P'And t0.CardCode != '0663-001-D'
and t0.U_fechavenccontra is not null
and t0.U_fechavenccontra >= {?Fecha1}
and t0.U_fechavenccontra <= {?Fecha2}
Ya estándo así puedes usar esa columna en tu diseño dde Crystal Reports para agrupgar la información de distintas formas.
Saludos y nos avisas si te funcionó.
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola Jorge.
Si el query te totaliza por dia supongo que entonces llegas a seis dias por semana, entonces se me ocurre que podrias en el mismo Crystal Reports hacer dos campos, uno de resumen que te totalice todos los subtotales y luego uno totales al que puedes tomar como un total semanal.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
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.