reporte de cantidad de facturas por día.

[angeles804]

alguien me podrá ayudar, ahorita he estado alejada un poco de sql y a veces se me complica hacer reportes ...jeeje tengo que hacer un reporte que me de el numero de facturas por dia y por sucursal que se realizan. me podrían ayudar. tengo algo como esto:

SELECT DocTime, Count(*) As No_Filas

From OINV

GROUP BY docTime

pero me envia todos los documentos de factura que se han generado. no como yo lo quiero. bueno gracias.

[Former Member · Accepted solution]

Este debería ser el mismo:

SELECT T0.DocTime,

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '0' and '1000000' and docdate='2009/02/18')) As FORJADORES,

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '1000001' and '2000000' and docdate='2009/02/18')) As UNIVERSIDAD,

(select count(docentry) from oinv where (doctime=t0.doctime and docnum between '2000001' and '3000000' and docdate='2009/02/18')) as LIBRAMIENTO,

(select count(docentry) from oinv where( doctime=t0.doctime and docnum between '3000001' and '4000000' and docdate='2009/02/18')) as CANGREJOS,

(select count(docentry) from oinv where (doctime=t0.doctime and docnum between '4000001' and '5000000' and docdate='2009/02/18')) as ROSARITO,

(select count (docentry) from oinv where (doctime=t0.doctime and docnum between '5000001' and '6000000' and docdate='2009/02/18')) as SANTA_ROSA

From OINV T0

where t0.docdate='2009/02/18'

group by T0.DocTime

[angeles804 · Accepted solution]

Esta es la forma que queriaque saliera. mas o menos.

 SELECT distinct 'series'= case
When T0.series=1 then ' FORJADORES'
WHEN T0.series=34 then  'UNIVERSIDAD'
WHEN T0.series=35 then 'LIBRAMIENTO'
WHEN t0.series=36 then 'CANGREJOS'
WHEN T0.series=37 then 'ROSARITO'
WHEN T0. Series=144 then 'SANTA ROSA'
END,
(select count(docentry) from oinv where (series=t0.series and doctime between '700' and '800' and docdate='2009/02/18')) as [7 AM A 8 AM],
(select count(docentry) from oinv where (series=t0.series and doctime between '800' and '900' and docdate='2009/02/18')) as [8 AM A 9 AM],
(select count(docentry) from oinv where (series=t0.series and doctime between '900' and '1000' and docdate='2009/02/18')) as [9 AM  A 10 AM],
(select count(docentry) from oinv where (series=t0.series and doctime between '1000' and '1100' and docdate='2009/02/18')) as [10 AM A 11 AM],
(SELECT Count(docentry) From OINV where (series = t0.series and doctime between '1100' and '1200' and docdate='2009/02/18')) As  [11 AM a 12 PM],
(SELECT Count(docentry) From OINV where (series= t0.series and doctime between '1200' and '1300' and docdate='2009/02/18')) As [12 PM a 1 PM],
(select count(docentry) from oinv where (series=t0.series and doctime between '1300' and '1400' and docdate='2009/02/18')) as [1 PM A 2 PM],
(select count(docentry) from oinv where( series=t0.series and doctime between '1400' and '1500' and docdate='2009/02/18')) as [2 PM A 3 PM],
(select count(docentry) from oinv where (series=t0.series and doctime between '1500' and '1600' and docdate='2009/02/18')) as [3PM A 4 PM],
(select count(docentry) from oinv where (series=t0.series and doctime between '1600' and '1700' and docdate='2009/02/18')) as [4PM A 5 PM], 
(select count(docentry) from oinv where (series=t0.series and doctime between '1700' and '1800' and docdate='2009/02/18')) as [5 PM A 6 PM],
(select count(docentry) from oinv where (series=t0.series and doctime between '1800' and '1900' and docdate='2009/02/18')) as [6 PM A 7 PM]
From OINV T0
where t0.docdate='2009/02/18' 

[Former Member]

SELECT T0.DocTime,

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '0' and '1000000' and docdate='2009/02/18')) As No_Filas1,

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '1000001' and '2000000' and docdate='2009/02/18')) As No_Filas2

From OINV T0

where T0.docnum >'0' and t0.docdate='2009/02/18'

GROUP BY t0.docTime

[angeles804]

gracias esto quedo bien asi, solo faltaria pulirlo mas para lo que realmente quiero, pero esto me srivio mucho.

solo agregue el distinct para que solo me mande una sola hora.

{}

SELECT distinct(T0.DocTime),

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '0' and '1000000' and docdate='2009/02/18')) As FORJADORES,

(SELECT Count(docentry) From OINV where (doctime = t0.doctime and docnum between '1000001' and '2000000' and docdate='2009/02/18')) As UNIVERSIDAD,

(select count(docentry) from oinv where (doctime=t0.doctime and docnum between '2000001' and '3000000' and docdate='2009/02/18')) as LIBRAMIENTO,

(select count(docentry) from oinv where( doctime=t0.doctime and docnum between '3000001' and '4000000' and docdate='2009/02/18')) as CANGREJOS,

(select count(docentry) from oinv where (doctime=t0.doctime and docnum between '4000001' and '5000000' and docdate='2009/02/18')) as ROSARITO,

(select count (docentry) from oinv where (doctime=t0.doctime and docnum between '5000001' and '6000000' and docdate='2009/02/18')) as SANTA_ROSA

From OINV T0

where t0.docdate='2009/02/18'

{}

[angeles804]

que es el reporting service?, ese creo que no lo tengo yo, si me pudieras dar el dato de eso, aunque yo solo quiero el numero de facturas que se hacen, no cantidad ni totales ninada de eso. bueno gracias por tu ayuda

