CODEWITHSUNDEEP
sqlsql-serversql-server-2008
klausk
klausk

Reputation: 197

Selecting data based on status - sometimes a certain status must be accepted

let's say I have a tabel with the following structure:

ID | Date | Name | Status | Attribute A | Attribute B

Now I want to select all rows from that tabel - however, if there are two items from the same data and with the same name - where one of them have the status CANCLED, then I only want to display the one that does not have status=CANCLED. IF however - there is only one item on the given date, with that name - then I want to select it no matter what the status may be.

At the moment I'm blind to a solution - the only thing I can think about is mixing up a stored procedure, with a temp table and a lot of if/else statements. However - I'm pretty sure there must be ways to solve this problem - and probably in a rather simple query.

Thank you!

EDIT: Example data

ID          Date       Name        Status     Attribute A Attribute B
----------- ---------- ----------- ---------- ----------- -----------
1           2013-10-17 A           Complete   AA          BB        
2           2013-10-17 A           Cancled    CC          DD        
3           2013-10-18 A           Cancled    DD          EE        
4           2013-10-18 B           Complete   AA          BB

The script to create the table (as requested by some):

CREATE TABLE [dbo].[StackoverflowTest](
  [ID] [int] NOT NULL,
  [Date] [date] NULL,
  [Name] [varchar](50) NULL,
  [Status] [varchar](10) NULL,
  [Attribute A] [nchar](10) NULL,
  [Attribute B] [nchar](10) NULL,
 )

Based on the data above - the lines I want returned is the ones with the following IDs: 1, 3, 4

hope this makes my intentions a bit more clear

Upvotes: 1

Views: 52

Answers (2)

roman
roman

Reputation: 117345

you can use common table expression with row_number() function for that

with cte as (
   select
       *,
       row_number() over(
           partition by Date, Name
           order by case when status = 'Cancled' then 1 else 0 end
       ) as rn 
   from Table1
)
select
    ID, Date, Name, Status, [Attribute A], [Attribute B]
from cte
where rn = 1

But, if there's more than one record with same Date, Name and status <> 'CANCELED', query will return only one arbitrary row.

=> sql fiddle demo

Upvotes: 1

bsivel
bsivel

Reputation: 2949

This assumes that other status values are not in a lower alpha order than 'Canceled'.

select max([date]) as [date], [name], max([Status]), [Attribute A], [Attribute B]
From [YourTableName]
group by [Name],[Attribute A], [Attribute B]

Upvotes: 0

Related Questions