24/02/2012, 09:21
|
| | | Fecha de Ingreso: enero-2011
Mensajes: 58
Antigüedad: 13 años, 10 meses Puntos: 0 | |
| Datos inconsistente al colocar union
Hola amigos del foro necesito una ayuda .. que tengo una union y al ejecutar el query por separado los resultados que obtengo son los deseados. pero con simplemenmte al colocar union en mi query los resultados son diferentes .. Espero alguien me pueda ayudar. Gracias. Aqui esta el query.
select (select top 1 (case MONTH(CONVERT(VARCHAR,cff.DOCDATE))
WHEN '01' THEN 'ENE'
WHEN '02' THEN 'FEB'
WHEN '03' THEN 'MAR'
WHEN '04' THEN 'ABR'
WHEN '05' THEN 'MAY'
WHEN '06' THEN 'JUN'
WHEN '07' THEN 'JUL'
WHEN '08' THEN 'AGO'
WHEN '09' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DIC'
end) as MES from [SOP30200] cff where cff.SOPNUMBE = caf.SOPNUMBE) AS MES,
(select top 1 (case
WHEN RTRIM(caff.SALSTERR) between '10000' and '19999' THEN 'LA GRAN CARACAS'
WHEN RTRIM(caff.SALSTERR) between '20000' and '29999' THEN 'ZULIA-FALCON'
WHEN RTRIM(caff.SALSTERR) between '30000' and '39999' THEN 'CENTRO'
WHEN RTRIM(caff.SALSTERR) between '40000' and '49999' THEN 'LOS ANDES'
WHEN RTRIM(caff.SALSTERR) between '50000' and '59999' THEN 'LARA - LOS LLANOS'
WHEN RTRIM(caff.SALSTERR) between '60000' and '69999' THEN 'ORIENTE'
WHEN RTRIM(caff.SALSTERR) = '70000' THEN 'OFICINA'
end) as ZONA FRom [SOP30200] caff where caff.SOPNUMBE = caf.SOPNUMBE) as ZONA ,
cast(isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SOPNUMBE = caf.SOPNUMBE
and fIn.SOPTYPE='3'
and fIn.SLPRSNID <> 'PROMOCIONES'
and fIn.SALSTERR between '10000' and '70000'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) -
isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SOPNUMBE = caf.SOPNUMBE
and fIn.SOPTYPE='4'
and fIn.SALSTERR between '10000' and '70000'
and fIn.SLPRSNID <> 'PROMOCIONES'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) as int) unidades_total
from SOP30200 caf
inner join SOP30300 de on caf.SOPNUMBE = de.SOPNUMBE
where
caf.VOIDSTTS<>'1'
and de.[cmpntseq] not in ('16384','32768')
and caf.SLPRSNID <>'PROMOCIONES'
and (caf.SOPTYPE = '3' or caf.SOPTYPE = '4')
and( caf.SALSTERR between '10000' and '79999')
and (caf.DOCDATE between '01/01/2011' and '31/12/2011')
group by caf.SOPNUMBE
union
select (select top 1 (case MONTH(CONVERT(VARCHAR,cff.DOCDATE))
WHEN '01' THEN 'ENE'
WHEN '02' THEN 'FEB'
WHEN '03' THEN 'MAR'
WHEN '04' THEN 'ABR'
WHEN '05' THEN 'MAY'
WHEN '06' THEN 'JUN'
WHEN '07' THEN 'JUL'
WHEN '08' THEN 'AGO'
WHEN '09' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DIC'
end) as MES from [SOP30200] cff where cff.SOPNUMBE = caf.SOPNUMBE) AS MES,
(select top 1 (case
WHEN RTRIM(caff.SALSTERR) between '10000' and '19999' THEN 'LA GRAN CARACAS'
WHEN RTRIM(caff.SALSTERR) between '20000' and '29999' THEN 'ZULIA-FALCON'
WHEN RTRIM(caff.SALSTERR) between '30000' and '39999' THEN 'CENTRO'
WHEN RTRIM(caff.SALSTERR) between '40000' and '49999' THEN 'LOS ANDES'
WHEN RTRIM(caff.SALSTERR) between '50000' and '59999' THEN 'LARA - LOS LLANOS'
WHEN RTRIM(caff.SALSTERR) between '60000' and '69999' THEN 'ORIENTE'
WHEN RTRIM(caff.SALSTERR) = '70000' THEN 'OFICINA'
end) as ZONA FRom [SOP30200] caff where caff.SOPNUMBE = caf.SOPNUMBE) as ZONA ,
cast(isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SOPNUMBE = caf.SOPNUMBE
and fIn.SOPTYPE='3'
and fIn.SLPRSNID = 'PROMOCIONES'
and fIn.SALSTERR between '70000' and '70000'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) -
isnull(((select top 1 sum(quantity) FROM [SOP30300] d
inner join [SOP30200] fIn on d.SOPNUMBE = fIn.SOPNUMBE
where fIn.VOIDSTTS<>1
and fIn.SOPNUMBE = caf.SOPNUMBE
and fIn.SOPTYPE='4'
and fIn.SALSTERR between '70000' and '70000'
and fIn.SLPRSNID = 'PROMOCIONES'
and fIn.DOCDATE between '01/01/2011' and '31/12/2011'
and d.[cmpntseq] not in ('16384','32768'))),0) as int) unidades_total
from SOP30200 caf
inner join SOP30300 de on caf.SOPNUMBE = de.SOPNUMBE
where
caf.VOIDSTTS<>'1'
and de.[cmpntseq] not in ('16384','32768')
and caf.SLPRSNID = 'PROMOCIONES'
and (caf.SOPTYPE = '3' or caf.SOPTYPE = '4')
and( caf.SALSTERR between '70000' and '70000')
and (caf.DOCDATE between '01/01/2011' and '31/12/2011')
group by caf.SOPNUMBE |