how to get data dynamically in view basing on fiscal year

Question

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

Can you please elaborate on what exactly determines the start/end dates of your query? — Giorgos Betsos, Jun 01 '16 at 07:25

score 3 · Answer 1 · answered Jun 01 '16 at 08:12

3

select t.*,getdate() 
    from @t t
    where   year(startdate) * 100 + month(startdate) >=
        case 
        when  month(getdate()) in (1,2,3) then (year(getdate()) * 100) + 3 - 100
        else  (year(getdate()) * 100) + 3 
        end

answered Jun 01 '16 at 08:12

P.Salmon

17,104
2
12
19

it's not giving exact result based fiscal year – mohan111 Jun 01 '16 at 08:38

score 1 · Accepted Answer · answered Jun 01 '16 at 07:57

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)

this is fine..some where I have forgot CTE – mohan111 Jun 01 '16 at 08:45 — mohan111, Jun 01 '16 at 08:45

score 0 · Answer 3 · answered Jun 01 '16 at 06:57

0

If you already worked out the code in a script\stored procedure you can re-use such code in a Table-Valued User-Defined Functions.

That way you will be able to query the UDF like a view.

answered Jun 01 '16 at 06:57

rverdelli

48
5

I just want to use this code in View only not in UDF – mohan111 Jun 01 '16 at 07:07

score 0 · Answer 4 · answered Jun 01 '16 at 07:30

You can try something like this:

select t.* 
from @t t
cross join (
   select startdate = case 
                         when MONTH(@CURR_DATE) IN (1,2,3)
                            then CAST( CAST(YEAR(@CURR_DATE)-1 AS VARCHAR)+'/04/01'  AS DATE)
                         else CAST( CAST(YEAR(@CURR_DATE) AS VARCHAR)+'/04/01'  AS DATE)
                      end) s
cross join (
   select enddate = case 
                       when MONTH(@CURR_DATE) IN (1,2,3)
                          then CAST( CAST(YEAR(@CURR_DATE)  AS VARCHAR)+'/03/31'  AS DATE)
                       else CAST( CAST(YEAR(@CURR_DATE)+1 AS VARCHAR)+'/03/31'  AS DATE)
                    end) e
where t.StartDate between s.startdate and e.enddate
order by year (t.StartDate)

how to get data dynamically in view basing on fiscal year

4 Answers4