question

ShekarNandi-3665 avatar image
0 Votes"
ShekarNandi-3665 asked ShekarNandi-3665 commented

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

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-generalsql-server-transact-sql
number-of-days.png (7.9 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.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ShekarNandi-3665 commented

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
· 10
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.

Thanks Yuan it worked :-). Your genius....56882-capture2.png


0 Votes 0 ·
capture2.png (9.0 KiB)

![![S][1]][2]


Forgot to mention one more conditiopn I need to meet which is in column E a s following screenshot shown. In this case Number of days should be Column E - Column B
![56912-image.png][1]


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 ColB> Cole
END
FROM YourTable

i TRIED ABOUT Code it doesnt work.
[1]: /answers/storage/temp/56912-image.png

0 Votes 0 ·
image.png (11.6 KiB)

In which condition, you would like to use ColumnE - ColumnB? Do you mean when ColumnE exists, use ColumnE - ColumnB? If that is a case, try this:

 SELECT 
     CASE 
         WHEN ColE IS NOT NULL THEN DATEDIFF(dd, ColB, ColE)
         ELSE
             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
     END
 FROM YourTable
1 Vote 1 ·
Show more comments
SQLZealots avatar image
0 Votes"
SQLZealots answered

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/

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.