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 |
|
jj6052703
Starting Member
11 Posts |
 Posted - 2014-01-24 : 08:29:57
|
| HelloI was able to get the above query working. I have expanded a couple of things in the system that require me to re tool this query. The query I ended up with is as follows.WITH CTE AS (SELECT TaskID, MAX(DateEntered) AS LastTaskOccuredOn, DATEDIFF(dd, MAX(DateEntered), GETDATE()) AS TimeElapsedSinceLastOcc FROM dbo.TaskLog AS TaskLog_1 GROUP BY TaskID) SELECT TOP (100) PERCENT Tasks.TaskID, Tasks.TaskName, Tasks.TestID, Tasks.TaskRecurrenceDays, TaskLog.LastTaskOccuredOn, TaskLog.TimeElapsedSinceLastOcc, dbo.ProcessLocations.ProcessLocation, dbo.PhysicalLocations.LocationName, dbo.PhysicalLocations.LocationID, dbo.PhysicalLocations.ProcessLocationID FROM dbo.Tasks AS Tasks INNER JOIN dbo.ProcessLocations ON Tasks.ProcessLocationID = dbo.ProcessLocations.ProcessLocationID INNER JOIN dbo.PhysicalLocations ON dbo.ProcessLocations.ProcessLocationID = dbo.PhysicalLocations.ProcessLocationID LEFT OUTER JOIN CTE AS TaskLog ON TaskLog.TaskID = Tasks.TaskID WHERE (Tasks.TaskStatus = 'Active') AND (Tasks.TaskRecurrenceDays <= TaskLog.TimeElapsedSinceLastOcc) AND (Tasks.TaskType = 'Product') ORDER BY Tasks.TaskNameThis actually works exactly as I want when a task is due for the day.It finds the task that's due, gets the process location and looks at the physical locations attached to that process location. it generates the task as due for each physical location. And my problem is when there are two physical locations.When a task is due it shows correctly. Task is due for each physical location. This is exactly what I want. The problem is a task will be listed twice, once for each location, which is correct. When I complete a task the query assumes the task has been completed for both locations and the task is no longer due. How do I tie in the locationid so that a task with a second locationwill still show as due after the first location has been recorded complete?I have added the locationID in the tasklog that is recorded when I complete a task. I need the query to tell the task has not been completed for the second location.Sorry for the long winded summation. Thanks for any advise/ guidance.Jeff D Jackson Jeff D Jackson |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-01-24 : 11:09:39
|
| I think you need to add the location id from the tasklog to the join with the cte. To do that you need to add the location id to the cte. |
 |
|
|
jj6052703
Starting Member
11 Posts |
Posted - 2014-01-24 : 14:31:35
|
| Thank you. I agree. I know I need to tie into the task detection from the tasklog, that now includes that locationID, that did not exist at the time of the query above. This issue is definitely at the top of my knowledge range. I have tried on my own for, lets say, some time before I posted. The closest I have made it, is to have the opposite effect. The query would recognize that it couldn't verify by locationid and always listed both tasks, one for each location, even when the task had been recorded complete in the tasklog. Unlike now where when the task is completed on one location, it thinks the task is done for both and no longer shows.I know I was on the right track.But unfortunately I couldn't get it, so I am looking for slightly more guidance than, "You need to tie in". I know that part, the how is the part that I cant make work. I am pretty sure I need to modify the first query to somehow include the group by of the locationid and also the locationID for the tasklog, task last completed record but my attempts are failing.ThanksJeff D Jackson |
|
|
|
jj6052703
Starting Member
11 Posts |
Posted - 2014-01-27 : 08:33:37
|
| Anyone here who may be able to help?ThanksJeff D Jackson |
|
|
|
|
|
|
|
|
Need help with Group by query