[Former Member]

Reporting Services es un server digamoslo asi que viene junto con el MSSQL, que lo mas probable es que lo tengas tu en tu instalacion de SQL.

Aqui te dejo una liga para la implementacion.

http://msdn.microsoft.com/es-es/library/ms159868(SQL.90).aspx

Una vez implementado se pueden generar reportes que con querys de SAP no saldrian, por ejemplo este mismo query que te pase en RS lo hago con una 'matrix' y separa sucursales (series) y montos, ganancias, etc.

Saludos.

[angeles804]

a ver, si esta bien el query carlos, pero yo ocupo uno donde te diga esto ejemplo.

suc 1 | suc2 | suc3 | suc4 |

11:00 AM 2 | 4 | 6 | 8 |

11:01 AM 7 | 8 | 8 | 9 |

11:02 AM 8 | 9 | 2 | 1 |

Esto es lo que quiero y tengo este query que saca esto.

{SELECT doctime, Count(*) As No_Filas

From OINV

where( docnum >'0' and docnum< ' 1000000' ) and (docdate='2009/02/18')

GROUP BY docTime}

11:00 AM 2

11:01 AM 1

[Former Member]

Se me olvido comentarte que eso lo hago ya en ReportingServices.....como es un reporte tipo gerencial con ganancia y demas se manda automaticamente por correo.

En RS se hace un acomodo para que se vea asi.

[Former Member]

SELECT distinct t3.slpcode as slpcode,t3.slpname as slpname,T0.docdate, 
CASE 
WHEN T0.[ObjType] = '13' AND T0.[isIns] = 'N' AND T0.[DocSubType] = '--' THEN 'FACTURA'  
WHEN T0.[ObjType] = '13' AND T0.[isIns] = 'Y' THEN 'F.RESERVA' 
WHEN T0.[ObjType] = '13' AND T0.[DocSubType] = 'DN' THEN 'N.DEBITO'
WHEN  T1.[BaseType] = '203' THEN 'F.ANTICIPO'   END AS TIPODOC,
T0.cardname as cardname,T0.docnum as docnum,T1.quantity as quantity,T1.Linetotal as linetotal, T1.[Dscription] as dscription, T5.[ItmsGrpNam] , T1.[LineTotal] AS Total1, t1.grssprofit as grssprofit, 
T7.[SeriesName] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE  T1.targetType<> '14' AND T0.DocDate >= CONVERT(CHAR(10), GETDATE(), 112) +' 00:00' AND T0.DocDate <= CONVERT(CHAR(10), GETDATE(), 112) +' 23:59:59'

Este es parte de uno que uso para facturacion diaria con otros datos.

A ver si te sirve....

[Former Member]

Pruebe esto, entonces:

SELECT doctime, Count(*) As No_Filas

From dbo.OINV

where docnum >'1000000' and docdate='[%0\]'

GROUP BY docTime

[angeles804]

tengo esto:

suc 1.

SELECT doctime, Count(*) As No_Filas

From OINV

where( docnum >'0' and docnum<'1000000') and (docdate='2009/02/18')

GROUP BY docTime

suc 2.

SELECT doctime, Count(*) As No_Filas

From OINV

where( docnum >'1000000' and docnum<'2000000') and (docdate='2009/02/18')

GROUP BY docTime

suc 3.

SELECT doctime, Count(*) As No_Filas

From OINV

where( docnum>'2000000' and docnum<'3000000') and (docdate='2009/02/18')

GROUP BY docTime

pero quisiera que me saliera eso por columnas en un solo select, anteriormente alguien me paso para hacer algosimilar con las almacenes y el material en stock. alguien me podría ayudar con esto....por fis...

[Former Member]

Una solución fácil es:

SELECT T0.doctime, Count(*) As No_Filas

From dbo.OINV T0

where T0.docnum > '[%0]' and T0.docdate = ' '

GROUP BY T0.docTime

[Former Member]

Pruebe esta una:

SELECT DocTime, Count(*) As No_Filas

From OINV

Where DateDiff(day, CreateDate, getdate()) < 1

GROUP BY docTime

Gracias

Gordon

[angeles804]

SELECT doctime, Count(*) As No_Filas

From OINV

where( docnum >'1000000' and docnum<'2000000') and (docdate='2009/02/18')

GROUP BY docTime

tengo esto para generar el reporte, pero ocupo que sea por numeracion de documento distintos y en el sap no me deja meter esto. [%0] entonces como le hago ahii??

[Humberto_Neira]

SELECT DocDate, Count(*) As No_Filas

From OINV

GROUP BY DocDate

lo que te esta faltando, es el dato donde registras la sucursal de cada docuemnto.

[angeles804]

mmm si, pero digo tengo algo asi,obvi que ese no me sirve por que me envia todas las facturas agrupadas por hora, y eso no quiero.... quiero columna de almacen, renglon la hora y que sea por día con una condicion where... para que asi me arregue de un solo días..

[Humberto_Neira]

OJO con el tema SUCURSAL y no confundir con almacen.

Recuerda que las BODEGAS se registran a nivel de detalles de una Factura, lo significa que una factura podria tener N bodegas en un mismo documento, entonces, a que bodega pertenece esa factura.

Te recomiendo, analizar bien el tema de SUCURSALES, pues si lo basas en BODEGAS es de alto riesgo cualquier error en el ingreso de datos si es que usan BODEGAS diferentes en cada linea.

[angeles804]

sorry es ... oinv de ... los encabezados de las facturas con respecto a las seriees, que es lo que quiero manejar ...si fuera el detalle de seria la inv1 . no?. bueno eso es lqo ueyo quiero sacar. ahorita estoy pensando en subquerys.