Help with next date in SQL query

• Jeff Moden

  SSC Guru

  Points: 1003870

  More actions

  May 30, 2013 at 10:44 am

  #1619789

  Tyekhan (5/30/2013)

  ---

  Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

  Jeff Moden (5/30/2013)

  ---

  So, any feedback on my previous comment of...

  Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

  WHICH ID? Certainly not the HistoryID, right?

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

• Tyekhan

  Old Hand

  Points: 304

  More actions

  May 30, 2013 at 3:10 pm

  #1619917

  Sorry the PID in the table that is unique ID per customer. --- [PID] [int] NOT NULL,

  Jeff Moden (5/30/2013)

  ---

  Tyekhan (5/30/2013)

  ---

  Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

  Jeff Moden (5/30/2013)

  ---

  So, any feedback on my previous comment of...

  Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.

  WHICH ID? Certainly not the HistoryID, right?

• Tyekhan

  Old Hand

  Points: 304

  More actions

  June 4, 2013 at 6:36 am

  #1620918

  Hope this helps a bit,

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

  DROP TABLE #mytable

  --===== Create the test table with

  CREATE TABLE #mytable

  (

  [HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [SystemID] [tinyint] NOT NULL,

  [PID] [int] NOT NULL,

  [Name] [varchar](35) NOT NULL,

  [Source] [varchar](32) NOT NULL,

  [SourceField] [varchar](32) NOT NULL,

  [SourceID] [varchar](32) NOT NULL,

  [CallDateTime] [datetime] NOT NULL,

  [Num] [varchar](15) NOT NULL,

  [CallData] [varchar](240) NOT NULL,

  [ANI] [varchar](15) NOT NULL,

  [RC] [varchar](5) NOT NULL,

  [ID] [int] NOT NULL,

  --===== Insert the test data into the test table

  INSERT INTO #History

  (ID, Name, CallDateTime, RC, Num)

  SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

  SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

  SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

  SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

  SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

  SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

  SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

  SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

  SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

  SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

  SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

  SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

  SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

  SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

  SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

  SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

  SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

  SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

  SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

  I'm using Server 2008 Management Studio.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 4, 2013 at 9:41 am

  #1621031

  Tyekhan (6/4/2013)

  ---

  Hope this helps a bit,

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

  DROP TABLE #mytable

  --===== Create the test table with

  CREATE TABLE #mytable

  (

  [HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [SystemID] [tinyint] NOT NULL,

  [PID] [int] NOT NULL,

  [Name] [varchar](35) NOT NULL,

  [Source] [varchar](32) NOT NULL,

  [SourceField] [varchar](32) NOT NULL,

  [SourceID] [varchar](32) NOT NULL,

  [CallDateTime] [datetime] NOT NULL,

  [Num] [varchar](15) NOT NULL,

  [CallData] [varchar](240) NOT NULL,

  [ANI] [varchar](15) NOT NULL,

  [RC] [varchar](5) NOT NULL,

  [ID] [int] NOT NULL,

  --===== Insert the test data into the test table

  INSERT INTO #History

  (ID, Name, CallDateTime, RC, Num)

  SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

  SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

  SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

  SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

  SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

  SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

  SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

  SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

  SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

  SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

  SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

  SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

  SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

  SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

  SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

  SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

  SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

  SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

  SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

  I'm using Server 2008 Management Studio.

  ok....will throw in an idea for you to consider.

  first off...the script you posted above will not work...you need to test this before you post.

  I leave you to sort that..

  second you fail to provide data for all columns

  third...your sample of dates (days) is only two dates....???

  anyways, based on what I think you need here is some code to play with

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..History','U') IS NOT NULL

  DROP TABLE History

  --===== Create the test table with

  CREATE TABLE History

  (

  [HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [SystemID] [tinyint] NULL,

  [PID] [int] NULL,

  [Name] [varchar](35) NOT NULL,

  [Source] [varchar](32) NULL,

  [SourceField] [varchar](32) NULL,

  [SourceID] [varchar](32) NULL,

  [CallDateTime] [datetime] NULL,

  [Num] [varchar](15) NULL,

  [CallData] [varchar](240) NULL,

  [ANI] [varchar](15) NULL,

  [RC] [varchar](10) NULL,

  [ID] [int] NULL,

  )

  --===== Insert the test data into the test table

  INSERT INTO History

  (ID, Name, CallDateTime, RC, Num)

  SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL

  SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL

  SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL

  SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL

  SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL

  SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL

  SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL

  SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL

  SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL

  SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL

  SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL

  SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL

  SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL

  SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL

  SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL

  SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL

  SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL

  SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL

  SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'

  SELECT HistoryID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

  FROM History

  ORDER BY Name, CallDateTime

  ;

  with CTE as

  (

  SELECT ID, Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn

  FROM History

  WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

  )

  SELECT firstcall.ID

  , firstcall.Name

  , firstcall.CallDateTime

  , firstcall.Num

  , firstcall.RC

  , nextcall.CallDateTime AS NextCallDateTime

  , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

  FROM

  cte AS firstcall INNER JOIN cte AS nextcall

  ON firstcall.Name = nextcall.Name

  WHERE (firstcall.rn = 1)

  AND (nextcall.rn = 2);

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Tyekhan

  Old Hand

  Points: 304

  More actions

  June 5, 2013 at 4:26 am

  #1621266

  Thanks

  I have added PID to the data below The PID is unique ID per customer so on the below i want to see

  PID = 22

  calldatetime 25/02/2012

  NextCallDateTime = 28/02/2013

  numberofdays = 3

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL

  DROP TABLE HistoryTEST

  --===== Create the test table with

  CREATE TABLE HistoryTEST

  (

  [HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [SystemID] [tinyint] NULL,

  [PID] [int] NULL,

  [Name] [varchar](35) NOT NULL,

  [Source] [varchar](32) NULL,

  [SourceField] [varchar](32) NULL,

  [SourceID] [varchar](32) NULL,

  [CallDateTime] [datetime] NULL,

  [Num] [varchar](15) NULL,

  [CallData] [varchar](240) NULL,

  [ANI] [varchar](15) NULL,

  [RC] [varchar](10) NULL,

  [ID] [int] NULL,

  )

  --===== Insert the test data into the test table

  INSERT INTO HistoryTEST

  (ID, Name, CallDateTime, RC, Num, PID)

  SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL

  SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL

  SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL

  SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL

  SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL

  SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL

  SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL

  SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL

  SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL

  SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL

  SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL

  SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL

  SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL

  SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL

  SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL

  SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL

  SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL

  SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL

  SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL

  SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL

  SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL

  SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42'

  SELECT HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

  FROM HistoryTEST

  ORDER BY Name, CallDateTime

  ;

  with CTE as

  (

  SELECT ID, PID,Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn

  FROM HistoryTEST

  WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

  )

  SELECT firstcall.ID

  , firstcall.PID

  , firstcall.Name

  , firstcall.CallDateTime

  , firstcall.Num

  , firstcall.RC

  , nextcall.CallDateTime AS NextCallDateTime

  , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

  FROM

  cte AS firstcall INNER JOIN cte AS nextcall

  ON firstcall.Name = nextcall.Name

  WHERE (firstcall.rn = 1)

  AND (nextcall.rn = 2);

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 5, 2013 at 7:15 am

  #1621338

  suggest you revisit the solution that Jeff Moden gave you.....just swap out the table name and I think you will need to replace "Num" with "PID"...that should give you what you want.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Tyekhan

  Old Hand

  Points: 304

  More actions

  June 5, 2013 at 9:33 am

  #1621383

  I have replace name with PID, but its coming up blank

  Any ideas

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL

  DROP TABLE HistoryTEST

  --===== Create the test table with

  CREATE TABLE HistoryTEST

  (

  [HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

  [SystemID] [tinyint] NULL,

  [PID] [int] NULL,

  [Name] [varchar](35) NOT NULL,

  [Source] [varchar](32) NULL,

  [SourceField] [varchar](32) NULL,

  [SourceID] [varchar](32) NULL,

  [CallDateTime] [datetime] NULL,

  [Num] [varchar](15) NULL,

  [CallData] [varchar](240) NULL,

  [ANI] [varchar](15) NULL,

  [RC] [varchar](10) NULL,

  [ID] [int] NULL,

  )

  --===== Insert the test data into the test table

  INSERT INTO HistoryTEST

  (ID, Name, CallDateTime, RC, Num, PID)

  SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL

  SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL

  SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL

  SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL

  SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL

  SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL

  SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL

  SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL

  SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL

  SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL

  SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL

  SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL

  SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL

  SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL

  SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL

  SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL

  SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL

  SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL

  SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL

  SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL

  SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL

  SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL

  SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42'

  SELECT HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

  FROM HistoryTEST

  ORDER BY PID, CallDateTime

  ;

  with CTE as

  (

  SELECT ID, PID,Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [PID] order by calldatetime ) as rn

  FROM HistoryTEST

  WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

  )

  SELECT firstcall.ID

  , firstcall.PID

  , firstcall.Name

  , firstcall.CallDateTime

  , firstcall.Num

  , firstcall.RC

  , nextcall.CallDateTime AS NextCallDateTime

  , datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

  FROM

  cte AS firstcall INNER JOIN cte AS nextcall

  ON firstcall.PID = nextcall.PID

  WHERE (firstcall.rn = 1)

  AND (nextcall.rn = 2);

  J Livingston SQL (6/5/2013)

  ---

  suggest you revisit the solution that Jeff Moden gave you.....just swap out the table name and I think you will need to replace "Num" with "PID"...that should give you what you want.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 5, 2013 at 9:39 am

  #1621390

  that's not the code from Jeff Moden...that's mine...go back a few posts

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Tyekhan

  Old Hand

  Points: 304

  More actions

  June 5, 2013 at 9:55 am

  #1621409

  Tired that code again but still not working,

  Randomizing ContactID's and CallDateTime's...

  (1000000 row(s) affected)

  Building/populating the #HistoryTest table...

  Msg 208, Level 16, State 1, Line 19

  Invalid object name 'AdventureWorks.Person.Contact'.

  J Livingston SQL (6/5/2013)

  ---

  that's not the code from Jeff Moden...that's mine...go back a few posts

• Jeff Moden

  SSC Guru

  Points: 1003870

  More actions

  June 5, 2013 at 10:56 am

  #1621426

  Tyekhan (6/5/2013)

  ---

  Tired that code again but still not working,

  Randomizing ContactID's and CallDateTime's...

  (1000000 row(s) affected)

  Building/populating the #HistoryTest table...

  Msg 208, Level 16, State 1, Line 19

  Invalid object name 'AdventureWorks.Person.Contact'.

  J Livingston SQL (6/5/2013)

  ---

  that's not the code from Jeff Moden...that's mine...go back a few posts

  I just haven't had the time to come up with good example data. You should take the time to either install the AdventureWorks database or read the solution code I posted to warp it to your needs or read the first link in my signature line below for how to post some usable data for us to demonstrate a solution with.

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

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 5, 2013 at 11:56 am

  #1621449

  Jeff Moden (6/5/2013)

  ---

  Tyekhan (6/5/2013)

  ---

  Tired that code again but still not working,

  Randomizing ContactID's and CallDateTime's...

  (1000000 row(s) affected)

  Building/populating the #HistoryTest table...

  Msg 208, Level 16, State 1, Line 19

  Invalid object name 'AdventureWorks.Person.Contact'.

  J Livingston SQL (6/5/2013)

  ---

  that's not the code from Jeff Moden...that's mine...go back a few posts

  I just haven't had the time to come up with good example data. You should take the time to either install the AdventureWorks database or read the solution code I posted to warp it to your needs or read the first link in my signature line below for how to post some usable data for us to demonstrate a solution with.

  Jeffs code suitably altered to your needs

  --=======================================================================================

  -- Solve the problem

  --=======================================================================================

  DECLARE @pGivenDT DATETIME, --This could be a parameter for a stored proc.

  @StartDay DATETIME,

  @nextday DATETIME

  ;

  SELECT @pGivenDT = '2012-02-25',

  @StartDay = DATEDIFF(dd,0,@pGivenDT),

  @nextday = DATEADD(dd,1,@StartDay)

  ;

  WITH

  cteFindGiven AS

  ( --=== Find the calls on the day desired

  SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY PID,CallDateTime),

  ID, PID,Name, CallDateTime, Num, RC

  FROM HistoryTest

  WHERE CallDateTime >= @StartDay

  AND CallDateTime < @nextday

  ),

  cteFindNext AS

  ( --=== Find the first call for each Num after the given day.

  SELECT PID,

  CallDateTime = MIN(CallDateTime)

  FROM HistoryTest

  WHERE CallDateTime >= @nextday

  GROUP BY PID

  )

  --===== Join the two CTE's to produce the desired output

  SELECT g.PID,

  g.Name,

  g.CallDateTime,

  g.Num,

  g.RC,

  [Next CallDateTime] = n.CallDateTime,

  [Total Number of Days] = DATEDIFF(dd,g.CallDateTime,n.CallDateTime)

  FROM cteFindGiven g

  LEFT JOIN cteFindNext n --Remove "LEFT" to get just the matches after QA

  ON n.PID = g.PID

  WHERE g.RowNum = 1

  by the way..you really do need to understand the code and how it works...you after all are going to have to support it. If you care to read all the comments Jeff has kindly included...this will give you a good start...post back with questions as necessary.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day