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
 Select Query with Join to Compare

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-11-14 : 08:53:43
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 09:37:52
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

93 Posts

Posted - 2012-11-14 : 13:42:43
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!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 13:48:24
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

93 Posts

Posted - 2012-11-14 : 14:42:19
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 14:48:20
Glad I could help!

Jim

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

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-11-14 : 15:23:41
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 )
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 15:34:39
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 15:48:40
That's the way!

Jim

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

- Advertisement -