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.
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.EquipmentIDFROM pmtask INNER JOIN pmaint ON pmtask.TaskNum = pmaint.TaskNum INNER JOIN equiphours ON pmaint.EquipmentID = equiphours.EquipmentIDWHERE (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 directionSELECT pmtask.PMTaskID, pmtask.TaskNum, pmtask.LastPerfDate, pmtask.LastPerfHours, pmtask.NextPerfDate, pmtask.NextPerfHours, pmtask.PerformsEvery, pmtask.PerformHow, pmtask.WONum, pmaint.EquipmentIDFROM 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.EquipmentIDWHERE (pmtask.WONum IS NULL) AND (pmtask.NextPerfDate <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))AND (pmtask.PerformHow = 'Hours')AND equiphours.lastValue = 1JimEveryday I learn something that somebody else already knew |
|
|
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. StacyP.S. I also learn something everyday that somebody else already knew! |
|
|
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.hoursEverything in here(SELECT EquipmentID,Hours, Row_Number() OVER (partition by Equipment order by date desc) as LastValueFROM equiphours) equiphours is available to use, and you can add extra columns to it without breaking anythingJimEveryday I learn something that somebody else already knew |
|
|
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 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-14 : 14:48:20
|
Glad I could help!JimEveryday I learn something that somebody else already knew |
|
|
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,StacyP.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 pmtaskSET NextPerfDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))FROM pmtaskINNER JOIN pmaint ON pmtask.TaskNum = pmaint.TaskNumINNER JOIN (SELECT EquipmentID,Miles, Row_Number() OVER (partition by EquipmentID order by date desc) as LastValue FROM equipmiles) equipmiles ON pmaint.EquipmentID = equipmiles.EquipmentIDWHERE (pmtask.NextPerfDate IS NULL) AND (pmtask.PerformHow = 'Miles') AND equipmiles.lastValue = 1 AND (pmtask.NextPerfHours <= Equipmiles.Miles ) |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-14 : 15:34:39
|
Nah, just make it a derived tableUPDATE tgt SET NextPerfDate = current_timestampFROM pmttask tgtINNER 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.JimEveryday I learn something that somebody else already knew |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-14 : 15:48:40
|
That's the way!JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|