How To Calculate number of days based on Quarter Start Date and End Date Based with Given Date condition In SQL?

Shekar Nandi 21 Reputation points
2021-01-14T16:00:14.767+00:00

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

Dan56653-number-of-days.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-01-14T19:12:06.67+00:00

    Should be like this:

    SELECT 
        CASE 
            WHEN ColA < ColB THEN DATEDIFF(dd, ColB, ColC)
            WHEN ColA >= ColB AND ColA <= ColC THEN DATEDIFF(dd, ColA, ColC)
            ELSE -- Something else if ColA > ColC
        END
    FROM YourTable
    

1 additional answer

Sort by: Most helpful
  1. SQLZealots 276 Reputation points
    2021-01-14T17:01:09.937+00:00

    With calendar table , you can easily achieve this. Please refer the blog post for building a simple Calendar table as below.

    https://sqlzealots.com/2020/11/13/calendar-table-in-sql-server/

    0 comments No comments