question

MaheshBirajdar-1825 avatar image
0 Votes"
MaheshBirajdar-1825 asked MaheshBirajdar-1825 answered

FISCAL_WEEK from 1 Jan till First Friday

Hi,

We have one requirement. Wee need to create a fiscal_week column in SQL_server table.
Table will have data as Normal_date column as fiscal_date and fiscal_year colum which will have year part of the date.

Logic for FISCAL_WEEK is as below.

  • FIRST FISCAL WEEK WILL START FROM 1 JAN OF EVERY YEAR AND IT WILL BE TILL FIRST FRIDAY.

  • SECOND WEEK STARTS FROM SATURDAY AND IT WILL RUN TILL NEXT FRIDAY.

  • THIS WILL GO ON TILL THE END OF YEAR (31 JAN)

We will Have data something as below table.

118381-image.png

How would I create query for this ?We will have data from 2010 till 2035 years in the table.

Thanks,
Mahesh

sql-server-transact-sql
image.png (43.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MaheshBirajdar-1825 avatar image
0 Votes"
MaheshBirajdar-1825 answered

Hi,

Thank you for reply.

I got answer as below.

SET DATEFIRST 6
GO

SELECT FISCAL_DATE, DATEPART(WEEK,Fiscal_Date) FISCAL_WEEK FROM ERP.CUSTOM_FISCAL_CALENDER

Thanks,
Mahesh

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-9594 avatar image
0 Votes"
GuoxiongYuan-9594 answered

Since the function DATENAME(week, FISCAL_DATE)) returns the number of the week starting on Sunday and ending on Saturday, you can use a CASE statement to check if DATENAME(weekday, FISCAL_DATE) is Saturday. If yes, FISCAL_WEEK = CONVERT(tinyint, DATEPART(week, FISCAL_DATE)) + 1. Otherwise FISCAL_WEEK = CONVERT(tinyint, DATEPART(week, FISCAL_DATE)).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.