mohan111 mohan111 - 6 months ago 12
SQL Question

how to get data dynamically in view basing on fiscal year

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

Answer

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)
Comments