September 28, 2016 at 7:44 am
I'm trying to allot tasks to time frames and it's not as easy as it sounds.
I have a system where an Order can have one or many services. These services may run consecutively at the same site, concurrently at the same site, consecutively at the same site with a break or concurrently at different sites. People may carry out tasks at the sites as required.
I've been asked to create a fact table that links these tasks to the sites. This itself is straightforward enough because there is essentially a one-to-one relationship between the task and the site. It becomes more complicated because I have been asked to include service ID's on the fact table. There is a one-to-many relationship between the site and services and because of this I start to get duplicate rows. A task may appear to have been done under many services and this is not always the case.
I can narrow down some of the services using the start and end dates and assigning the task to a service depending on when it was created. This doesn't work however for tasks that were created either before a service began, after it ended or between services. In these cases it's been agreed to assign the task to either the first service that starts after it was created or the last service that finished before it was created. For a task that was created between two services it should be assigned to the last service before it was created. Where there are concurrent services at the same place, the task is assigned to the lowest service ID.
My current thinking about how to do this is to create a temporary table with the details of each service. There will be a row with a start date of 2011-12-01 00:00:00.00 and a end date of the earliest service start date for an order, this 'pseudo-service will have the Service ID of the earliest service. There will be another row with a service start date of the latest service end date and the end date will be now. The service ID for this row will be the ID of the latest service. Any breaks in services will have a similar row. That way, when I join to the temporary table, I'll be able to filter using a date range to only return one service per task.
Yes, fixing the data is the best way to solve this; no, it's not an option :crazy: The other, and my favoured approach, is to tell the user they can't have the service ID because they don't actually need it.
CREATE TABLE #Services
(
ServiceIDINT
,OrderIDINT
,StartDateDATETIME
,EndDateDATETIME
)
INSERT INTO #Services VALUES
(100001,200001,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')--- One site, one service
,(100002,200002,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000') --- Two sites, one service at each site
,(100003,200002,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000') --- Two sites, one service at each site
,(100004,200003,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')-- One site, break in service
,(100005,200003,'2016-09-17 00:00:00.000','2016-09-20 00:00:00.000')-- One site, break in service
,(100006,200004,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')-- One site, concurrent services
,(100007,200004,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')-- One site, concurrent services
,(100008,200005,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')-- One site, consecutive services
,(100009,200005,'2016-09-15 00:00:00.000','2016-09-20 00:00:00.000')-- One site, consecutive services
CREATE TABLE #Sites
(
SiteIDINT
,AddressVARCHAR(15)
)
INSERT INTO #Sites VALUES
(300001,'1 Fake Street')
,(300002,'2 Fake Street')
,(300003,'3 Fake Street')
,(300004,'4 Fake Street')
,(300005,'5 Fake Street')
,(300006,'6 Fake Street')
CREATE TABLE #SiteXService
(
SiteIDINT
,ServiceIDINT
)
INSERT INTO #SiteXService VALUES
(300001, 100001)
,(300002,100002)
,(300003,100003)
,(300004,100004)
,(300004,100005)
,(300005,100006)
,(300005,100007)
,(300006,100008)
,(300006,100009)
CREATE TABLE #Tasks
(
TaskIDINT
,SiteIDINT
,TaskTypeVARCHAR(50)
,CreatedDATETIME
)
INSERT INTO #Tasks VALUES
(400001,300001 ,'Fit the equipment','2016-09-01 15:00:00.000')-- Two different tasks at the same place
,(400002,300001,'Remove the equipment','2016-09-14 15:15:00.000')-- Two different tasks at the same place
,(400003,300002,'Fit the equipment','2016-09-01 19:15:00.000')-- Two different tasks at different places
,(400004,300003,'Fit the equipment','2016-09-01 19:15:00.000')-- Two different tasks at different places
,(400005,300004,'Fit the equipment','2016-09-01 19:15:00.000')-- Task created within a service date range
,(400006,300004,'Fit the equipment','2016-09-16 19:15:00.000')-- Task created between two services
,(400007,300004,'Remove the equipment','2016-09-18 19:15:00.000')-- Task created within a service date range
,(400008,300005,'Check the equipment','2016-09-06 19:15:00.000')-- Task created to site with concurrent services
,(400009,300006,'Fit the equipment','2016-08-16 19:15:00.000')-- Task created before a service starts
,(400010,300006,'Remove the equipment','2016-09-14 19:15:00.000')-- Task created within a service date range
,(400011,300006,'Recover missing equipment','2016-09-21 19:15:00.000')-- Task created after a service ends
CREATE TABLE #Results
(
SiteIDINT
,ServiceIDINT
,StartDateDATETIME
,EndDateDATETIME
)
/*
What I'd like to create.
*/
INSERT INTO #Results VALUES
(300001,100001,'2011-12-01 00:00:00.000','2016-09-01 00:00:00.000')--- Start date of 2011-12-01 and end date of first service start
,(300001,100001,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000') --Actual service row
,(300001,100001,'2016-09-15 00:00:00.000',GETDATE()) --- Start date of last service end date and indefinite end
,(300002,100002,'2011-12-01 00:00:00.000','2016-09-01 00:00:00.000')
,(300002,100002,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')
,(300002,100002,'2016-09-15 00:00:00.000',GETDATE())
,(300003,100003,'2011-12-01 00:00:00.000','2016-09-01 00:00:00.000')
,(300003,100003,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')
,(300003,100003,'2016-09-15 00:00:00.000',GETDATE())
,(300004,100004,'2016-12-01 00:00:00.000','2016-09-01 00:00:00.000')--- Start date of 2011-12-01 and end date of first service start
,(300004,100004,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000') --Actual service row
,(300004,100005,'2016-09-15 00:00:00.000','2016-09-17 00:00:00.000') -- Start date of end of previous service and end date of start of next
,(300004,100005,'2016-09-17 00:00:00.000','2016-09-20 00:00:00.000')--Actual service row
,(300004,100005,'2016-09-20 00:00:00.000',GETDATE())
,(300005,100006,'2012-12-01 00:00:00.000','2016-09-01 00:00:00.000')
,(300005,100006,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')
,(300005,100006,'2016-09-15 00:00:00.000',GETDATE())
,(300005,100007,'2012-12-01 00:00:00.000','2016-09-01 00:00:00.000')
,(300005,100007,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')
,(300005,100007,'2016-09-15 00:00:00.000',GETDATE())
,(300006,100008,'2011-12-01 00:00:00.000','2016-09-01 00:00:00.000')
,(300006,100008,'2016-09-01 00:00:00.000','2016-09-15 00:00:00.000')
,(300006,100009,'2016-09-15 00:00:00.000','2016-09-20 00:00:00.000')
,(300006,100009,'2016-09-20 00:00:00.000',GETDATE())
SELECT
r.ServiceID
,t.TaskID
,t.TaskType
,dates.StartDate
,t.Created
,dates.EndDate
,dates.ServiceID
FROM #Resultsr
JOIN #SiteXService sxsON sxs.ServiceID= r.ServiceID
JOIN #SitessiON si.SiteID= sxs.SiteID
JOIN #TaskstON t.SiteID= si.SiteID
CROSS APPLY
(
SELECT
*
FROM #Services s
WHERE s.ServiceID = r.ServiceID
) dates
WHERE
t.Created >= r.StartDate
AND t.Created <= r.EndDate
DROP TABLE
#Services
,#Sites
,#SiteXService
,#Tasks
,#Results
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply