Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Reporte CR agruparlo por semanas

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}

Former Member
Former Member replied

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.

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question