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 - 2013-06-26 : 19:35:56
|
I have table1 which is basically a task list.Table 1 = TaskID, TaskName and TaskRecurrenceDays in number of days.I have table2 where task completion is recorded using taskID from table1.Table2 = TaskID, TaskName, DateTime, NotesI am #1 looking for best method to retrieve a list of Tasks from table1 that would be considered due/outstanding. Or where the taskrecurrence days is greater than or not found, according to table1 based on entries from table2.I understand the datediff function but am getting lost on the most polically correct METHOD to use and hopefully a pointer or 2 in how to layout this query.Do I need a cursor or can this/should this be done with set based or nested queries?Thank you for any guidance.Jeff D Jackson |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-26 : 21:10:28
|
May be something like this:[CODE]--Table Definitions:DECLARE @Table1 TABLE (TaskID INT, TaskName VARCHAR(20), TaskRecurrenceDays INT);DECLARE @Table2 TABLE (TaskID INT, TaskName VARCHAR(20), [DateTime] DATETIME, Notes VARCHAR(120));-- Test Data: INSERT INTO @Table1 VALUES (1, 'DEFRAG', 7),(2, 'DIFFBACKUP', 1),(3, 'FULLBACKUP', 5),(4, 'REBUILDINDEX', 30);INSERT INTO @Table2 VALUES(1, 'DEFRAG', '2013-06-08', 'SUCCESSFUL'),(1, 'DEFRAG', '2013-06-15', 'SUCCESSFUL'),(1, 'DEFRAG', '2013-06-22', 'INTERRUPTED'),(2, 'DIFFBACKUP', '2013-06-22', 'INTERRUPTED'),(2, 'DIFFBACKUP', '2013-06-23', 'SUCCESSFUL'),(2, 'DIFFBACKUP', '2013-06-24', 'SUCCESSFUL'),(2, 'DIFFBACKUP', '2013-06-25', 'INTERRUPTED'),(3, 'FULLBACKUP', '2013-06-08', 'SUCCESSFUL'),(3, 'FULLBACKUP', '2013-06-13', 'SUCCESSFUL'),(3, 'FULLBACKUP', '2013-06-18', 'INTERRUPTED');-- QUERY;WITH CTE AS(SELECT TaskID, TaskName, MAX([DateTime]) LastTaskOccuredOn, DATEDIFF(dd, MAX([DateTime]), GETDATE()) AS TimeElapsedSinceLastOcc FROM @Table2 GROUP BY TaskID, TaskName)SELECT T2.TaskID, T2.TaskName, T2.TaskRecurrenceDays, T1.LastTaskOccuredOn, T1.TimeElapsedSinceLastOcc, CASE WHEN T2.TaskRecurrenceDays < T1.TimeElapsedSinceLastOcc THEN 'OVER DUE' WHEN T2.TaskRecurrenceDays = T1.TimeElapsedSinceLastOcc THEN 'DUE' WHEN T1.TimeElapsedSinceLastOcc IS NULL THEN 'OUTSTANDING' ELSE 'GOOD' END AS CurrentStatus FROM @Table1 T2 LEFT JOIN CTE T1 ON T1.TaskID = T2.TaskID and T1.TaskName = T2.TaskName;[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 03:10:06
|
In my opinion you should have a period field also in Table1 as you need to define period within which task has to recur ie say 4 times a month, thrice a quarter etc. Other ways TaskRecurrenceDays will not make much sense ie say a value of 4 means just do activity only 4 times altogether? certainly not!Also having period will make sure you can consolidate the entries in table2 over the period and see if all recurences are completed or not.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jj6052703
Starting Member
11 Posts |
Posted - 2014-01-23 : 09:25:36
|
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 = 'Plant') 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 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 |
|
|
|
|
|
|
|