I have a task table where I want to create a view on that can do the following: 1. Iterate through each row 2. Explode each day between start and end date into a new row 3. Insert the average task work in a new column
This is the table:
CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int
)
INSERT INTO #InputTABLE VALUES('2298aas','2018-06-06','2018-06-12',200);
I have gotten so far as to get 2 and 3 solved, but I really struggle with the iteration part. The code below will fail if more that one row is present in the InputTABLE:
CREATE TABLE #OutputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int
)
DECLARE @taskid varchar (200)
DECLARE @cnt int
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @incr int
DECLARE @tempDate datetime
DECLARE @work int
DECLARE @averagework int
SET @taskid=(Select TaskID from #InputTABLE)
SET @startDate=(Select startdate from #InputTABLE)
SET @endDate=(Select enddate from #InputTABLE)
SET @cnt=DATEDIFF(dy,@startDate,@endDate)
SET @incr=0
SET @tempDate=DATEADD(dy,@incr,Cast(@startDate As datetime))
SET @work=(Select TaskWork from #InputTABLE)
SET @averagework= @work/@cnt
WHILE @cnt>=0
BEGIN
IF @cnt = 0
BEGIN
INSERT INTO #OutputTABLE VALUES(@taskid,@tempDate,@endDate,@averagework);
END
ELSE
BEGIN
insert into #OutputTABLE values(@taskid,@tempDate,DATEADD(dy, DATEDIFF(dy,0,@tempDate)+1, -1),@averagework);
END
SET @tempDate=DATEADD(dy,@incr+1,DATEADD(dy,DATEDIFF(dy,0,@startDate),0))
SET @cnt=@cnt-1
SET @incr=@incr+1
END
I thought about implementing the solution using a cursor from this, but I'm unsure on how to do it? I am also worried by the comments about looping through rows is bad for performance, so any advice on that is much appreciated!
The table is about 15.000 rows and the average date range is about 31 days, so the view will be circa 465.000 rows. Not a high number, but the table is continually growing so I might also need to limit the view to only the last two years.
Your question is not very clear, but my magic cystall ball tells me, that you might be looking for this:
SET DATEFORMAT ymd;
CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int
);
INSERT INTO #InputTABLE VALUES('six days','2018-06-06','2018-06-12',200)
,('one day','2018-06-06','2018-06-07',200);
SELECT TaskID
,DATEADD(DAY,B.Numbr,startdate) AS ExplodingDate
,CAST(TaskWork AS DECIMAL(10,4))/A.DayDiff
FROM #InputTABLE
CROSS APPLY(SELECT DATEDIFF(DAY,startdate,enddate) AS DayDiff) A
CROSS APPLY(SELECT TOP (A.DayDiff) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Numbr FROM master..spt_values) B
DROP TABLE #InputTABLE;
the result
TaskID ExplodingDate (Kein Spaltenname)
six days 2018-06-06 00:00:00.000 33.333333333333333
six days 2018-06-07 00:00:00.000 33.333333333333333
six days 2018-06-08 00:00:00.000 33.333333333333333
six days 2018-06-09 00:00:00.000 33.333333333333333
six days 2018-06-10 00:00:00.000 33.333333333333333
six days 2018-06-11 00:00:00.000 33.333333333333333
one day 2018-06-06 00:00:00.000 200.000000000000000
Short explanation
The first APPLY computes the difference in days between your two dates.
The second APPLY uses a trick with TOP and ROW_NUMBER to create a tally table on the fly.
This will create as many rows per input row, as there are days between start and end date.
The rest is simple computation...
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE dbo.RunningNumbers(Number INT NOT NULL
,CalendarDate DATE NOT NULL
,CalendarYear INT NOT NULL
,CalendarMonth INT NOT NULL
,CalendarDay INT NOT NULL
,CalendarWeek INT NOT NULL
,CalendarYearDay INT NOT NULL
,CalendarWeekDay INT NOT NULL);
DECLARE @CountEntries INT = 100000;
DECLARE @StartNumber INT = 0;
WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally AS
(
SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr
FROM E8
)
INSERT INTO dbo.RunningNumbers
SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.*
FROM CteTally
CROSS APPLY
(
SELECT DATEADD(DAY,CteTally.Nmbr,{ts'1900-01-01 00:00:00'})
) AS CalendarDate(d)
CROSS APPLY
(
SELECT YEAR(CalendarDate.d) AS CalendarYear
,MONTH(CalendarDate.d) AS CalendarMonth
,DAY(CalendarDate.d) AS CalendarDay
,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek
,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay
,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay
) AS CalendarExt;
GO
SET DATEFORMAT ymd;
CREATE TABLE #InputTABLE
(
TaskID varchar (200),
startdate DATETIME,
enddate DATETIME,
TaskWork int
);
INSERT INTO #InputTABLE VALUES('six days','2018-06-06','2018-06-12',200)
,('one day','2018-06-06','2018-06-07',200);
SELECT TaskID
,B.CalendarDate
,CAST(TaskWork AS DECIMAL(10,4))/(A.DayDiff+1)
FROM #InputTABLE
CROSS APPLY(SELECT DATEDIFF(DAY,startdate,enddate) AS DayDiff) A
CROSS APPLY(SELECT * FROM dbo.RunningNumbers WHERE CalendarDate BETWEEN startdate AND enddate) B
DROP TABLE #InputTABLE;
GO
USE master;
GO
DROP DATABASE TestDB;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments