Hi,
I have this table:
StatusDate
PaperID StatusKy StatusDate
10200 S 8/7/1999
10200 U 8/5/1999
10111 U 03/25/2003
10111 RU 03/23/2003
10000 U 05/17/2000
10000 S 05/23/2000
10000 RU 05/20/2000
I would like to select the case id with the status that have U later than S and RU. if U is earlier than S or RU, or if RU is later than S, then ignore it. I would like to get this result:
10200 S 8/7/1999
10200 U 8/5/1999
10111 U 03/25/2003
10111 RU 03/23/2003
10000 U 05/17/2000
10000 S 05/23/2000
10000 RU 05/20/2000
I use the following query
SELECT * FROM StatusDate
SELECT i.paperid, i.statusdate, i.statusky
FROM statusdate as i where i.paperid in
(select distinct p.paperid from statusdate as p inner join
( select paperid, statusid, statusdate, statusky
from statusdate where statusky IN ('S','RU'))
as Akeys on p.paperid = akeys.paperid and p.statusdate > akeys.statusdate)
order by i.paperid, i.statusdate, i.statusky
But it gives me this result:
10000 2000-05-17 00:00:00.000 U
10000 2000-05-20 00:00:00.000 RU
10000 2000-05-23 00:00:00.000 S
1000003 1997-02-18 00:00:00.000 S
1000003 1997-02-18 00:00:00.000 U
1000003 1997-02-19 00:00:00.000 RU
1000004 1997-05-12 00:00:00.000 U
1000004 1997-05-28 00:00:00.000 S
1000004 1997-05-29 00:00:00.000 RU
10111 2003-03-23 00:00:00.000 RU
10111 2003-03-25 00:00:00.000 U
18169 1993-09-14 00:00:00.000 S
18169 1993-09-16 00:00:00.000 U
18169 1993-09-16 00:00:00.000 U
Can anyone help me in this? thanks.
Jul,
Most of us really like to see when folks learn from the suggestions that are given as a result of posting problems here.
The solution to this problem is very similar to the suggestion to the other problem that you posted earlier today. (See that solution again here.) I would hope that you have examined that posting and realized that it is almost the same problem, and that therefore a variation of the same solution should work for this problem.
It would be helpful if you could post what you have tried so far. Also, DaleJ went to extra effort to create a sample table and INSERT statements with data so he would test his suggestion. It would be very thoughtful if you were to provide the table DDL and sample data in the form of INSERT statements. (See your earlier posting for DaleJ's example of how to provide a table DDL and sample data.)
After all, you are asking folks to volunteer their valuable time to help you, so it seems that you should be willing to help us by providing the sample table and data in a form that is easily usable.
|||Thank's so much Arnie, I do apologize for that, i am really stupid, even when i am trying to change the queries lot of times, i still couldnt get the result and i am in a rush. but thanks so much, and i appreciate ur time to advise me. thanks again!!!|||Jul,
I didn't intend to cause you to feel unwelcome.
I want you to know that if you would help us by providing the table DDL and sample data (just like in DaleJ's example), it would be easier for folks to help you. And add to that when you show what you've attempted, folks don't have to waste their time setting up the test environment, can perhaps more easily see how to assist and better help you.
|||PaperID StatusKy StatusDate
10200 S 8/7/1999
10200 U 8/5/1999
10111 U 03/25/2003
10111 RU 03/23/2003
10000 U 05/17/2000
10000 S 05/23/2000
10000 RU 05/20/2000
Result Set
10200 S 8/7/1999
10200 U 8/5/1999
10111 U 03/25/2003
10111 RU 03/23/2003
10000 U 05/17/2000
10000 S 05/23/2000
10000 RU 05/20/2000
Please observe the rows in bold. and ur statement " if U is earlier than S or RU, or if RU is later than S, then ignore it. I would like to get this result:" both are conflicting .
Please send the approriate such that we can work around
Thank you
|||You can use the following query..
Your sample result data has lot of conflicts..
But you can change the case when expression occordingly to achive your resultset.
Code Snippet
Create Table #casestatus (
[PaperID] int ,
[StatusKy] Varchar(100) ,
[StatusDate] datetime
);
Insert Into #casestatus Values('1000003','RU','2/19/1997');
Insert Into #casestatus Values('1000003','S','2/18/1997');
Insert Into #casestatus Values('1000003','U','2/18/1997');
Insert Into #casestatus Values('1000004','RU','5/29/1997');
Insert Into #casestatus Values('1000004','S','5/28/1997');
Insert Into #casestatus Values('1000004','U','5/12/1997');
Insert Into #casestatus Values('18169','S','9/14/1993');
Insert Into #casestatus Values('18169','U','9/16/1993');
Insert Into #casestatus Values('18169','U','9/16/1993');
Insert Into #casestatus Values('10200','S','8/7/1999');
Insert Into #casestatus Values('10200','U','8/5/1999');
Insert Into #casestatus Values('10111','U','03/25/2003');
Insert Into #casestatus Values('10111','RU','03/23/2003');
Insert Into #casestatus Values('10000','U','05/17/2000');
Insert Into #casestatus Values('10000','S','05/23/2000');
Insert Into #casestatus Values('10000','RU','05/20/2000');
Select
*
From
#casestatus
Where
[PaperID] in
(
Select
[PaperID]
From
(
Select
[PaperID]
,Max(Case When [StatusKy] ='U' Then [StatusDate] End) U
,Max(Case When [StatusKy] ='RU' Then [StatusDate] End) RU
,Max(Case When [StatusKy] ='S' Then [StatusDate] End) S
From
#casestatus
Group By
[PaperID]
) as Data
Where
Case When (U>RU or RU is NULL) and (U>S or S is NULL) Then 1 Else 0 End = 1
)
|||Yeah, it was actually an incorrect data. It was supposed to be the other way around. thank's so much guys, i really appreciate everything. I will provide the table DDL and correct sample data next time. Couldn't thank you enough.
No comments:
Post a Comment