July 19, 2016 at 3:15 pm
I was wondering if anyone could help me. I've been asked to create a database for regular machine maintenance.
There will be multiple users, and will sign off on a completed task with a date/timestamp.
If they are overdue on any task, or more, it will display a warning message. When they mark the task as complete, it will reset and continue forward.
I've gotten this far but am unsure as how to create the repeating tasks for each item. Would anyone be able to offer suggestions? Do I create a stored procedure to automatically populate the table daily? I'm not sure where to go at this point, or how to do that.
CREATE TABLE Tasks (
TaskID int IDENTITY(1,1) NOT NULL,
Machine varchar(255) NOT NULL,
Name varchar(255) NOT NULL,
PeriodFreq int NOT NULL,
PeriodTypeID varchar(255) NOT NULL,
Area varchar(255),
Time varchar(255),
Notes varchar(555), )
July 20, 2016 at 8:40 am
This is a job for a tally table[/url]!
Here's a crash course on how they work. Run this:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(x), -- 10
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(x), -- 100
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(x), -- 1K
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(x), -- 10K
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(x) -- 100K
Now that we got that out of the way here's on way to approach this. Create a stored proc that takes a few parameters - taskID, the required number of occurrences, a start date.
CREATE PROC dbo.CreateTasks
(
@taskID int,
@firstDueDate date,
@Instances int
)
AS
SELECT
TaskID,
DateDue =
-- add any other date types as needed...
CASE PeriodTypeID
WHEN 'd' THEN DATEADD(DD,N,@firstDueDate)
WHEN 'm' THEN DATEADD(MM,N,@firstDueDate)
END,
[Status] = 'Not Started',
Name = NULL,
CompletedDate = NULL,
Notes = NULL
FROM Tasks
CROSS JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(x),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(x)
) iTally(N) -- Maximum of 100K
WHERE TaskID = @taskID AND N < @Instances;
GO
Now, note what this does:
EXEC dbo.CreateTasks 1, '20160801', 5; -- 5 days of tasks
EXEC dbo.CreateTasks 2, '20160801', 10; -- 10 days of tasks
EXEC dbo.CreateTasks 3, '20160801', 36; -- 36 months of tasks
Now you can populate your Recurrences table like this:
INSERT Recurrences(TaskID, DateDue, [Status], Name, CompletedDate, Notes)
EXEC dbo.CreateTasks 1, '20160801', 1000;
INSERT Recurrences(TaskID, DateDue, [Status], Name, CompletedDate, Notes)
EXEC dbo.CreateTasks 2, '20160801', 1000;
INSERT Recurrences(TaskID, DateDue, [Status], Name, CompletedDate, Notes)
EXEC dbo.CreateTasks 3, '20160801', 36;
-- Itzik Ben-Gan 2001
July 20, 2016 at 8:46 am
Thank you! I really appreciate your help.
July 20, 2016 at 9:54 am
no prob. 😛
Read up on tally tables if you don't know about them. They're an invaluable tool.
-- Itzik Ben-Gan 2001
July 20, 2016 at 12:58 pm
Alan.B (7/20/2016)
no prob. 😛Read up on tally tables if you don't know about them. They're an invaluable tool.
Thanks, will do!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply