How to create logic for Fiscal year based on the date column from a Fact table

[tushar gaurav]

Hi,
In my report there are two prompts i.e year and week.
The start of week should be from sunday to saturday.
So if we select year =2020 and week = 1 then in report we need to show data from 29th Dec 2019 to 4th January2020 and so on.
Here we need to take the date field from a fact table.
Can someone please help me in creating the logic for this requirement.

[dougp]

It looks like both Oracle and SQL Server start the week on Monday by default.  2019-12-29 would be week 53 and 2019-12-30 would be week 1.  If you need something different, you'll need to design it based on your requirements.  Some things to consider:

What is the first day of the week?  (Sunday)
How do you define week 1?  Is this the week containing Jan 1?  Is it the week containing the first Thursday of the year?

Once you have your requirements defined, the code development should be mechanical.

[tushar gaurav]

Hi dougp,
Thanks for replying.
Yes ,the week starts from Sunday and end on saturday,now the problem is for 2020 year and week 1 the week start date is 29Dec2019 ,however the actual year is 2020 so we need to mask dates 29-Dec-2019 to 31-Dec-2019 so that it comes under year 2020.
I am not able to achieve this.