August 27, 2014 at 11:53 am
Hi,
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.
One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours)
What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.
The 9538 value in the HRAbsenceHours function refers to the absences-workflowID
I can't get this solved.
Thanks!
reg,
Michiel
August 30, 2014 at 8:48 am
Here is a quick "set based loop" method using an inline Tally table to count the hours.
😎
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
TEST_DATA_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,startdate DATETIME NOT NULL
,enddate DATETIME NOT NULL
);
INSERT INTO @TEST_DATA(startdate,enddate)
VALUES
('2014-08-20 09:00:00.000','2014-08-23 13:00:00.000')
;
/* Seed for the inline Tally table */
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
/* Assign 1 to each working hour between 9 and 17 */
,COUNT_HOURS AS
(
SELECT
TD.TEST_DATA_ID
,CASE
WHEN DATEPART(HOUR,(DATEADD(HOUR,NM.N,TD.startdate))) BETWEEN 10 AND 17 THEN 1
ELSE 0
END AS COUNT_HOURS
,CONVERT(DATE,DATEADD(HOUR,NM.N,TD.startdate)) AS GR_DATE
FROM @TEST_DATA TD
OUTER APPLY
(
SELECT TOP(DATEDIFF(HOUR,TD.startdate,TD.enddate))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7
) AS NM(N)
)
--resource date hours
SELECT
CH.TEST_DATA_ID AS [resource]
,CH.GR_DATE AS [date]
,SUM(CH.COUNT_HOURS) AS [hours]
FROM COUNT_HOURS CH
GROUP BY CH.TEST_DATA_ID,CH.GR_DATE;
Results
resource date hours
----------- ---------- ------
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply