CODEWITHSUNDEEP
sqlsql-servert-sqlselect
DareDevil
DareDevil

Reputation: 5349

Selection One Entry Only As Non Zero In SQl Select

I have a scenario where I have to select multiple rows from table, I have multiple rows of one record but with different status, at times I have two identical rows with identical data for status < for that case I canted to select Non zero for the first occurrence and set 0 for the remaining occurrences.

Below is the Image to show and I have marked strike-out and marked 0 for the remaining occurrence.

And body could suggest better SQL Query:

Image

Here is the Query: I am getting zero value for status 1 for ID =1 but I need to show first as regular and then 0 if that status repeats again.

CREATE TABLE #Temp
(ID INT,
ItemName varchar(10),
Price Money,
[Status] INT,
[Date] Datetime)


INSERT INTO #Temp VALUES(1,'ABC',10,1,'2014-08-27')
INSERT INTO #Temp VALUES(1,'ABC',10,2,'2014-08-27')
INSERT INTO #Temp VALUES(1,'ABC',10,1,'2014-08-28')
INSERT INTO #Temp VALUES(2,'DEF',25,1,'2014-08-26')
INSERT INTO #Temp VALUES(2,'DEF',25,3,'2014-08-27')
INSERT INTO #Temp VALUES(2,'DEF',25,1,'2014-08-28')
INSERT INTO #Temp VALUES(3,'GHI',30,1,'2014-08-27')


SELECT CASE WHEN Temp.Status = 1 THEN
 0
 ELSE
 Temp.Price END AS Price,
 * FROM (SELECT * FROM #Temp) Temp

 DROP TABLE #Temp

Here is the result:

result

Upvotes: 1

Views: 157

Answers (4)

Bulat
Bulat

Reputation: 6979

You can do this with UNION: 

SELECT * FROM #Temp t
WHERE NOT EXISTS 
   (SELECT * FROM #Temp 
    WHERE t.id = id and t.status = status and t.date < date)
UNION ALL
SELECT ID, ItemName, 0 as Price, status, date
WHERE EXISTS 
   (SELECT * FROM #Temp 
    WHERE t.id = id and t.status = status and t.date < date)

Or subquery:

SELECT CASE 
     WHEN (SELECT COUNT(*) 
           FROM #Temp 
           WHERE t.id = id and t.status = status 
             and t.date > date) > 1 THEN 0 ELSE price END as NewPrice, t.* 
FROM #Temp t

Or possibly RANK() function:

  SELECT CASE 
       WHEN RANK() OVER (PARTITION BY id, status ORDER BY date) > 1 
       THEN 0 ELSE Price END,
       t.*   
  FROM #Temp t

Upvotes: 0

Adi
Adi

Reputation: 232

please try this below code . it is working for me. 

CREATE TABLE #Temp
(ID INT,
ItemName varchar(10),
Price Money,
[Status] INT,
[Date] Datetime)


INSERT INTO #Temp VALUES(1,'ABC',10,1,'2014-08-27')
INSERT INTO #Temp VALUES(1,'ABC',10,2,'2014-08-27')
INSERT INTO #Temp VALUES(1,'ABC',10,1,'2014-08-28')
INSERT INTO #Temp VALUES(2,'DEF',25,1,'2014-08-26')
INSERT INTO #Temp VALUES(2,'DEF',25,3,'2014-08-27')
INSERT INTO #Temp VALUES(2,'DEF',25,1,'2014-08-28')
INSERT INTO #Temp VALUES(3,'GHI',30,1,'2014-08-27')


select *,case when a.rn=1 and status!=2 then price else 0 end as price  from 
     (select *,ROW_NUMBER() over(partition by status,date order by date asc) rn from #Temp) a 
     order by ItemName asc

Upvotes: 0

bummi
bummi

Reputation: 27384

You might modify your inner select using Row_Number() and set price to Zero for RowNumber > 1.

SELECT CASE WHEN Temp.RowNumber > 1 THEN
 0
 ELSE
 Temp.Price END AS Price,
 * FROM (

          SELECT *,Row_Number() over (PARTITION  by ID,Status ORDER BY ID,Date) AS 'RowNumber'
          FROM #Temp

) Temp
Order by ID,Date

Upvotes: 1

gotqn
gotqn

Reputation: 43666

You can try this:

;WITH DataSource AS
(
    SELECT RANK() OVER (PARTITION BY [ID], [ItemName], [Price], [Status] ORDER BY Date) AS [RankID]
           ,*
    FROM #Temp
)
SELECT [ID]
      ,[ItemName]
      ,IIF([RankID] = 1, [Price], 0)
      ,[Status]
      ,[Date]
FROM DataSource
ORDER BY [ID]
        ,[Date]

Here is the output:

enter image description here

Upvotes: 1

Related Questions