This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(118.4, 37%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESN%3C/text%3E%3C/svg%3E)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(240, 3%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
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
Thanks Yuan it worked :-). Your genius....
][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
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.
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
Thanks Yuan, Yes codes works as needed, as the Settle date comes between last Quarter ( 01/01/2020 - 31/12/2020). what will happen when I run same SQL query for Next Quarter which is 01/01/2021 - 31/03/2021. Then it should calculate o days because it already settled in last quarter.?
I think there is another column for some like Customer, Client, User, right?
I have to run the report on every Quarter. Number of days is based on Each Quarter with condition of COlumn A and E. 
Scenario:
a s following screenshot shown
Date:
Q1 - Jan 2020- March 2020
Q2- Apri l 2020 - June 2020
Q3 July 2020-Sept 2020
Q4 Oct 2020- Dec 2020 (,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)
Q1 - Jan 2021- March 2021
Q2- Apri 2021- June 2021
Q3 July 2021-Sept 2021
Q4 Oct 2021- Dec 2021
SELECT
CASE
WHEN ColE IS NOT NULL THEN
CASE
WHEN ColE < ColB THEN 0
ELSE DATEDIFF(dd, ColB, ColE)
END
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
it says Incorrect syntax near the keyword 'CASE'.
SELECT
CASE
WHEN ColE IS NOT NULL
CASE
WHEN ColE < ColB THEN 0
ELSE DATEDIFF(dd, ColB, ColE)
END
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
Sorry I missed a "THEN" after WHEN ColE IS NOT NULL. I updated it.
Spot On Thanks You so much it worked.
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/