TSQL Help

• SQLEnthus

  SSC Veteran

  Points: 266

  More actions

  February 23, 2012 at 2:24 pm

  #257270

  I have table data like this (duration needs to be aggregated, and max id_date needs to be grabbed)

  Rule 1 : If checksum of first row is not equal to second row, keep that row, if second row is not equal to third, keep that row .

  ROW_NOidCheckSumdurationid_date

  18503602421504101/1/2011

  2850360244337731101/2/2011

  385036024269731101/5/2011

  485036024269731101/6/2011

  585036024269731101/9/2011

  68503602421504101/10/2011

  78503602421504101/11/2011

  88503602421566101/12/2011

  98503602421506101/13/2011

  108503602421506101/14/2011

  119739306421504111/15/2011

  129739306421504101/19/2011

  Result

  18503602421504101/1/2011

  2850360244337731101/2/2011

  385036024269731301/9/2011

  48503602421504201/11/2011

  58503602421566101/12/2011

  68503602421506201/14/2011

  19739306421504211/19/2011

  Any ideas how to achieve this?

  
  hello

• SQLEnthus

  SSC Veteran

  Points: 266

  More actions

  February 23, 2012 at 2:32 pm

  #1451107

  apologize for the formatting, attached a spreadsheet with good formatting

  
  hello

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 23, 2012 at 2:43 pm

  #1451109

  SQLEnthus (2/23/2012)

  ---

  apologize for the formatting, attached a spreadsheet with good formatting

  here you go; this is the best way to provide sample data in the future; anyone who follows the thread can simply paste this into SSMS, and then work on a solution.

  It took me a while to massage your example into this format, but once it's there, it's really easy to work with, and it takes the guesswork out of datatypes, which can make a difference in the solution.

  I ran out of steam converting this, so hopefully another volunteer will pickup where i left off.

  CREATE TABLE [dbo].[#TMP] (

  [ROW_NO] INT NULL,

  [ID] INT NULL,

  [CHECKSUM] INT NULL,

  [DURATION] INT NULL,

  [ID_DATE] DATETIME NULL)

  INSERT INTO [#TMP]

  SELECT 1,85036024,21504,10,'1/1/2011' UNION ALL

  SELECT 2,85036024,4337731,10,'1/2/2011' UNION ALL

  SELECT 3,85036024,269731,10,'1/5/2011' UNION ALL

  SELECT 4,85036024,269731,10,'1/6/2011' UNION ALL

  SELECT 5,85036024,269731,10,'1/9/2011' UNION ALL

  SELECT 6,85036024,21504,10,'1/10/2011' UNION ALL

  SELECT 7,85036024,21504,10,'1/11/2011' UNION ALL

  SELECT 8,85036024,21566,10,'1/12/2011' UNION ALL

  SELECT 9,85036024,21506,10,'1/13/2011' UNION ALL

  SELECT 10,85036024,21506,10,'1/14/2011' UNION ALL

  SELECT 11,97393064,21504,11,'1/15/2011' UNION ALL

  SELECT 12,97393064,21504,10,'1/19/2011'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQLEnthus

  SSC Veteran

  Points: 266

  More actions

  February 23, 2012 at 2:46 pm

  #1451112

  Thank you. i will do it going forward

  
  hello

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 23, 2012 at 2:47 pm

  #1451113

  I didn't try to convert the numeric value to a date value.

  But here's my approach:

  DECLARE @tbl TABLE

  (

  ROW_NOINT IDENTITY(1,1),

  idINT,

  CHECKS INT,

  duration INT,

  id_date INT

  )

  INSERT INTO @tbl

  VALUES

  (85036024,21504,10,40544),

  (85036024,4337731,10,40545),

  (85036024,269731,10,40548),

  (85036024,269731,10,40549),

  (85036024,269731,10,40552),

  (85036024,21504,10,40553),

  (85036024,21504,10,40554),

  (85036024,21566,10,40555),

  (85036024,21506,10,40556),

  (85036024,21506,10,40557),

  (97393064,21504,11,40558),

  (97393064,21504,10,40562);

  WITH cte AS

  (

  SELECT

  *,

  ROW_NUMBER() OVER(ORDER BY Row_No)-

  ROW_NUMBER() OVER(PARTITION BY id,checks ORDER BY Row_No) grp

  FROM @tbl

  )

  SELECT id,checkS, SUM(duration), MAX(id_date)

  FROM cte

  GROUP BY id,checkS,grp

  ORDER BY grp

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• SQLEnthus

  SSC Veteran

  Points: 266

  More actions

  February 23, 2012 at 6:50 pm

  #1451154

  Sweet, thank you

  
  hello