Skip to Content
avatar image
Former Member

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}

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 09, 2014 at 03:40 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 10, 2014 at 03:53 AM

    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

    Add comment
    10|10000 characters needed characters exceeded