Hi,
Suppose I have an script that use calendar table to retrieve record or identify current month record and next month record.
let say an example. if the lastday of the current month is Oct 28 i will use this as my firstday of next month which the records will point to 2016-11.
Lastday = lastday value can be change 2 to 3 days if some items are not yet done or there are still need to process at the floor(production) or the transaction was done earlier from the lastday date.
it can be change the lastday 2016-10-27 or 2016-10-29, then the cmp_month for next month the fistday should be like this 2016-10-27 or 2016-10-29 and the lastday of next month will be 2016-11-28.
Thank you in advance.
[code]create table #sample
(cmp_month nvarchar(7), firstday datetime, lastday datetime)
insert into #sample(cmp_month,firstday,lastday)values('2016-10','2016-09-28 00:00:00.000','2016-10-28 00:00:00.000')
insert into #sample(cmp_month,firstday,lastday)values('2016-11','2016-10-28 00:00:00.000','2016-11-28 00:00:00.000')
insert into #sample(cmp_month,firstday,lastday)values('2016-12','2016-11-28 00:00:00.000','2016-12-28 00:00:00.000')
--declare variables
Declare @dt datetime
Declare @cmpDate nvarchar(7)
Declare @currMonth as nvarchar(10)
Declare @nextMonth as nvarchar(10)
Set @dt =dateadd(day,0,datediff(day,0,GETDATE()))
set @cmpDate=cast(datepart(yyyy,getdate()) as varchar(4))+'-'+right('00' + cast(datepart(mm,getdate()) as varchar(2)), 2)
select *
select * from #sample
IF ??????
BEGIN
print 'current PO'
Set @currMonth = cast(datepart(yyyy,getdate()) as varchar(4))+right('00' + cast(datepart(mm,getdate()) as varchar(2)), 2)
set @nextMonth = cast(datepart(yyyy,getdate()) as varchar(4))+right('00' + cast(datepart(mm,getdate())+1 as varchar(2)), 2)
END
ELSE
BEGIN
print 'next PO'
Set @currMonth = cast(datepart(yyyy,getdate()) as varchar(4))+'-'+right('00' + cast(datepart(mm,getdate()) as varchar(2)), 2)
set @nextMonth = cast(datepart(yyyy,getdate()) as varchar(4))+'-'+right('00' + cast(datepart(mm,getdate())+2 as varchar(2)), 2)
END
---will do some scripting[/code]