Ayuda con Query (CASE)

Gabrielg,

1 - Usa un alias nuevo para el resultado de la funcion CASE.
2 - Usa la funcion CASE en la clausula GROUP BY.

SELECT
Nuevo_Project = case when dbo.JDT1.Project IS NULL then '999'
when dbo.JDT1.Account = '10006141' AND
dbo.JDT1.Project = '031' then '777'
when dbo.JDT1.Account = '10006535' AND
dbo.JDT1.Project = '031' then '888'
ELSE dbo.JDT1.Project END,
SUM (dbo.JDT1.Debit - dbo.JDT1.Credit) Balance_001,
FROM dbo.JDT1
GROUP BY case when dbo.JDT1.Project IS NULL then '999'
when dbo.JDT1.Account = '10006141' AND
dbo.JDT1.Project = '031' then '777'
when dbo.JDT1.Account = '10006535' AND
dbo.JDT1.Project = '031' then '888'
ELSE dbo.JDT1.Project END;
GO


AMB




"Gabrielg" wrote:

Hola amigos,
Me podrian dar una mano que este query
Obtengo error por no agrupar segun Account (cosa que no quiero)

Gracias

SELECT
Project = case when dbo.JDT1.Project IS NULL then '999'
when dbo.JDT1.Account = '10006141' AND
dbo.JDT1.Project = '031' then '777'
when dbo.JDT1.Account = '10006535' AND
dbo.JDT1.Project = '031' then '888'
ELSE dbo.JDT1.Project END,
SUM (dbo.JDT1.Debit - dbo.JDT1.Credit) Balance_001,
FROM dbo.JDT1
GROUP BY Project
.

On Jan 26, 8:07 am, Alejandro Mesa
wrote:

Gabrielg,

1 - Usa un alias nuevo para el resultado de la funcion CASE.
2 - Usa la funcion CASE en la clausula GROUP BY.

SELECT
Nuevo_Project = case when dbo.JDT1.Project IS NULL then '999'
                      when dbo.JDT1.Account = '10006141' AND
dbo.JDT1.Project = '031' then '777'
                      when dbo.JDT1.Account = '10006535' AND
dbo.JDT1.Project = '031' then '888'
                     ELSE dbo.JDT1.Project END,
SUM (dbo.JDT1.Debit - dbo.JDT1.Credit) Balance_001,
FROM dbo.JDT1
GROUP BY case when dbo.JDT1.Project IS NULL then '999'
                      when dbo.JDT1.Account = '10006141' AND
dbo.JDT1.Project = '031' then '777'
                      when dbo.JDT1.Account = '10006535' AND
dbo.JDT1.Project = '031' then '888'
                     ELSE dbo.JDT1.Project END;
GO

AMB



"Gabrielg" wrote:
> Hola amigos,
> Me podrian dar una mano que este query
> Obtengo error por no agrupar segun Account (cosa que no quiero)

> Gracias

> SELECT
> Project = case when dbo.JDT1.Project IS NULL then '999'
>                       when dbo.JDT1.Account = '10006141' AND
> dbo.JDT1.Project = '031' then '777'
>                       when dbo.JDT1.Account = '10006535' AND
> dbo.JDT1.Project = '031' then '888'
>                      ELSE dbo.JDT1.Project END,
> SUM (dbo.JDT1.Debit - dbo.JDT1.Credit) Balance_001,
> FROM dbo.JDT1
> GROUP BY Project
> .- Hide quoted text -

- Show quoted text -

Gracias,

y en el caso que quiera agregarle WHERE N_Proyect in
('021','031','071','777','888'','999')

Gracias