May 7, 2016 at 7:37 pm
Hi,
I have the big query below and want the plant_month column to show only end of the month date. How can I apply the dateparam query in the big query below
with dates(dateparam) as
(
select cast('2013-02-01' as datetime)-1 as datetime
union all
select DATEADD(month, 1, dateparam+1)-1
from dates
where dateparam < GETDATE()
)
Big Query that i need to change to show Void Status Per Month
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)
SELECT * FROM @Void
;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
,History_Ind
,Prop_Code
,Void_Start_Date
,coalesce(Let_Date, cast(getdate() as date)) As 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 < 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 8, 2016 at 12:15 pm
May 9, 2016 at 8:31 am
pietlinden (5/8/2016)
This is Lynn Pettis' calculation[/url] for first day of the next month...
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next monthlast day of the month is the day before that, so
SELECT dateadd(d,dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0),-1)
I actually prefer this (slightly shorter) code for the last day of the current month.
SELECT DATEADD(mm, DATEDIFF(mm, -1, @ThisDate), -1)
Of course, in SQL2012 you can simply use
SELECT EOMONTH(@ThisDate)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply