Skip to Content
0
Jun 22, 2011 at 10:16 PM

agrupar datos conjunto de series en consulta

762 Views

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