Yet another query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Yet another query question

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-02 : 15:45:44

I have a table that holds events (ID, Type, Source, Dte).

For each ID, I'd like to find three pieces of information:
- Type (RType) for the record with Source = 'R'
- Type (NonRF) for the most recent record with Source <> 'R' and Type='F'
- Type (NonRNonF) for the most recent record with Source <> 'R' and Type<>'F'

There will be at the most one record with Source='R', and 0 or more with Source<>'R' for each ID.

In other words for the following records:

declare @sample table (ID int, Type char(1), Src char(1), Dte datetime)

insert	@sample
--     id   Type  Src  Dte
select  1,  'F',  'R', '11/11/2000'  union all
select  1,  'A',  'B', '11/12/2003'  union all
select  1,  'F',  'D', '11/10/2002'  union all
select  1,  'F',  'C', '11/09/2001'  union all

select  2,  'F',  'H', '11/11/2000'  union all

select  3,  'A',  'R', '11/11/2000'  union all
select  3,  'A',  'H', '11/11/2001'  union all
select  3,  'D',  'U', '11/11/2002'  union all

select  4,  'W',  'R', '11/11/2000'

I'd expect the following results:

ID RType  NonRF   NonRNonF
1  'F'    'F'     'A'
2  ''     'F'     ''
3  'A'    ''      'D'
4  'W'    ''      ''

How do I write query that returns such output?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-02 : 17:15:27

[code]-- prepare sample data
declare @sample table (id int, type char(1), src char(1), dte datetime)

insert @sample
select 1, 'F', 'R', '11/11/2000' union all
select 1, 'A', 'B', '11/12/2003' union all
select 1, 'F', 'D', '11/10/2002' union all
select 1, 'F', 'C', '11/09/2001' union all
select 2, 'F', 'H', '11/11/2000' union all
select 3, 'A', 'R', '11/11/2000' union all
select 3, 'A', 'H', '11/11/2001' union all
select 3, 'D', 'U', '11/11/2002' union all
select 4, 'W', 'R', '11/11/2000'

-- Show the result
SELECT s1.ID,
MAX(CASE WHEN s1.Src = 'R' THEN s1.Type ELSE '' END) AS RType,
MAX(CASE WHEN s1.Src <> 'R' AND s1.Type = 'F' THEN 'F' ELSE '' END) AS NonRF,
ISNULL((SELECT TOP 1 s2.Type FROM @Sample AS s2 WHERE s2.ID = s1.ID AND s2.Src <> 'R' AND s2.Type <> 'F' ORDER BY s2.Dte DESC), '') AS NonRNonF
FROM @Sample AS s1
GROUP BY s1.ID
ORDER BY s1.ID[/code]

Peter Larsson
Helsingborg, Sweden