Difference count

• ramrajan

  SSCommitted

  Points: 1737

  More actions

  September 3, 2015 at 1:46 am

  #320994

  -- 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

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  September 3, 2015 at 2:08 am

  #1824204

  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

• ramrajan

  SSCommitted

  Points: 1737

  More actions

  September 3, 2015 at 3:52 am

  #1824220

  I just want the difference count. i am not sure why you are returning all data

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  September 3, 2015 at 4:08 am

  #1824225

  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

• ramrajan

  SSCommitted

  Points: 1737

  More actions

  September 3, 2015 at 4:30 am

  #1824230

  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.

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  September 3, 2015 at 4:54 am

  #1824234

  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.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 3, 2015 at 8:26 am

  #1824314

  ramrajan (9/3/2015)

  ---

  -- 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

  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) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1003857

  More actions

  September 3, 2015 at 9:29 am

  #1824354

  sgmunson (9/3/2015)

  ---

  ramrajan (9/3/2015)

  ---

  -- 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

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ramrajan

  SSCommitted

  Points: 1737

  More actions

  September 4, 2015 at 5:48 am

  #1824599

  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

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  September 4, 2015 at 6:38 am

  #1824610

  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

• ScottPletcher

  SSC Guru

  Points: 100946

  More actions

  September 4, 2015 at 10:24 am

  #1824735

  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) A socialist is someone who will give you the shirt off *someone else's* back.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 5, 2015 at 12:20 pm

  #1824833

  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) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1003857

  More actions

  September 5, 2015 at 5:07 pm

  #1824839

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)