September 3, 2015 at 1:46 am
-- Find Quarter difference count
declare @enddate datetime = '2015-09-03 00:39:53.973'
declare @startdate datetime = '2014-11-07 00:42:39.920'
WITH Quarters AS (
SELECT Q = '1', MonthBegin = 1, MonthEnd = 3 UNION
SELECT Q = '2', MonthBegin = 4, MonthEnd = 6 UNION
SELECT Q = '3', MonthBegin = 7, MonthEnd = 9 UNION
SELECT Q = '4', MonthBegin = 10, MonthEnd = 12
)
SELECT * FROM Quarters
-- I need to find total count difference between start date and enddate
@startdate - Fourth Quarter - 2014
@enddate - Three Quarter - 2015
Here in this case i should get quarter difference count as 4 . So how to get the difference count
September 3, 2015 at 2:08 am
It is best if you can have calendar table in your database. will help you a lot in these kind of queries.
However, you can do something like this as well
declare @enddate datetime = cast('2015-09-03 00:39:53.973' as date)
declare @startdate datetime = cast('2014-11-07 00:42:39.920' as date)
SELECT DISTINCT
year(DATEADD(dd, rn, @startdate))
, DATEPART(Quarter, DATEADD(dd, rn, @startdate))
from
(
select TOP 110000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS RN
from sys.all_columns c
cross join sys.all_columns cc
) Tally
where DATEADD(dd, rn, @startdate) <= @enddate
hope it helps
September 3, 2015 at 3:52 am
I just want the difference count. i am not sure why you are returning all data
September 3, 2015 at 4:08 am
I thought you might have figure it out but my bad, here is the complete version
declare @enddate datetime = cast('2015-09-03 00:39:53.973' as date)
declare @startdate datetime = cast('2014-11-07 00:42:39.920' as date)
SELECT COUNT(1) AS QuaterCount
FROM
(
SELECT DISTINCT
year(DATEADD(dd, rn, @startdate)) AS CalendarYear
, DATEPART(Quarter, DATEADD(dd, rn, @startdate)) AS CalendarQuarter
from
(
select TOP 110000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS RN
from sys.all_columns c
cross join sys.all_columns cc
) Tally
where DATEADD(dd, rn, @startdate) <= @enddate
) A
September 3, 2015 at 4:30 am
some of the thing s looks really complicated to me using TOP 110001 and also using sys.all_columns table, do you suggest any alternative simple approach, because my lead asking me soo many question why we used this. also not in understandable way.
September 3, 2015 at 4:54 am
You can avoid most of this by simply creating a Calendar Table in your database. you query will become a lot simpler.
Why i used sys.all_columns (Btw well you can use other table of your liking) is because i need to generate a Tally Table. If you do not have any understanding about it then this is the best time to get your hands on it. Check following links
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Tally Table Uses - Part I[/url]
Tally Table Uses - Part II[/url]
to generate a date series between two dates i need a tally table which generate dates from StartDate to EndDate.
Once i all the dates i can calculate the Quarter, Year, Month for each of the date. ( These columns will be by default available in Calendar table).
Once you get all of these field you can calculate the quarter duration between two dates.
Its look complicated but its not its all about understanding.
hope it helps.
September 3, 2015 at 8:26 am
ramrajan (9/3/2015)
-- Find Quarter difference countdeclare @enddate datetime = '2015-09-03 00:39:53.973'
declare @startdate datetime = '2014-11-07 00:42:39.920'
WITH Quarters AS (
SELECT Q = '1', MonthBegin = 1, MonthEnd = 3 UNION
SELECT Q = '2', MonthBegin = 4, MonthEnd = 6 UNION
SELECT Q = '3', MonthBegin = 7, MonthEnd = 9 UNION
SELECT Q = '4', MonthBegin = 10, MonthEnd = 12
)
SELECT * FROM Quarters
-- I need to find total count difference between start date and enddate
@startdate - Fourth Quarter - 2014
@enddate - Three Quarter - 2015
Here in this case i should get quarter difference count as 4 . So how to get the difference count
Of far more importance than the "how", is the fact that you are expecting a result of 4. Run the following:
declare @startdate datetime = '2014-11-07 00:42:39.920'
declare @enddate datetime = '2015-09-03 00:39:53.973'
SELECT DATEDIFF(quarter, @startdate, @enddate) AS QUARTER_DIFFERENCE
The result that SQL Server thinks is the difference in quarters between those two dates is 3, and SQL Server is correct.
So my question is, why do you want 4 as the answer? If you are going to count the quarter that the @startdate is in,
then all you need to do is add 1 to the result, as follows:
declare @startdate datetime = '2014-11-07 00:42:39.920'
declare @enddate datetime = '2015-09-03 00:39:53.973'
SELECT DATEDIFF(quarter, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
Let us know what works for you.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 3, 2015 at 9:29 am
sgmunson (9/3/2015)
ramrajan (9/3/2015)
-- Find Quarter difference countdeclare @enddate datetime = '2015-09-03 00:39:53.973'
declare @startdate datetime = '2014-11-07 00:42:39.920'
WITH Quarters AS (
SELECT Q = '1', MonthBegin = 1, MonthEnd = 3 UNION
SELECT Q = '2', MonthBegin = 4, MonthEnd = 6 UNION
SELECT Q = '3', MonthBegin = 7, MonthEnd = 9 UNION
SELECT Q = '4', MonthBegin = 10, MonthEnd = 12
)
SELECT * FROM Quarters
-- I need to find total count difference between start date and enddate
@startdate - Fourth Quarter - 2014
@enddate - Three Quarter - 2015
Here in this case i should get quarter difference count as 4 . So how to get the difference count
Of far more importance than the "how", is the fact that you are expecting a result of 4. Run the following:
declare @startdate datetime = '2014-11-07 00:42:39.920'
declare @enddate datetime = '2015-09-03 00:39:53.973'
SELECT DATEDIFF(quarter, @startdate, @enddate) AS QUARTER_DIFFERENCE
The result that SQL Server thinks is the difference in quarters between those two dates is 3, and SQL Server is correct.
So my question is, why do you want 4 as the answer? If you are going to count the quarter that the @startdate is in,
then all you need to do is add 1 to the result, as follows:
declare @startdate datetime = '2014-11-07 00:42:39.920'
declare @enddate datetime = '2015-09-03 00:39:53.973'
SELECT DATEDIFF(quarter, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
Let us know what works for you.
+1000.
I wonder how many quarters the OP would want for the following...
declare @startdate datetime = '2015-03-31 23:59:59.097';
declare @enddate datetime = '2015-04-01 00:00:00.000';
--Jeff Moden
September 4, 2015 at 5:48 am
declare @startdate datetime = '2015-03-31 23:59:59.097';
declare @enddate datetime = '2015-04-01 00:00:00.000';
returns 2
Awesome simplified solution with SELECT DATEDIFF(Year, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
September 4, 2015 at 6:38 am
ramrajan (9/4/2015)
declare @startdate datetime = '2015-03-31 23:59:59.097';declare @enddate datetime = '2015-04-01 00:00:00.000';
returns 2
Awesome simplified solution with SELECT DATEDIFF(Year, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
Correction Year should be replaceed with Quarter Keyword
SELECT DATEDIFF(quarter, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
September 4, 2015 at 10:24 am
You certainly don't need a calendar table for that simple a calculation! All you need is:
SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
declare @enddate datetime = '2015-09-03 00:39:53.973' --2015,q3
declare @startdate datetime = '2014-11-07 00:42:39.920' --2014,q4 = 4qs "touched"
SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
set @enddate = '2015-06-30 00:39:53.973' --2015,q2
set @startdate = '2014-01-01 00:42:39.920' --2014,q1 = 6 qs "touched"
SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 5, 2015 at 12:20 pm
ScottPletcher (9/4/2015)
You certainly don't need a calendar table for that simple a calculation! All you need is:SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
declare @enddate datetime = '2015-09-03 00:39:53.973' --2015,q3
declare @startdate datetime = '2014-11-07 00:42:39.920' --2014,q4 = 4qs "touched"
SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
set @enddate = '2015-06-30 00:39:53.973' --2015,q2
set @startdate = '2014-01-01 00:42:39.920' --2014,q1 = 6 qs "touched"
SELECT (DATEDIFF(MONTH, 0, @enddate) / 3) - (DATEDIFF(MONTH, 0, @startdate) / 3) + 1
Is there a reason that DATEDIFF(quarter, @startdate, @enddate) or possibly adding 1 to that to count the quarter that the start date is in, is insufficient?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 5, 2015 at 5:07 pm
twin.devil (9/4/2015)
ramrajan (9/4/2015)
declare @startdate datetime = '2015-03-31 23:59:59.097';declare @enddate datetime = '2015-04-01 00:00:00.000';
returns 2
Awesome simplified solution with SELECT DATEDIFF(Year, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
Correction Year should be replaceed with Quarter Keyword
SELECT DATEDIFF(quarter, @startdate, @enddate) + 1 AS QUARTER_DIFFERENCE
Considering that there are only 3 milliseconds between those two dates, are you really, really sure that the answer should be "2" for Quarter_Difference? Personally, I don't believe so.
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply