Need Solution

• balu.arunkumar

  Ten Centuries

  Points: 1132

  More actions

  October 17, 2014 at 6:18 am

  #311941

  Hi,

  I need query for below problem.

  As per my source data, if anyone purchase within 48 hours it should be considered as single purchase.

  I have 1 million unique names in my source data table. If i go with "While loop" it took so much of time. Any alternate way there??

  Source Data:

  Create Table #Temp_data(Seq int,Name varchar(1),Purchased_date datetime)

  insert into #Temp_data select 1,'A','2014-10-07 13:04:16.887'

  insert into #Temp_data select 2,'A','2014-10-07 16:04:16.887'

  insert into #Temp_data select 3,'A','2014-10-08 01:04:16.887'

  insert into #Temp_data select 4,'A','2014-10-13 13:05:02.437'

  insert into #Temp_data select 5,'A','2014-10-13 20:05:02.437'

  insert into #Temp_data select 6,'A','2014-10-17 13:05:29.077'

  insert into #Temp_data select 1,'B','2014-10-06 13:05:41.830'

  insert into #Temp_data select 2,'B','2014-10-06 16:05:41.830'

  insert into #Temp_data select 3,'B','2014-10-07 05:05:41.830'

  insert into #Temp_data select 4,'B','2014-10-12 13:06:16.263'

  insert into #Temp_data select 5,'B','2014-10-12 23:06:16.263'

  I need output in below format.

  Output Query:

  Create Table #Temp_Out(Seq int,Name varchar(1),Purchased_date datetime,Newseq int)

  insert into #Temp_Out(Seq,Name,Purchased_date) select 1,'A','2014-10-07 13:04:16.887'

  insert into #Temp_Out(Seq,Name,Purchased_date) select 2,'A','2014-10-07 16:04:16.887'

  insert into #Temp_Out(Seq,Name,Purchased_date,Newseq) select 3,'A','2014-10-08 01:04:16.887',1

  insert into #Temp_Out(Seq,Name,Purchased_date) select 4,'A','2014-10-13 13:05:02.437'

  insert into #Temp_Out(Seq,Name,Purchased_date,Newseq) select 5,'A','2014-10-13 20:05:02.437',2

  insert into #Temp_Out(Seq,Name,Purchased_date,Newseq) select 6,'A','2014-10-17 05:05:29.077',3

  insert into #Temp_Out(Seq,Name,Purchased_date) select 1,'B','2014-10-06 13:05:41.830'

  insert into #Temp_Out(Seq,Name,Purchased_date) select 2,'B','2014-10-06 16:05:41.830'

  insert into #Temp_Out(Seq,Name,Purchased_date,Newseq) select 3,'B','2014-10-07 05:05:41.830',1

  insert into #Temp_Out(Seq,Name,Purchased_date) select 4,'B','2014-10-12 13:06:16.263'

  insert into #Temp_Out(Seq,Name,Purchased_date,Newseq) select 5,'B','2014-10-12 23:06:16.263',2

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  October 28, 2014 at 11:07 pm

  #1755407

  Hope this will help,

  I just used self join for these kind of approaches.

  with cte as

  (

  selecta.seq as a_seq,a.Name a_name,a.Purchased_date as a_Purchased_date,

  b.seq as b_seq,b.Name b_name,b.Purchased_date as b_Purchased_date

  from#Temp_data a left join #Temp_data b

  ona.name = b.name

  anda.seq = b.seq -1

  )

  selecta_seq,a_name,a_Purchased_date ,datediff(hh,a_Purchased_date ,b_Purchased_date)

  fromcte

  whereisnull(datediff(hh,a_Purchased_date ,b_Purchased_date),49) > =48

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 29, 2014 at 3:25 am

  #1755450

  Looking at Name "A", the second row (by Seq) is within 48 hours of the first row. The third row is within 48 hours of the second row. The fourth row is more than 48 hours from the third row.

  According to your description "if anyone purchase within 48 hours it should be considered as single purchase", the first three rows should be classed as a single purchase.

  I think your sample output is incorrect and should look like this:

  [/SELECT *

  FROM (

  select 1,'A','2014-10-07 13:04:16.887',1 UNION ALL

  select 2,'A','2014-10-07 16:04:16.887',1 UNION ALL

  select 3,'A','2014-10-08 01:04:16.887',1 UNION ALL

  select 4,'A','2014-10-13 13:05:02.437',2 UNION ALL

  select 5,'A','2014-10-13 20:05:02.437',2 UNION ALL

  select 6,'A','2014-10-17 05:05:29.077',3 UNION ALL

  select 1,'B','2014-10-06 13:05:41.830',1 UNION ALL

  select 2,'B','2014-10-06 16:05:41.830',1 UNION ALL

  select 3,'B','2014-10-07 05:05:41.830',1 UNION ALL

  select 4,'B','2014-10-12 13:06:16.263',2 UNION ALL

  select 5,'B','2014-10-12 23:06:16.263',2

  ) d (Seq, Name, Purchased_date, Newseq) code]

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden