Hi Expert ,
How to calculate number of days for last quarter ( 01/01/2020 - 31/12/2020). I have to run the report every quarter to calculate the number of days based on the Date condition. When I run for next Quarter 01/01/2021 -31/03/2021) the next time I run the SQL script it will be based on the next quart with the previous and current data.
Date column can be within the quarter, or it could come in before last quarter.
Supposing, I have a list of dates in Column A, and now, I want to get the number of days based on Column B and C as following screenshot shown. Suppose Date Column is less than Friday of Quarter than Calculate the Number of days (C-B). If Dates is within the quarter date, then calculate the (C-A). How could I deal with this job quickly and easily in SQL to get the number of days?
Below is the SQL script I'm using.
select
CreateDate as Date
,DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) AS Firstdayoflastquarter -- First day of last quarter
,DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))AS Lastdayoflastquarter-- Last day of last quarter
datediff(DD,
Caculate Number of Days
DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)),
DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -1, 0)) as Number days
From temptable
Many Thanks
Dan
