agrupar datos conjunto de series en consulta

[angeles804]

Por favor ayudenme a agrupar las series, ocupo que solo me aparezcan 5 grupos pero la verdad no se como puedo agruparlos para que las series me tomen, sin tanto código.

Declare @fecha1 as datetime
declare @fecha2 as datetime
Set @fecha1 = (SELECT DISTINCT T0.rateDate FROM ORTT T0 WHERE T0.RATEDATE = [%0])
set @fecha2=(SELECT DISTINCT T0.rateDATE FROM ORTT T0 WHERE T0.RATEDATE=[%1])
SELECT distinct'series'=  case
when t0.series=1 THEN 'Forjadores'
WHEN t0.series=225 then 'FORJ FAE-RE'
WHEN T0.SERIES=190 THEN 'FORJ FAE-E'
when t0.series=34 THEN 'UNIVERSIDAD'
WHEN t0.series=191 THEN 'UNI FAE-I'
WHEN t0.series=227 then 'UNI FAE-RI'
when t0.series=35 THEN 'LIBRAMIENTO'
WHEN T0.SERIES=200 THEN 'LIB FAE-L'
WHEN t0.series=231 then 'LIB FAE-RL'
when t0.series=36 THEN 'CANGREJOS'
WHEN t0.series=203 THEN 'CNG FAE-H'
WHEN t0.series=232 then 'CNG FAE-RH'
when t0.series=37  THEN 'ROSARITO'
WHEN t0.series=215 THEN 'ROS FAE-G'
WHEN t0.series=233 then 'ROS FAE-RG'
when t0.series=144 THEN 'STA ROSA'
WHEN t0.series=197 THEN 'STA ROSA FAE-S'
WHEN t0.series=229 then 'STA ROSA FAE-RS'
ELSE 'ND'
end,
(select count(docentry) from oinv where
( series=t0.series and (datename(dw,docdate)='monday') 
and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Lunes',
(select count(docentry) from oinv where( series=t0.series 
and (datename(dw,docdate)='tuesday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Martes',
(select count(docentry) 
from oinv where( series=t0.series and (datename(dw,docdate)='wednesday')
 and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Miercoles',
(select count(docentry) 
from oinv where( series=t0.series and (datename(dw,docdate)='thursday') 
and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Jueves',
(SELECT Count(docentry) 
From OINV where (series= t0.series and (datename(dw,docdate)='friday') 
and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Viernes',
(SELECT Count(docentry) 
From OINV where (series = t0.series and (datename(dw,docdate)='saturday') 
and (docdate>=@fecha1 and docdate<=@fecha2))) As 'sabado',
(select count(docentry) 
from oinv where (series=t0.series and (datename(dw,docdate)='sunday') 
and (docdate>=@fecha1 and docdate<=@fecha2))) as 'domingo' 
From OINV T0
where t0.docdate>=@fecha1 AND T0.DOCDATe

[former_member188440]

Que grupos quieres q te aparezcan?? podrias meter todo este codigo en un subquery y asi lo filtras mas, podrias ser mas especifica?

[angeles804]

quintana

Mira el ejemplo que te pongo aqui abajo es para agrupar en un conjunto tres series, y es que si yo lo hago como este código me queda lo mismo solo que cambia el titulo del reglon en esa columna, es lo único malo. y yo quisiera que todas se agruparan.

Se me ocurrio hacer un selec para cada grupo de series pero el problema es que tendria varias columnas como grupos de serie que quieras y eso no quiero.

oks?.

Declare @fecha1 as datetime
declare @fecha2 as datetime
Set @fecha1 = (SELECT DISTINCT T0.rateDate FROM ORTT T0 WHERE T0.RATEDATE = [%0])
set @fecha2=(SELECT DISTINCT T0.rateDATE FROM ORTT T0 WHERE T0.RATEDATE=[%1])
SELECT distinct'series'=  case
when t0.series=1 or t0.series=225 or T0.SERIES=190 THEN 'FORJ'
when t0.series=34 or t0.series=191 or t0.series=227 then 'UNIVERSIDAD'
when t0.series=35 or T0.SERIES=200 or t0.series=231  THEN 'LIBRAMIENTO'
when t0.series=36 or t0.series=203 or t0.series=232 THEN 'CANGREJOS'
when t0.series=37 or t0.series=215 or t0.series=233 then 'ROS FAE-RG'
when t0.series=144 or t0.series=197 or t0.series=229 THEN 'STA ROSA'
ELSE 'ND'
end,
(select count(docentry) from oinv where( series=t0.series and (datename(dw,docdate)='monday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Lunes',
(select count(docentry) from oinv where( series=t0.series and (datename(dw,docdate)='tuesday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Martes',
(select count(docentry) from oinv where( series=t0.series and (datename(dw,docdate)='wednesday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Miercoles',
(select count(docentry) from oinv where( series=t0.series and (datename(dw,docdate)='thursday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Jueves',
(SELECT Count(docentry) From OINV where (series= t0.series and (datename(dw,docdate)='friday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'Viernes',
(SELECT Count(docentry) From OINV where (series = t0.series and (datename(dw,docdate)='saturday') and (docdate>=@fecha1 and docdate<=@fecha2))) As 'sabado',
(select count(docentry) from oinv where (series=t0.series and (datename(dw,docdate)='sunday') and (docdate>=@fecha1 and docdate<=@fecha2))) as 'domingo' 
From OINV T0
where t0.docdate>=@fecha1 AND T0.DOCDATE<=@fecha2

[former_member188440]

A ver si te entendi

Declare @fecha1 as datetime
declare @fecha2 as datetime
Set @fecha1 = (SELECT DISTINCT T0.rateDate FROM ORTT T0 WHERE T0.RATEDATE = [%0])
set @fecha2=(SELECT DISTINCT T0.rateDATE FROM ORTT T0 WHERE T0.RATEDATE=[%1])
SELECT distinct'series'=  case
when t0.series in (1,225,190,n1,n2,nX) THEN 'Grupo 1'
when t0.series in (34,191,227,n1,n2,nX) then  'Grupo 2'
.
.
.
.
ELSE 'ND'
end,
(select count(docentry) from oinv where( series=t0.series and (datename(dw,docdate)='monday') and (docdate>=@fecha1 ......................................

Algo asi???

[angeles804]

Algo en las condiciones de las series que en la misma columna de lunes, por ejemplo, me sume lo que contenga las series que en el grupo1.

y asi sucesivamente, no se si me explique :(. pero es mas bien que el count del segundo select se aplique para tres series, por ejemplo del grupo1.

[former_member188440]

Por eso te decia del subquery, asi como lo tienes con los alias de Grupo1 , Grupo 2 etc

todo el codigo que utilizas para sacar esos alias, metelo dentro de otro select que te permita sumar los alias como campos

como si pusieras algo asi

Select count (Principal.[Grupo1]+Principal.[Grupo2]  as 'Lunes', .......  FROM   --- select principal q te puede agrupar tus series

(

select ....... case when...... Then [Grupo1]
case when .... Then [Grupo2]
.
.
.
.
)  Principal ---Alias q le das a una "tabla"

order by 1

No se si me di a entender