How can i create a view Dynamically to get the data based on fiscal year(Financial year).
Lets have look at sample data where im having sample data.
Declare @t table(StartDate date )
insert into @t values('04/01/2012'),
('01/01/2012'),
('09/15/2013'),
('04/01/2014'),
('01/01/2015'),
('09/15/2015'),
('04/01/2016'),
('01/01/2017'),
('09/15/2016')
Just take an example if I have ran the view today I need to get from March 2016 to April 2017. If I have ran view on May 2017 I need to get data from march 2017 to upto may 2017. I can work it out in Sql server scripts or Stored procedure but how can I achieve the same result in Dynamic View or View . Suggest me !
my script
DECLARE @STARTDATE DATETIME, @ENDDATE DATETIME,@CURR_DATE DATETIME
SET @CURR_DATE='2016-06-01'
IF MONTH(@CURR_DATE) IN (1,2,3)
BEGIN
SET @STARTDATE= CAST( CAST(YEAR(@CURR_DATE)-1 AS VARCHAR)+'/04/01' AS DATE)
SET @ENDDATE= CAST( CAST(YEAR(@CURR_DATE) AS VARCHAR)+'/03/31' AS DATE)
END
ELSE
BEGIN
SET @STARTDATE= CAST( CAST(YEAR(@CURR_DATE) AS VARCHAR)+'/04/01' AS DATE)
SET @ENDDATE= CAST( CAST(YEAR(@CURR_DATE)+1 AS VARCHAR)+'/03/31' AS DATE)
END
select * from @t
where StartDate between
@STARTDATE AND @ENDDATE
order by year (StartDate)
it's giving data what I want for the fiscal year (2016-2017) but how can I use this and create a VIEW
You can use cte
with dates based on current date (GETDATE()
) in a view:
;WITH cte AS (
SELECT CASE WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST( CAST(YEAR(GETDATE())-1 AS VARCHAR)+'/04/01' AS DATE) ELSE CAST( CAST(YEAR(GETDATE()) AS VARCHAR)+'/04/01' AS DATE) END AS StartDate,
CASE WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST( CAST(YEAR(GETDATE()) AS VARCHAR)+'/03/31' AS DATE) ELSE CAST( CAST(YEAR(GETDATE())+1 AS VARCHAR)+'/03/31' AS DATE) END AS EndDate
)
SELECT t.*
FROM YourTable t
INNER JOIN cte c
ON t.StartDate between c.StartDate AND c.EndDate
ORDER BY year(t.StartDate)