Calculate Days between Teams
-
Hi All,
I'm trying to find the number of days a property is with the maintenance team (assets) and allocation team when a property is vacant. When the property is vacant apart from the Void Start date, all the dates are blank.
Flow of property movement: Void Start Date > Referred to Assets> Referred to Allocations>Let Date
Case 1: All the dates can be the same if the property is repaired and let same day. In this case property was with assets and allocation 1 day
Case 2: Referred to Assets & Referred to Allocation can be greater than void start date
Case 3: Void Start Date & Referred to Assets are same day and referred to allocation is greater than Void Start Date & Referred to Assets
Case 4: Let date can be blank for few months. In this case Days with allocation is the full month or Referred to Allocations - End of Month
Case 5: When Referred to Allocation date is within the month and Referred to Assets date is in the previous month and let date is blank then
a) Days with assets is Start Month - Referred to Allocations
b) Days with Allocations is Referred to Allocations - Today (i.e when the report is run)
Below is sample data
Any doubts please ask.
Declare @void Table
( [Void Ref] int
, [Prop Code] int
, [Void Start Date] date
, [Referred to Assets] date
, [Accepted by Assets] date
, [Referred to Allocations] date
, [Accepted by Allocations] date
, [Let Date] date)
;
INSERT INTO @void
([Void Ref], [Prop Code], [Void Start Date], [Referred to Assets], [Accepted by Assets], [Referred to Allocations], [Accepted by Allocations],[Let Date])
VALUES
(4841, 4247, '2017-09-14', '2017-09-14', '2017-09-14', '2017-12-19', '2017-12-19', '2018-01-01'),
(5004, 2387, '2017-11-07', '2017-11-08', '2017-11-08', '2017-11-13', '2017-11-13', NULL),
(5048, 3922, '2018-01-16', NULL, NULL, NULL, NULL, NULL),
(5147, 1043, '2017-11-30', '2017-11-30', '2017-11-30', '2017-12-04', '2017-12-04', '2017-12-06'),
(5237, 3442, '2018-01-07', '2018-01-08', '2018-01-08', '2018-01-08', NULL, NULL);with dates(Month_End) as
(
select cast('2014-01-01' as datetime)-1 as datetime
union all
select DATEADD(month, 1, Month_End+1)-1
from dates
where Month_End < GETDATE()
),
Months As
(select DateAdd(month, -1, DateAdd(day, 1, Month_End)) As Month_Start
,Month_End
,[Void Ref]
,[Prop Code]
,[Void Start Date]
,[Referred to Assets]
,[Accepted by Assets]
,[Referred to Allocations]
,[Accepted by Allocations]
,[Let Date]
FROM @void v join dates d ON d.Month_End Between [Void Start Date] and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,isnull([Let Date], GETDATE()))+1,0))
)Select Month_Start
,Month_End
,[Void Ref]
,[Prop Code]
,[Void Start Date]
,[Referred to Assets]
,[Accepted by Assets]
,[Referred to Allocations]
,[Accepted by Allocations]
,[Let Date]
,Case When Month_Start >= [Referred to Assets] Then
Case When Month_End <= isnull([Referred to Allocations], GETDATE()) Then DATEDIFF(day, [Referred to Assets], Month_End)
Else DATEDIFF(day, [Referred to Assets], isnull([Referred to Allocations], GETDATE()))
End
When [Referred to Assets] > GETDATE() AND [Referred to Allocations] IS NULL Then 0
Else
Case When Month_End <= isnull([Referred to Allocations], GETDATE()) Then DATEDIFF(day,[Referred to Assets], Month_End)
Else DATEDIFF(day,[Referred to Assets], isnull([Referred to Allocations], GETDATE()))
End
End As [Days with Assets]
,Case When Month_Start >= [Referred to Allocations] Then
Case When Month_End <= isnull([Let Date], GETDATE()) Then DATEDIFF(day, [Referred to Allocations], Month_End) + 1
Else DATEDIFF(day, [Referred to Allocations], isnull([Let Date], GETDATE())) +1
End
When [Referred to Allocations] > GETDATE() AND [Let Date] IS NULL Then 0
Else
Case When Month_End <= isnull([Let Date], GETDATE()) Then DATEDIFF(day,[Referred to Allocations], Month_End) + 1
Else DATEDIFF(day,[Referred to Allocations], isnull([Let Date], GETDATE()+1))
End
End As [Days with Allocation]
From Months
order by [Void Ref]Here is another cal for days with assets and allocations
, DATEDIFF(
DAY
, CASE WHEN ([Referred to Assets] is null or [Referred to Assets]< DateAdd(month, -1, DateAdd(day, 1, Month_End))) and [Void Start Date] < DateAdd(month, -1, DateAdd(day, 1, Month_End))
THEN DateAdd(month, -1, DateAdd(day, 1, Month_End))
WHEN ([Referred to Assets] is null or [Referred to Assets]< DateAdd(month, -1, DateAdd(day, 1, Month_End))) and [Void Start Date] > DateAdd(month, -1, DateAdd(day, 1, Month_End))
THEN [Void Start Date]
ELSE [Referred to Assets] END
,CASE WHEN [Referred to Allocations] <= Month_End
THEN [Referred to Allocations]
WHEN [Referred to Allocations] is null and ([Referred to Assets] is null or [Referred to Assets]< DateAdd(month, -1, DateAdd(day, 1, Month_End)))
and [Void Start Date]<DateAdd(month, -1, DateAdd(day, 1, Month_End))
THEN dateadd(dd,-1,DateAdd(month, -1, DateAdd(day, 1, Month_End)))
WHEN [Referred to Allocations] is null and getdate()<Month_End
THEN getdate()
ELSE Month_End END
) + 1 AS [Days with Assets]
,DATEDIFF(
DAY
, CASE WHEN [Accepted by Allocations] > DateAdd(month, -1, DateAdd(day, 1, Month_End)) THEN dateadd(dd,-1,[Accepted by Allocations]) ELSE DATEADD(DAY, -1, DateAdd(month, -1, DateAdd(day, 1, Month_End))) END
, CASE WHEN [Accepted by Allocations] IS NULL AND [Let Date] IS NULL then DATEADD(DAY, -1, DateAdd(month, -1, DateAdd(day, 1, Month_End)))
when [Let Date] < Month_End THEN [Let Date] ELSE Month_End END
) AS [Days with Allocations]
Desired Results
2 Thanks
Jag -
Are you really on SQL 2008? This is a lot easier on SQL 2012.
Also, using an rCTE for calendar generation is extremely inefficient. Here is a better approach using a Tally table.
WITH Base AS
(
SELECT n
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n)
)
, Tally AS
(
SELECT TOP(DATEDIFF(MONTH, '2013-12-31', GETDATE())) ROW_NUMBER() OVER(ORDER BY @@VERSION) AS n
FROM Base a
CROSS JOIN Base b
)
SELECT DATEADD(MONTH, n, '2013-12-31') AS Month_End
FROM TallyDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Friday, January 12, 2018 1:54 PM
Hi Drew,
Yeah , the application provider has no plans to move to SQL 2012 or 2016 any time soon.
I will try the Tally table but I wanted help with calculating the days with teams pls.
Appreciate your assistance please.
Thanks
Jag
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply