SQL based on 2 tables. - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL based on 2 tables.

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, Notes

I 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jj6052703
Starting Member

11 Posts

Posted - 2014-01-23 : 09:25:36

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 = '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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources