Conditional Selection of Rows Using TSQL SQL Server (2008 R2)

Question

I've been staring at this for hours and hours and can't come up with an "elegant" set-based way of getting the result set I need...

Here's my sample data (my real data could be 1,000,000+ rows)...

DECLARE @t AS TABLE (ID int,ID1 nvarchar(15),[DATE] date,PERIOD int,[TYPE] nchar(1));

INSERT INTO @t (ID,ID1,[DATE],PERIOD,[TYPE])
VALUES
(1,N'NUM1','2016-01-01',1,N'B'),
(2,N'NUM1','2016-01-01',2,N'A'),
(3,N'NUM1','2016-01-01',3,N'A'),
(4,N'NUM1','2016-01-01',4,N'B'),
(5,N'NUM1','2016-01-01',4,N'A'),
(6,N'NUM1','2016-01-01',5,N'A'),

(7,N'NUM1','2016-01-02',1,N'A'),
(8,N'NUM1','2016-01-02',2,N'A'),
(9,N'NUM1','2016-01-02',3,N'A'),
(10,N'NUM1','2016-01-02',4,N'A'),
(11,N'NUM1','2016-01-02',5,N'A'),

(12,N'NUM2','2016-01-01',1,N'A'),
(13,N'NUM2','2016-01-01',1,N'B'),
(14,N'NUM2','2016-01-01',2,N'A'),
(15,N'NUM2','2016-01-01',3,N'A'),
(16,N'NUM2','2016-01-01',4,N'B'),
(17,N'NUM2','2016-01-01',4,N'A'),
(18,N'NUM2','2016-01-01',5,N'A'),

(19,N'NUM2','2016-01-02',1,N'A'),
(20,N'NUM2','2016-01-02',2,N'B'),
(21,N'NUM2','2016-01-02',3,N'A'),
(22,N'NUM2','2016-01-02',4,N'A'),
(23,N'NUM2','2016-01-02',4,N'B'),
(24,N'NUM2','2016-01-02',5,N'A');

Here is the result set I'm trying to get...

1,'NUM1','2016-01-01',1,'B'
2,'NUM1','2016-01-01',2,'A'
3,'NUM1','2016-01-01',3,'A'
5,'NUM1','2016-01-01',4,'A'
6,'NUM1','2016-01-01',5,'A'

7,'NUM1','2016-01-02',1,'A'
8,'NUM1','2016-01-02',2,'A'
9,'NUM1','2016-01-02',3,'A'
10,'NUM1','2016-01-02',4,'A'
11,'NUM1','2016-01-02',5,'A'

12,'NUM2','2016-01-01',1,'A'
14,'NUM2','2016-01-01',2,'A'
15,'NUM2','2016-01-01',3,'A'
17,'NUM2','2016-01-01',4,'A'
18,'NUM2','2016-01-01',5,'A'

19,'NUM2','2016-01-02',1,'A'
20,'NUM2','2016-01-02',2,'B'
21,'NUM2','2016-01-02',3,'A'
22,'NUM2','2016-01-02',4,'A'
24,'NUM2','2016-01-02',5,'A'

Simply put, each day has 5 periods. They can be of type A or B. I need to get the A types. but if there are no A types, I need to get the B types... (Sounds so simple when I write it out.., but my brain will not come up with something suitable)

Pleeeeeease put me out of my misery..

Answer 1

You can use ROW_NUMBER for this:

SELECT ID, ID1, [DATE], PERIOD, [TYPE]
FROM (
   SELECT ID, ID1, [DATE], PERIOD, [TYPE],  
          ROW_NUMBER() OVER (PARTITION BY ID1, [DATE], PERIOD 
                             ORDER BY [TYPE]) AS rn
   FROM @t) AS t
WHERE t.rn = 1

Using ORDER BY [TYPE] in the OVER clause of ROW_NUMBER places 'A' records on top of 'B' records. If there are no 'A' records for a given ID1, [DATE], PERIOD then B records are assigned rn = 1.