CODEWITHSUNDEEP
sqlsql-servercountconditional-statements
Dr.Wido
Dr.Wido

Reputation: 35

SQL Server make a count with a condition on a view

What I need is to be able to add a column on my view that enumerates the records that meet a specific condition, right now I'm using row_number() to display in the column NU_COLA the row index with the condition.

SELECT  
OT.NU_ORDEN_TRABAJO,
OT.FL_PRIORIDAD,
OT.DT_ADDROW,
OT.USERID,
OT.DT_UPDATEROW,
OE.CD_UBICACION_ACTUAL,
OE.CD_UBICACION_CONFIRMADA,
OE.CD_UBICACION_SUGERIDA,
OE.NU_ORDEN_ESTIBA,
OE.NU_PLATAFORMA,
P.CD_ETIQUETA,
DT.DS_DESCRIPCION AS DS_DESCRIPCION_ESTADO,
DT2.DS_DESCRIPCION AS DS_DESCRIPCION_TIPO,
case when DT.DS_DESCRIPCION = 'Pendiente' then
ROW_NUMBER() OVER (ORDER BY DT_ADDROW ASC) else -1 end AS NU_COLA


FROM T_ORDEN_TRABAJO AS OT 
INNER JOIN
        T_ORDEN_ESTIBA AS OE ON 
        OT.NU_ORDEN_TRABAJO = OE.NU_ORDEN_TRABAJO 
        INNER JOIN
            T_PLATAFORMA AS P ON 
            P.NU_PLATAFORMA = OE.NU_PLATAFORMA
            INNER JOIN
            T_DOMINIO_DET AS DT ON
            OT.ND_ESTADO_ORDEN_TRABAJO = DT.NU_DOMINIO
            INNER JOIN
            T_DOMINIO_DET AS DT2 ON
            OT.ND_ORDEN_TRABAJO = DT2.NU_DOMINIO

That gives me something like this:

NU_COLA
-1
-1
-1
 4
-1
 6

And I need this:

NU_COLA
-1
-1
-1
 1
-1
 2

Any ideas?

Upvotes: 0

Views: 42

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can change your query as below:

CASE WHEN DT.DS_DESCRIPCION = 'Pendiente' then
ROW_NUMBER() OVER (ORDER BY case when DT.DS_DESCRIPCION = 'Pendiente' then DT_ADDROW  else 'ZZZzzz' end ASC) else -1 end AS NU_COLA

Upvotes: 0

WJS
WJS

Reputation: 714

A simple solution is to split the query into two separate queries and UNION them together, the top query handles the row_number for DT.DS_DESCRIPCION = 'Pendiente', the second is the case where DT.DS_DESCRIPCION does not equal 'Pendiente':

SELECT  
OT.NU_ORDEN_TRABAJO,
OT.FL_PRIORIDAD,
OT.DT_ADDROW,
OT.USERID,
OT.DT_UPDATEROW,
OE.CD_UBICACION_ACTUAL,
OE.CD_UBICACION_CONFIRMADA,
OE.CD_UBICACION_SUGERIDA,
OE.NU_ORDEN_ESTIBA,
OE.NU_PLATAFORMA,
P.CD_ETIQUETA,
DT.DS_DESCRIPCION AS DS_DESCRIPCION_ESTADO,
DT2.DS_DESCRIPCION AS DS_DESCRIPCION_TIPO,
case when DT.DS_DESCRIPCION = 'Pendiente' then
ROW_NUMBER() OVER (ORDER BY DT_ADDROW ASC) AS NU_COLA
FROM T_ORDEN_TRABAJO AS OT 
INNER JOIN
        T_ORDEN_ESTIBA AS OE ON 
        OT.NU_ORDEN_TRABAJO = OE.NU_ORDEN_TRABAJO 
        INNER JOIN
            T_PLATAFORMA AS P ON 
            P.NU_PLATAFORMA = OE.NU_PLATAFORMA
            INNER JOIN
            T_DOMINIO_DET AS DT ON
            OT.ND_ESTADO_ORDEN_TRABAJO = DT.NU_DOMINIO
            INNER JOIN
            T_DOMINIO_DET AS DT2 ON
            OT.ND_ORDEN_TRABAJO = DT2.NU_DOMINIO
WHERE DT.DS_DESCRIPCION = 'Pendiente'
UNION
SELECT  
OT.NU_ORDEN_TRABAJO,
OT.FL_PRIORIDAD,
OT.DT_ADDROW,
OT.USERID,
OT.DT_UPDATEROW,
OE.CD_UBICACION_ACTUAL,
OE.CD_UBICACION_CONFIRMADA,
OE.CD_UBICACION_SUGERIDA,
OE.NU_ORDEN_ESTIBA,
OE.NU_PLATAFORMA,
P.CD_ETIQUETA,
DT.DS_DESCRIPCION AS DS_DESCRIPCION_ESTADO,
DT2.DS_DESCRIPCION AS DS_DESCRIPCION_TIPO,
case when DT.DS_DESCRIPCION = 'Pendiente' then
-1 AS NU_COLA 
FROM T_ORDEN_TRABAJO AS OT 
INNER JOIN
        T_ORDEN_ESTIBA AS OE ON 
        OT.NU_ORDEN_TRABAJO = OE.NU_ORDEN_TRABAJO 
        INNER JOIN
            T_PLATAFORMA AS P ON 
            P.NU_PLATAFORMA = OE.NU_PLATAFORMA
            INNER JOIN
            T_DOMINIO_DET AS DT ON
            OT.ND_ESTADO_ORDEN_TRABAJO = DT.NU_DOMINIO
            INNER JOIN
            T_DOMINIO_DET AS DT2 ON
            OT.ND_ORDEN_TRABAJO = DT2.NU_DOMINIO
WHERE DT.DS_DESCRIPCION <> 'Pendiente'

Upvotes: 1

Related Questions