Retrieving Record - 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

General SQL Server Forums

New to SQL Server Programming

Retrieving Record

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2012-03-02 : 16:08:22

Retrieve the oldest Req number with Occ code 4 or 5 but it is not followed by an Occ code 2 or 3.
If the request is followed by Occ code 2 or 3, retrieve Occ code 4 or 5 prior to that record
(See example 1). If followed by 0 continue down history for oldest Record with occupancy code 4 or 5
(See example 2). Highlighted record is record for selection.
Example 1
Req No Occ Code
5 4
4 4
3 2
2 4
1 4

Example 2
Req No Occ Code
6 4
5 4
4 4
3 0
2 4
1 4
we can take Oldest Req No by Min(ReqNo) or as you like

could any one help me on this how to get a query

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 16:47:08

can we see what you tried so far? it looks like assignment question so unless you try it you're not going to be given spoonfed solutions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-02 : 17:17:58

Unless that table has some way to identify which is row follows another row it's a trick question.

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2012-03-04 : 23:47:12

quote:
Originally posted by Lamprey

Unless that table has some way to identify which is row follows another row it's a trick question.

here is the logic in detailed.

create table #temp (id int,reqNo int,code int)

insert into #temp values(1000,10,4)
insert into #temp values(1000,08,4)
insert into #temp values(1000,09,4)
insert into #temp values(1000,07,2)
insert into #temp values(1000,06,4)
insert into #temp values(1000,05,4)
insert into #temp values(1001,09,4)
insert into #temp values(1001,08,4)
insert into #temp values(1001,07,5)
insert into #temp values(1001,06,4)
insert into #temp values(1001,05,0)
insert into #temp values(1001,04,4)
insert into #temp values(1001,03,4)
insert into #temp values(1001,10,2)
insert into #temp values(1002,09,4)
insert into #temp values(1002,08,4)
insert into #temp values(1002,07,5)
insert into #temp values(1002,06,1)
insert into #temp values(1002,05,4)
insert into #temp values(1002,04,4)

select * from #temp where ID = 1000 order by reqNo desc

I have a table with some values like this.I need a query or function for the logic below.

First we need to order by desc based on reqNo for each ID.
we need to check the "Code" is having 4 or 5 value for each ID, if it has 4 or 5
Then we need to check whether the first ID in the desc order(reqNo) is having 1 or 2 or 3 for the "code".
If it has either value then we need to skip that row and then just go on to the value where we will find 4 or 5 "code" for a particular ID.

If the value for "code" field is having 4 or 5 and if it is followed by 1 or 2 or 3 then we need to pick the prior value of that.
that means we need to pick the min value of 4 or 5 "code" prior to that.let me put in example.

just look for the id 1000 if we put in desc order by reqNo we have the "code" values are like this.

select * from #temp where ID = 1000 order by reqNo desc

here in the above query code 4 followed by 2 at reqNo 7. so we need to retrive the 3rd record. i.e.,
1000,8,4

select * from #temp where ID = 1001 order by reqNo desc

in the above query in order by reqNo desc the first value is 2 for "code". if it is 2 then we need to skip that and check for the
value 4 or 5 then it is followed by 0 then just go down. if you didnt find 1 or 2 or 3 followed by 4 or 5 then we have retrieve the
min value of "reqNo".

output we need to retrieve is 1001,3,4 record, because that is the min value of reqNo.

select * from #temp where ID = 1002 order by reqNo desc

In the above query the 5 followed by 1 so we need to retreive the record which is min of the above 4 or 5 "code"
values.output value should be
1002,7,5

I need a query in function or in any other thing by using while loop or any kind. I would be having the
records around 50k.Here we may pass ID as parameter

can any one please help me out to get query for the above logic.

thanks in advance

dev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources