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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with Group by query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jj6052703
Starting Member

USA
5 Posts

Posted - 01/24/2014 :  08:29:57  Show Profile  Reply with Quote
Hello

I 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.TaskName




This 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 location
will 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
Posting Yak Master

131 Posts

Posted - 01/24/2014 :  11:09:39  Show Profile  Reply with Quote
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.
Go to Top of Page

jj6052703
Starting Member

USA
5 Posts

Posted - 01/24/2014 :  14:31:35  Show Profile  Reply with Quote
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.

Thanks


Jeff D Jackson
Go to Top of Page

jj6052703
Starting Member

USA
5 Posts

Posted - 01/27/2014 :  08:33:37  Show Profile  Reply with Quote
Anyone here who may be able to help?

Thanks

Jeff D Jackson
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.06 seconds. Powered By: Snitz Forums 2000