Complicated Query Help - 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)

Complicated Query Help

Author

Topic

WretchedScab
Starting Member

3 Posts

Posted - 2006-01-30 : 10:55:19

ok my brain is NOT functioning today and I need to get this query done

basically I'm trying to get the current status of an item ONLY if the status is a certain status.

example table layout::

TableReview
PKeyReview

TableItem

PKeyItem
FKeyReview

TableSchedule

PKeySchedule
FKeyItem
FKeyActionType
ScheduleDate

TableAction- doesnt really matter since this value is passed in the form scope

ok so what I need is the most current date from TableSchedule for each TableItem in a certain TableReview (review key passed in form so its known) but only if the FKeyActionType is a specific type (passed in form so its known).

For some reason my brain isnt wrapping this cos its someone else's table design and code but the best I can do so far is to get the most current records with any action type OR get the dates of items that have the action code since a distinct or max doesn't seem to be cuttting it.

any ideas ?

====
some sample data:

fkey 5 action 6 date 1/1/06
fkey 5 action 4 date 1/1/05
fkey 5 action 2 date 1/1/04

lets say you wanted to get all the items who's current status is action code 4, in that query I would not want the above fkey to show up because its most current max(scheduledate) is action code 6.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-30 : 11:35:32

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

WretchedScab
Starting Member

3 Posts

Posted - 2006-01-30 : 11:44:49

um I posted the critical table layout but I can't give real names so I used example names. I gave the expected outcome, and some sample data. We know this is the T-SQL forum so we're talking MS SQL (2000 btw). I don't really get why you linked that, my post is pretty concise and detailed ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-30 : 12:18:04

Not very pretty, but it works..


declare @tbl table (
	fkey int,
	[action] int,
	[date] datetime
)

insert @tbl values (5,6,'01/01/2006')
insert @tbl values (5,4,'01/01/2005')
insert @tbl values (5,2,'01/01/2004')
insert @tbl values (6,4,'01/01/2006')
insert @tbl values (6,2,'01/01/2005')
insert @tbl values (6,1,'01/01/2004')


select fkey, [action], [date]
from @tbl
where fkey + [date] in (select fkey + max([date])
			from @tbl	
			group by fkey)
and [action] = 4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 18:54:11

Is this what you want ? if not please post some samples and expected result

 select t.*
from	TableAction t
where	date = (select max(date) from TableAction)

----------------------------------
'KH'

WretchedScab
Starting Member

3 Posts

Posted - 2006-01-31 : 15:49:01

nah both those don't take into account most of the specifics of my original post like the specific action codes and review keys that need to be joined in from a one to many and only get the most current date if its a specific action code.

the irony of the first response pointing me to a link to read isn't lost on me given these answers =)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-31 : 19:55:30

WretchedScab,

quote:
Originally posted by WretchedScab

um I posted the critical table layout but I can't give real names so I used example names.

Did I tell its a problem? I don't mind even if u give table names /column names as t1,t2... / F1, F2 .....

quote:
Originally posted by WretchedScab

... I gave the expected outcome, and some sample data....

Its 3 records and one result (that also v have to interpret)
Again, its hard for me to create tables, enter data and write the SQL, and test it for u. The only thing I can happily do is write the SQL. If u don't want to take trouble to write the SQL urself to Create tables & Insert statements, u think v should have interest more than u do?

quote:
Originally posted by WretchedScab

... We know this is the T-SQL forum so we're talking MS SQL (2000 btw).

It doesn't mean that there r any mind readers here

quote:
Originally posted by WretchedScab

.... I don't really get why you linked that, my post is pretty concise and detailed ? ...

Thats how u think If u want ur Q to be answered quickly & precisely, u should give us enough information (and any other help, to ease the work of ours)

Anyway good luck in ur attitude

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-01 : 09:48:50

As I understand it, there's two parts to your query:
1. Get the most recent date for each Item for a specific review
2. Use this date to isolate a specific Item row then check that this is of the required type.
Try this:

-- For Review = 1
DECLARE @ReviewID INT
SET @ReviewID = 1

SELECT
	i.PKeyItem, 
	s.ScheduleDate
FROM
	dbo.TableItem AS i
	JOIN dbo.TableSchedule AS s
		ON i.PKeyItem = s.FKeyItem
	JOIN 
	(
		SELECT
			i.ItemID, 
			MAX(s.ScheduleDate) AS ScheduleDate
		FROM
			TableItem AS i
			JOIN TableSchedule AS s
				ON i.ItemID = s.ItemID
		WHERE
			i.FKeyReview = @ReviewID
		GROUP BY
			i.ItemID
	) AS latest
WHERE
	i.FKeyReview = @ReviewID
	s.FKeyActionType = 4

Mark

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-01 : 10:59:46

quote:
Originally posted by WretchedScab

nah both those don't take into account most of the specifics of my original post like the specific action codes and review keys that need to be joined in from a one to many and only get the most current date if its a specific action code.

the irony of the first response pointing me to a link to read isn't lost on me given these answers =)

I worked from the information and expected results you gave, from these expected results, my query gives you what you asked for. If this is incorrect, then please post a more detailed description and expected resultset..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources