Friday, March 9, 2012

Help with query

Hi,

I have this table:

StatusDate

PaperID StatusKy StatusDate

1000003 RU 2/19/1997 1000003 S 2/18/1997 1000003 U 2/18/1997 1000004 RU 5/29/1997 1000004 S 5/28/1997 1000004 U 5/12/1997 18169 S 9/14/1993 18169 U 9/16/1993 18169 U 9/16/1993

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:

18169 S 9/14/1993 18169 U 9/16/1993 18169 U 9/16/1993

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

1000003 RU 2/19/1997 1000003 S 2/18/1997 1000003 U 2/18/1997 1000004 RU 5/29/1997 1000004 S 5/28/1997 1000004 U 5/12/1997 18169 S 9/14/1993 18169 U 9/16/1993 18169 U 9/16/1993

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

18169 S 9/14/1993 18169 U 9/16/1993 18169 U 9/16/1993

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