query to retrieve current status of a request

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

query to retrieve current status of a request

Author

Topic

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-28 : 08:24:57

hiiiii

I am undergoing a serious issue regarding retrieving status of a request. i should get the status whether it is waiting for next approval or is it closed.Each time the request moves up the hierarchy a notification is send.The id doesn't change but it keeps repeating up the hierarchy.For example i am submitting a leave request.Automatically a system generated notification comes saying that request has been sent to my manager for approval.once he approves the next step and another notification comes.so i have to retrieve the record that my request is waiting for approval.Please help me out

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 08:29:55

Is there a SQL query or something that we need to help with? are you saying a query doesn't return correct results? if so what is the query? and post the relevant structures of the tables involved...and expected results..

Poor planning on your part does not constitute an emergency on my part.

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-28 : 10:53:08

thanx for the response....

yes am looking for a query..i shud get the status of a request from table..... the values mainly are

id from to status comments
123 sys sam closed request fwrdd to sam
123 sam karl closed request approved and fwrdd to karl
123 karl steve open request approved and waiting
456
.
.
.
and it goes on....also it has got begindate, enddate and duedate....
in this case i shud get the latest updated one....even though it is completed, it has to be retrieved...the dates of each step is also different.....the latest updated date should be retrieved...like that all ids should be grouped.....

pls help me.....

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 11:04:51

This table structure should be rethought...you should have ID, FromID, ToID, StatusID and should link to a UserID table (for fromID and ToID names) and a status table which has the status ID and a status description...

What version of SQL?

if 2005 or later:


WITH cte_Date AS
( Select ID,Max(ENDDATE) as LastDate  
  FROM [tablename]
  Group by ID
 )

Select a.ID,a.BeginDate,a.Enddate,a.Status,a.Comments
FROM [tablename] a inner join cte_Date b on a.ID = b.ID and a.Enddate = b.LastDate

Should return the ID with last status and dates, based on the most recent date for each ID.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 12:21:31

[code]
select id, from, to, status, comments
from
(
select row_number() over (partition by id order by enddate desc) as rn,*
from table
)t
where rn=1
[/code]

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 12:52:12

visakh1..wouldn't that only return 1 record? if he needs to return more than 1 ID's status?

Poor planning on your part does not constitute an emergency on my part.

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-28 : 13:28:01

thank you for the reponse....

name of the table is wf_notifications....it is a temporary table which doesnot have any primary key/foreign key relationships....but the actual transaction starts from hr_api_transactions....i tried to get the details using group by and max(begin_date).....i am getting only id and the date...i need 'from' and 'to' user also.....iam using toad 9.0.1 work space...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 13:34:20

did you try either suggestion? post your query ...

Poor planning on your part does not constitute an emergency on my part.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-28 : 14:58:54

You're using Toad? What database engine are you using? Oracle, SQL Server, MySQL or something else?

--
Gail Shaw
SQL Server MVP

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-29 : 03:27:06

thanx.....

iam able to reach to some extent wat i wanted.....only 1 problem left nw.....my begin_date field has date as well as timestamp....
when i give the command 'max(begin_date)' only date is being considered.....i want to consider time also....because that particular detail is needed to retrieve exact record...should i use any other function specially for time consideration.....
pls help....

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-29 : 04:10:43

PersonID........begin_Date
872367.........01/01/08 15:00:00
872367.........01/01/08 16:00:00
872367.........01/01/08 18:00:00
976737.........01/01/08 19:00:00
976737.........02/01/08 16:00:00
911967.........03/01/08 18:00:00
.
.
if this the table....i have to get the result as below
872367........01/01/08 18:00:00
976737........02/01/08 16:00:00
.
.
.
pls help me

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-29 : 04:27:54

SELECT Personid, Begin_date FROM
(
SELECT Personid, Begin_date, Row_number() OVER( PARTITION BY Personid, Order By Begin_date DESC ) Rowno
FROM Yourtable
) A
WHERE Rowno = 1

Vaibhav T

If I cant go back, I want to go fast...

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-29 : 05:06:28

thanx....
still not working...missing expression message pops up highlighting 'order' by pops up....

Hananamraz
Starting Member

7 Posts

Posted - 2010-11-29 : 05:07:49

select wf.item_key,wf.from_user,wf.to_user,wf.status,wf.subject
from wf_notifications wf,(select max(begin_date) maxdate,item_key
from wf_notifications
group by item_key) wf1
where wf.ITEM_KEY=wf1.ITEM_KEY
and wf.BEGIN_DATE=wf1.maxdate
order by item_key

this is my query.............

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-29 : 07:33:40

What database engine are you using?

What is the exact error message?

--
Gail Shaw
SQL Server MVP

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-29 : 07:39:28

order by wf.Item_Key

Guessing it might be ambiguous column since the table alias prefix is missing, and it is in both tables.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 12:54:14

quote:
Originally posted by Hananamraz

PersonID........begin_Date
872367.........01/01/08 15:00:00
872367.........01/01/08 16:00:00
872367.........01/01/08 18:00:00
976737.........01/01/08 19:00:00
976737.........02/01/08 16:00:00
911967.........03/01/08 18:00:00
.
.
if this the table....i have to get the result as below
872367........01/01/08 18:00:00
976737........02/01/08 16:00:00
.
.
.
pls help me

according to this sample data below should work


select reqd columns here.....
from
(
select row_number() over (partition by PersonID order by begin_Date desc) as rn,*
from table
)t
where rn=1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources