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)
 SQL based on 2 tables.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jj6052703
Starting Member

USA
11 Posts

Posted - 06/26/2013 :  19:35:56  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/26/2013 :  21:10:28  Show Profile  Reply with Quote
May be something like this:

--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;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/27/2013 :  03:10:06  Show Profile  Reply with Quote
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
Go to Top of Page

jj6052703
Starting Member

USA
11 Posts

Posted - 01/23/2014 :  09:25:36  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 = '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
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.08 seconds. Powered By: Snitz Forums 2000