May 7, 2016 at 7:39 pm
Hi,
I have the below query and trying to calculate how long a vacant property was with repairs per month and how long it was vacant after repair.
Days with Repair - Date difference between [Accepted by Assets] AND [Referred to Allocations]
Days vacant after Repair - Date difference between [Accepted by Allocations] AND [Let Date]
I commented out the line that converts the below query to a monthly view so you get any idea how the result looks on daily basis.
--distinct
--cast( cast(year(plant_date) as nvarchar(50))+'-'+cast(month(plant_date) as nvarchar(50))+'-1' as datetime) as plant_date
Required Out
The Days with Repair AND Days vacant after Repair should increment from month to month. Prior to the property is [Accepted by Assets], Days with Repair is 0.The count starts once the property is [Accepted by Assets] till the property is [Referred to Allocations]. So in this case the days with repair in Sep is 1 and 0 in October
Similarly Days vacant after Repair count starts from the day the property [Accepted by Allocations] til the property is let.
Days vacant after Repair in Sep - 18 days
Oct - 31 days
Declare @void TABLE
(
[History Ind] int
, [Prop Code] int
, [Void Start Date] varchar(10)
, [Accepted by Assets] varchar(10)
, [Referred to Allocations] varchar(10)
, [Accepted by Allocations] varchar(10)
, [Let Date] varchar(10)
)
INSERT INTO @void
([History Ind], [Prop Code], [Void Start Date], [Accepted by Assets], [Referred to Allocations], [Accepted by Allocations], [Let Date])
VALUES
(1, 3143, '2013-08-07', '2013-08-29', '2013-09-12', '2013-09-13', '2013-11-01')
;with tmp(plant_date) as
(
select cast('20000101' as datetime)
union all
select plant_date + 1
from tmp
where plant_date < '20201231'
)
select --distinct
--cast( cast(year(plant_date) as nvarchar(50))+'-'+cast(month(plant_date) as nvarchar(50))+'-1' as datetime) as plant_date
plant_date
,[History Ind]
,[Prop Code]
,[Void Start Date]
,[Accepted by Assets]
,[Referred to Allocations]
,[Accepted by Allocations]
,coalesce([Let Date], cast(getdate() as date)) As [Let Date]
,DATEDIFF(day, [Accepted by Assets], [Referred to Allocations]) AS [Days with Repairs]
,DATEDIFF(day, isnull([Accepted by Allocations], [Referred to Allocations]), isnull([Let Date], GETDATE())) As [Days Vacant After Repair]
,CASE WHEN Datepart(month,[Void Start Date]) = Datepart(month,plant_date) AND Datepart(YEAR,[Void Start Date]) = Datepart(YEAR,plant_date) AND plant_date < coalesce([Let Date], cast(getdate() as date))
THEN 'Void'
WHEN (plant_date > [Void Start Date] AND plant_date = coalesce([Let Date], cast(getdate() as date)))
THEN 'Let'
WHEN Datepart(month,[Void Start Date]) <> Datepart(month,plant_date) AND Datepart(YEAR,[Void Start Date]) = Datepart(YEAR,plant_date) AND plant_date < [Let Date]
THEN 'CFWD'
WHEN Datepart(month,[Void Start Date]) <> Datepart(month,plant_date) AND Datepart(YEAR,[Void Start Date]) = Datepart(YEAR,plant_date) AND [Let Date] IS NUll
THEN 'CFWD'
END as 'Void Status'
from tmp join @void v on plant_date between [Void Start Date] and coalesce([Let Date], cast(getdate() as date))
option (maxrecursion 0)
May 7, 2016 at 8:24 pm
Why not just use a Calendar table... you can use a table-valued function to generate a list of dates on the fly. Then it's just a pure count.
May 7, 2016 at 9:51 pm
Yes, i'm using a calendar table but how do I write the query to only show me the void status with plant_date as the month end date. Below is my actual query which joins to a calendar table. But I want the plant_date column to show only month end date like 30/04/2016, 31/05/2016
select distinct
cast( cast(year(plant_date) as nvarchar(50))+'-'+cast(month(plant_date) as nvarchar(50))+'-1' as datetime) as plant_date
,[History Ind]
,[Prop Code]
,[Void Start Date]
,[Let Date]
,DATEDIFF(day, [Accepted by Assets], [Referred to Allocations]) AS [Vacant UnTenantable]
,DATEDIFF(day, isnull([Accepted by Allocations], [Referred to Allocations]), isnull([Let Date], GETDATE())) As [Vacant Tenantable]
,CASE WHEN Datepart(month,[Void Start Date]) = Datepart(month,HCHDB.dbo.calendar.plant_date ) AND Datepart(YEAR,[Void Start Date]) = Datepart(YEAR,HCHDB.dbo.calendar.plant_date) AND HCHDB.dbo.calendar.plant_date < isnull([Let Date], GETDATE())
THEN 'Void'
WHEN HCHDB.dbo.calendar.plant_date > [Void Start Date] AND HCHDB.dbo.calendar.plant_date = [Let Date]
THEN 'Let'
WHEN HCHDB.dbo.calendar.plant_date = [Void Start Date] AND HCHDB.dbo.calendar.plant_date = [Let Date]
THEN 'Let'
WHEN Datepart(month,[Void Start Date]) <> Datepart(month,HCHDB.dbo.calendar.plant_date) AND Datepart(YEAR,[Void Start Date]) <= Datepart(YEAR,HCHDB.dbo.calendar.plant_date) AND HCHDB.dbo.calendar.plant_date < [Let Date]
THEN 'CFWD'
WHEN Datepart(month,[Void Start Date]) = Datepart(month,HCHDB.dbo.calendar.plant_date) AND Datepart(YEAR,[Void Start Date]) <= Datepart(YEAR,HCHDB.dbo.calendar.plant_date) AND HCHDB.dbo.calendar.plant_date < [Let Date]
THEN 'CFWD'
WHEN Datepart(month,[Void Start Date]) <> Datepart(month,HCHDB.dbo.calendar.plant_date) AND Datepart(YEAR,[Void Start Date]) = Datepart(YEAR,HCHDB.dbo.calendar.plant_date) AND [Let Date] IS NUll
THEN 'CFWD'
END as 'Void Status'
from HCHDB.dbo.calendar join vw_void_summary v on HCHDB.dbo.calendar.plant_date between [Void Start Date] and isnull([Let Date], GETDATE())
May 7, 2016 at 10:26 pm
This
cast( cast(year(plant_date) as nvarchar(50))+'-'+cast(month(plant_date) as nvarchar(50))+'-1' as datetime) as plant_date
is a calculated column in your Calendar table.
Sounds like you might want to look up Calendar tables. Here's a good article: http://www.sqlservercentral.com/articles/T-SQL/70482/
May 8, 2016 at 2:47 am
The below query is kind of working but stops at the Let_Date. In the example below property 3143 is let on 2016-03-14 and in the result set the property does not appear in March. Since the property was let on 2016-03-14, in March the Void Status should show CFWD & Let.
CFWD = Carry Forward. When a property is not let in the same month it is carried forward to the next month till its let.
Void = When the property got vacant
Let = when the property was filled back again.
Declare @void TABLE
(History_Ind int
, Prop_Code int
, Void_Start_Date datetime
, Let_Date datetime
)
INSERT INTO @Void
(History_Ind, Prop_Code, Void_Start_Date, Let_Date)
VALUES
(1, 3143, '2013-08-07', '2013/11/01'),
(2, 3143, '2014-07-22', '2014/08/08'),
(3, 3143, '2014-09-03', '2014/09/02'),
(4, 3143, '2016-01-19', '2016/03/14'),
(1, 3657, '2015-04-09', '2015/04/23'),
(0, 3657, '2016-04-21', NULL)
;with dates(plant_date) as
(
select cast('2013-02-01' as datetime)-1 as datetime
union all
select DATEADD(month, 1, plant_date+1)-1
from dates
where plant_date < GETDATE()
)
select plant_date
,History_Ind
,Prop_Code
,Void_Start_Date
,Let_Date
,CASE WHEN Datepart(month,Void_Start_Date) = Datepart(month,plant_date ) AND Datepart(YEAR,Void_Start_Date) = Datepart(YEAR,plant_date) AND plant_date < isnull(Let_Date, GETDATE())
THEN 'Void'
WHEN plant_date > Void_Start_Date AND plant_date = Let_Date
THEN 'Let'
WHEN plant_date = Void_Start_Date AND plant_date = Let_Date
THEN 'Let'
WHEN Datepart(month,Void_Start_Date) <> Datepart(month,plant_date) AND Datepart(YEAR,Void_Start_Date) <= Datepart(YEAR,plant_date) AND plant_date < Let_Date
THEN 'CFWD'
WHEN Datepart(month,Void_Start_Date) = Datepart(month,plant_date) AND Datepart(YEAR,Void_Start_Date) <= Datepart(YEAR,plant_date) AND plant_date < Let_Date
THEN 'CFWD'
WHEN Datepart(month,Void_Start_Date) <> Datepart(month,plant_date) AND Datepart(YEAR,Void_Start_Date) = Datepart(YEAR,plant_date) AND Let_Date IS NUll
THEN 'CFWD'
END as 'Void Status'
from dates join @void v on plant_date between Void_Start_Date and isnull([Let_Date], GETDATE())
where Prop_Code = '3143' AND History_Ind = '4'
option (maxrecursion 0)
May 8, 2016 at 4:45 am
could you please provide the results you are expecting from your sample query above...I think it will help clarify what you want.
Thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 8, 2016 at 5:15 am
Hi,
I have attached the required result. Check the Plant_Date and Void Status.
Regards,
Jag
May 8, 2016 at 6:42 am
normal way to post is like this...
SELECT '2016-01-31' AS plant_date
, 4 AS History_Ind
, 3143 AS Prop_Code
, '2016-01-19' AS Void_Start_Date
, '2016-03-14' AS Let_Date
, 'Void' AS Void_Status
UNION ALL
SELECT '2016-02-29',4,3143,'2016-01-19','2016-03-14','CFWD'
UNION ALL
SELECT '2016-03-31',4,3143,'2016-01-19','2016-03-14','CFWD'
UNION ALL
SELECT '2016-03-31',4,3143,'2016-01-19','2016-03-14','Let';
So your counting the days between Void_Start and Let?
May 8, 2016 at 8:09 am
ok....so what do you want to see when it has been 'let' for two months and then after two month is vacated (Void)?
do you still want 'CFWD' or something else
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply