SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Query with Join to Compare
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 11/14/2012 :  08:53:43  Show Profile  Reply with Quote
I am trying to do a select query with three tables. The first table is pmtask, which has a child - one to many relationship with the pmaint table. I want to pull all tasks that the WONum column is blank and the PerformHow column = 'Hours' - that part I can do - see below.

SELECT     pmtask.PMTaskID, pmtask.TaskNum, pmtask.LastPerfDate, pmtask.LastPerfHours, pmtask.NextPerfDate, pmtask.NextPerfHours, pmtask.PerformsEvery, 
                      pmtask.PerformHow, pmtask.WONum, pmaint.EquipmentID
FROM         pmtask INNER JOIN
                      pmaint ON pmtask.TaskNum = pmaint.TaskNum INNER JOIN
                      equiphours ON pmaint.EquipmentID = equiphours.EquipmentID
WHERE     (pmtask.WONum IS NULL) AND (pmtask.NextPerfDate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (pmtask.PerformHow = 'Hours')


My problem is I also need it to get the EquipmentID for that task from the Pmaint Table. So I did a join with the Pmaint Table and then linked the Pmaint table with the EquipHours table by EquipmentID.

Then in the EquipHours table (which I enter weekly hour readings) I need to get the last value entered (Max in the Date column), which would be the last time I entered a value, and then use the EquipHours.Hours value to select only the tasks in pmtask table that the pmtask.NextPerfHours.value is less than or equal to the value from the last entered EquipHours table reading.

I hope this makes sense. I am trying to see if there is a way to do all this in one query.
Thanks for any help you can provide.
Stacy

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  09:37:52  Show Profile  Reply with Quote
I don't completely follow, my brain quits after just so many connections. but this might get you in the right direction

SELECT pmtask.PMTaskID, pmtask.TaskNum, pmtask.LastPerfDate, pmtask.LastPerfHours, pmtask.NextPerfDate, pmtask.NextPerfHours, pmtask.PerformsEvery,
pmtask.PerformHow, pmtask.WONum, pmaint.EquipmentID
FROM pmtask
INNER JOIN pmaint ON pmtask.TaskNum = pmaint.TaskNum
INNER JOIN (SELECT EquipmentID,Hours, Row_Number() OVER (partition by Equipment order by date desc) as LastValue
FROM equiphours
) equiphours ON pmaint.EquipmentID = equiphours.EquipmentID
WHERE (pmtask.WONum IS NULL) AND (pmtask.NextPerfDate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND (pmtask.PerformHow = 'Hours')
AND equiphours.lastValue = 1


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 11/14/2012 :  13:42:43  Show Profile  Reply with Quote
Thanks jimf for your response. You are good and the query is very close to what I need. I changed it a little because I was working on two different queries and in this one I didn't need the NextPerfDate because with it using the Hours it doesn't have a nextperfdate yet it only has a nextperfhours . This is what I have right now and it is getting the last entered hours from the equiphours table for each pmtask equipment. But how do I get it to only select the those pmtasks fields when the pmtask.NextPerfHours.Value is greater than or equal to that Equiphours.Hours.value we are getting from the Row_Number() OVER etc...
I really appreciate your help with this.
Stacy
P.S. I also learn something everyday that somebody else already knew!

Edited by - StacyOW on 11/14/2012 13:53:49
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  13:48:24  Show Profile  Reply with Quote
Won't just adding it in to your where clause do the trick?

AND pmtask.NextPerfHours >= equip.hours

Everything in here
(SELECT EquipmentID,Hours, Row_Number() OVER (partition by Equipment order by date desc) as LastValue
FROM equiphours
) equiphours

is available to use, and you can add extra columns to it without breaking anything

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 11/14/2012 :  14:42:19  Show Profile  Reply with Quote
jimf,
I got it! It works perfectly! I added that line down in the WHERE area right after the AND equiphours.lastValue = 1 and viola!

Thanks soooooo much for your help!
Stacy
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  14:48:20  Show Profile  Reply with Quote
Glad I could help!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 11/14/2012 :  15:23:41  Show Profile  Reply with Quote
One more question, I know I sound like a dope but I'm trying to learn this stuff. If I wanted to do an update on the records that are selected from this query - Do I have to put these results in a table so I can update the pmtask.NextPerfDate to Today's date?

Thanks again,
Stacy

P.S. I think I got this figured out - although it doesn't show the records that are updated so I'm not for sure but would it look something like this?
UPDATE pmtask
SET NextPerfDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
FROM pmtask
INNER JOIN pmaint ON pmtask.TaskNum = pmaint.TaskNum
INNER JOIN (SELECT EquipmentID,Miles, Row_Number() OVER (partition by EquipmentID order by date desc) as LastValue 
FROM equipmiles
) equipmiles ON pmaint.EquipmentID = equipmiles.EquipmentID
WHERE (pmtask.NextPerfDate IS NULL) AND (pmtask.PerformHow = 'Miles') 
AND equipmiles.lastValue = 1 AND (pmtask.NextPerfHours <= Equipmiles.Miles )

Edited by - StacyOW on 11/14/2012 15:39:25
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  15:34:39  Show Profile  Reply with Quote
Nah, just make it a derived table


UPDATE tgt 
SET NextPerfDate = current_timestamp
FROM pmttask tgt
INNER JOIN
(
	SELECT pmtask.PMTaskID, pmtask.TaskNum, pmtask.LastPerfDate, pmtask.LastPerfHours, pmtask.NextPerfDate, pmtask.NextPerfHours, pmtask.PerformsEvery, 
	pmtask.PerformHow, pmtask.WONum, pmaint.EquipmentID
	FROM pmtask 
	INNER JOIN pmaint ON pmtask.TaskNum = pmaint.TaskNum 
	INNER JOIN (SELECT EquipmentID,Hours, Row_Number() OVER (partition by Equipment order by date desc) as LastValue
	FROM equiphours
	) equiphours ON pmaint.EquipmentID = equiphours.EquipmentID
	WHERE (pmtask.WONum IS NULL) AND (pmtask.NextPerfDate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
	AND (pmtask.PerformHow = 'Hours')
	AND equiphours.lastValue = 1
	AND pmtask.NextPerfHours >= equip.hours
) src ON ptgt.PMTaskID = src.PMTaskID


There's probably a better way to this, but this will work. If this is all you're after, you don't need to select any columns other than the PMTaskID.


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  15:48:40  Show Profile  Reply with Quote
That's the way!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000