Determine the most recent datetime out of 3 columns

• logitestus

  SSCrazy

  Points: 2928

  More actions

  January 10, 2014 at 10:54 am

  #396090

  I have been wrecking my brain on this one. I have 3 tables that get joined together and each table contains a column for CreatedDate and ModifiedDate. What I am looking to do is return the most recent date for each column in the row. In the past, the SQL developers in my company have a liking to use user-defined scalar functions for this type of operation. Most of the time I am able to create a better performing query without having to use this type UDF. This time I am stumped. I have tried CTE's, PIVOT's, and a few other ideas but they all come back to RBAR.

  I suspect I will be performing a facepalm but if anyone has any suggestions, I'm completely open.

  Below contains tables, sample data, and the basic query with what I was looking for the result to be.

  CREATE TABLE [dbo].[Price]

  (

  [PriceID] [bigint] IDENTITY(1,1) NOT NULL,

  [PriceHostRef] [varchar](50) NULL,

  [ParentPriceID] [bigint] NULL CONSTRAINT [FK_Price_ParentPrice] FOREIGN KEY([ParentPriceID]) REFERENCES [dbo].[Price] ([PriceID]),

  [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Price_CreatedDate] DEFAULT (getutcdate()),

  [CreatedBy] [varchar](50) NULL,

  [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Price_ModifiedDate] DEFAULT (getutcdate()),

  [ModifiedBy] [varchar](50) NULL,

  CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED ([PriceID] ASC)

  )

  GO

  CREATE TABLE [dbo].[PriceDetail]

  (

  [PriceID] [bigint] NOT NULL CONSTRAINT [FK_PriceDetail_Price] FOREIGN KEY([PriceID]) REFERENCES [dbo].[Price] ([PriceID]),

  [SeqNbr] [tinyint] NOT NULL,

  [Qty] [int] NOT NULL CONSTRAINT [DF_PriceDetail_MaxQty] DEFAULT ((999999)),

  [Amt] [money] NOT NULL CONSTRAINT [DF_PriceDetail_Amt] DEFAULT ((0)),

  [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PriceDetail_CreatedDate] DEFAULT (getutcdate()),

  [CreatedBy] [varchar](50) NULL,

  [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PriceDetail_ModifiedDate] DEFAULT (getutcdate()),

  [ModifiedBy] [varchar](50) NULL,

  CONSTRAINT [PK_PriceDetail] PRIMARY KEY CLUSTERED ([PriceID] ASC,[SeqNbr] ASC)

  )

  GO

  CREATE TABLE [dbo].[PriceAssign]

  (

  [PriceAssignId] [bigint] IDENTITY(1,1) NOT NULL,

  [PriceID] [bigint] NOT NULL CONSTRAINT [FK_PriceAssign_Price] FOREIGN KEY([PriceID]) REFERENCES [dbo].[Price] ([PriceID]),

  [CustomerID] [bigint] NULL,

  [CustomerLocationID] [bigint] NULL,

  [ProductID] [bigint] NULL,

  [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PriceAssign_CreatedDate] DEFAULT (getutcdate()),

  [CreatedBy] [varchar](50) NULL,

  [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PriceAssign_ModifiedDate] DEFAULT (getutcdate()),

  [ModifiedBy] [varchar](50) NULL,

  CONSTRAINT [PK_PriceAssign] PRIMARY KEY CLUSTERED ([PriceAssignId] ASC)

  )

  GO

  SET IDENTITY_INSERT dbo.Price ON

  INSERT INTO dbo.Price(PriceId,PriceHostRef,ParentPriceId,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)

  VALUES

  (1,'1',NULL,'2011-11-15 22:16:52.410','insertScript','2012-07-07 22:32:52.410','updateScript'),

  (2,'10',NULL,'2011-11-15 22:16:52.510','insertScript','2012-07-07 22:32:52.510','updateScript'),

  (3,'100',NULL,'2011-11-15 22:16:52.530','insertScript','2012-07-07 22:32:52.530','updateScript'),

  (4,'101',NULL,'2011-11-15 22:16:52.560','insertScript','2012-07-07 22:32:52.560','updateScript'),

  (5,'102',NULL,'2011-11-15 22:16:52.583','insertScript','2012-07-07 22:32:52.583','updateScript'),

  (6,'103',NULL,'2011-11-15 22:16:52.603','insertScript','2012-07-07 22:32:52.603','updateScript'),

  (7,'104',NULL,'2011-11-15 22:16:52.623','insertScript','2012-07-07 22:32:52.623','updateScript'),

  (8,'105',NULL,'2011-11-15 22:16:52.643','insertScript','2012-07-07 22:32:52.643','updateScript'),

  (9,'106',NULL,'2011-11-15 22:16:52.660','insertScript','2012-07-07 22:32:52.660','updateScript'),

  (10,'107',NULL,'2011-11-15 22:16:52.683','insertScript','2012-07-07 22:32:52.683','updateScript'),

  (11,'108',NULL,'2011-11-15 22:16:52.700','insertScript','2012-07-19 22:28:52.700','updateScript'),

  (12,'109',NULL,'2011-11-15 22:16:52.723','insertScript','2012-07-19 22:28:52.723','updateScript'),

  (13,'11',NULL,'2011-11-15 22:16:52.740','insertScript','2012-07-19 22:28:52.740','updateScript'),

  (14,'110',NULL,'2011-11-15 22:16:52.763','insertScript','2012-07-19 22:28:52.763','updateScript'),

  (15,'111',NULL,'2011-11-15 22:16:52.780','insertScript','2012-07-19 22:28:52.780','updateScript'),

  (16,'112',NULL,'2011-11-15 22:16:52.800','insertScript','2012-07-19 22:28:52.800','updateScript'),

  (17,'113',NULL,'2011-11-15 22:16:52.820','insertScript','2012-07-19 22:28:52.820','updateScript'),

  (18,'114',NULL,'2011-11-15 22:16:52.840','insertScript','2012-07-19 22:28:52.840','updateScript'),

  (19,'115',NULL,'2011-11-15 22:16:52.860','insertScript','2012-07-19 22:28:52.860','updateScript'),

  (20,'116',NULL,'2011-11-15 22:16:52.880','insertScript','2012-07-19 22:28:52.880','updateScript')

  SET IDENTITY_INSERT dbo.Price OFF

  INSERT INTO dbo.PriceDetail(PriceId,SeqNbr,Qty,Amt,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)

  VALUES

  (1,1,99999,0.00,'2011-11-15 22:16:52.420','insertScript','2013-07-10 08:04:26.167','updateScript'),

  (2,1,99999,1.90,'2011-11-15 22:16:52.513','insertScript','2014-07-16 08:04:19.230','updateScript'),

  (3,1,99999,4.10,'2011-11-15 22:16:52.537','insertScript','2014-08-04 08:04:19.953','updateScript'),

  (4,1,99999,4.14,'2011-11-15 22:16:52.563','insertScript','2014-08-04 08:04:20.013','updateScript'),

  (5,1,99999,4.15,'2011-11-15 22:16:52.587','insertScript','2014-08-04 08:04:20.060','updateScript'),

  (6,1,99999,4.20,'2011-11-15 22:16:52.607','insertScript','2014-08-04 08:04:20.107','updateScript'),

  (7,1,99999,4.25,'2011-11-15 22:16:52.627','insertScript','2014-08-04 08:04:20.170','updateScript'),

  (8,1,99999,4.30,'2011-11-15 22:16:52.647','insertScript','2014-08-04 08:04:20.250','updateScript'),

  (9,1,99999,4.35,'2011-11-15 22:16:52.667','insertScript','2014-08-04 08:04:20.303','updateScript'),

  (10,1,99999,4.37,'2011-11-15 22:16:52.687','insertScript','2014-08-04 08:04:20.350','updateScript'),

  (11,1,99999,4.40,'2011-11-15 22:16:52.707','insertScript','2014-07-20 08:10:20.400','updateScript'),

  (12,1,99999,4.44,'2011-11-15 22:16:52.727','insertScript','2014-07-20 08:10:20.450','updateScript'),

  (13,1,99999,1.95,'2011-11-15 22:16:52.747','insertScript','2014-07-01 08:10:19.773','updateScript'),

  (14,1,99999,4.45,'2011-11-15 22:16:52.767','insertScript','2014-07-20 08:10:20.517','updateScript'),

  (15,1,99999,4.47,'2011-11-15 22:16:52.787','insertScript','2014-07-20 08:10:20.563','updateScript'),

  (16,1,99999,4.50,'2011-11-15 22:16:52.807','insertScript','2014-07-20 08:10:20.630','updateScript'),

  (17,1,99999,4.52,'2011-11-15 22:16:52.827','insertScript','2014-07-20 08:10:20.700','updateScript'),

  (18,1,99999,4.53,'2011-11-15 22:16:52.847','insertScript','2014-07-20 08:10:20.780','updateScript'),

  (19,1,99999,4.55,'2011-11-15 22:16:52.867','insertScript','2014-07-20 08:10:20.870','updateScript'),

  (20,1,99999,4.57,'2011-11-15 22:16:52.887','insertScript','2014-07-20 08:10:20.910','updateScript')

  SET IDENTITY_INSERT dbo.PriceAssign ON

  INSERT INTO dbo.PriceAssign(PriceAssignId,PriceId,CustomerId,CustomerLocationId,ProductId,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)

  VALUES

  (1,1,NULL,NULL,NULL,'2011-11-15 22:16:52.427','insertScript','2012-09-20 22:38:52.427','updateScript'),

  (2,2,NULL,NULL,NULL,'2011-11-15 22:16:52.520','insertScript','2012-09-20 22:38:52.520','updateScript'),

  (3,3,NULL,NULL,NULL,'2011-11-15 22:16:52.540','insertScript','2012-09-20 22:38:52.540','updateScript'),

  (4,4,NULL,NULL,NULL,'2011-11-15 22:16:52.567','insertScript','2012-09-20 22:38:52.567','updateScript'),

  (5,5,NULL,NULL,NULL,'2011-11-15 22:16:52.590','insertScript','2012-09-20 22:38:52.590','updateScript'),

  (6,6,NULL,NULL,NULL,'2011-11-15 22:16:52.610','insertScript','2012-09-20 22:38:52.610','updateScript'),

  (7,7,NULL,NULL,NULL,'2011-11-15 22:16:52.630','insertScript','2012-09-20 22:38:52.630','updateScript'),

  (8,8,NULL,NULL,NULL,'2011-11-15 22:16:52.650','insertScript','2012-09-20 22:38:52.650','updateScript'),

  (9,9,NULL,NULL,NULL,'2011-11-15 22:16:52.670','insertScript','2012-09-20 22:38:52.670','updateScript'),

  (10,10,NULL,NULL,NULL,'2011-11-15 22:16:52.693','insertScript','2012-09-20 22:38:52.693','updateScript'),

  (11,11,NULL,NULL,NULL,'2011-11-15 22:16:52.710','insertScript','2012-02-29 22:26:52.710','updateScript'),

  (12,12,NULL,NULL,NULL,'2011-11-15 22:16:52.733','insertScript','2012-02-29 22:26:52.733','updateScript'),

  (13,13,NULL,NULL,NULL,'2011-11-15 22:16:52.750','insertScript','2012-02-29 22:26:52.750','updateScript'),

  (14,14,NULL,NULL,NULL,'2011-11-15 22:16:52.770','insertScript','2012-02-29 22:26:52.770','updateScript'),

  (15,15,NULL,NULL,NULL,'2011-11-15 22:16:52.790','insertScript','2012-02-29 22:26:52.790','updateScript'),

  (16,16,NULL,NULL,NULL,'2011-11-15 22:16:52.810','insertScript','2012-02-29 22:26:52.810','updateScript'),

  (17,17,NULL,NULL,NULL,'2011-11-15 22:16:52.830','insertScript','2012-02-29 22:26:52.830','updateScript'),

  (18,18,NULL,NULL,NULL,'2011-11-15 22:16:52.850','insertScript','2012-02-29 22:26:52.850','updateScript'),

  (19,19,NULL,NULL,NULL,'2011-11-15 22:16:52.870','insertScript','2012-02-29 22:26:52.870','updateScript'),

  (20,20,NULL,NULL,NULL,'2011-11-15 22:16:52.890','insertScript','2012-02-29 22:26:52.890','updateScript')

  SET IDENTITY_INSERT dbo.PriceAssign OFF

  SELECT --DISTINCT

  P.PriceID

  , P.PriceHostRef

  --Commented out because it is not of interest for this issue

  /*

  , P.ParentPriceID

  , PA.CustomerID

  , PA.CustomerLocationID

  , PA.ProductID

  , PD.SeqNbr

  , PD.Qty

  , PD.Amt

  */

  ,P.ModifiedDate

  ,PD.ModifiedDate

  ,PA.ModifiedDate

  ,P.CreatedDate

  ,PD.CreatedDate

  ,PA.CreatedDate

  FROM dbo.Price P

  LEFT JOIN dbo.PriceDetail PD

  ON P.PriceID = PD.PriceID

  LEFT JOIN dbo.PriceAssign PA

  ON P.PriceID = PA.PriceID

  --What the result should look like

  PriceIDModifiedDateCreatedDate

  12013-07-10 08:04:26.1672011-11-15 22:16:52.427

  22014-07-16 08:04:19.2302011-11-15 22:16:52.520

  32014-08-04 08:04:19.9532011-11-15 22:16:52.540

  42014-08-04 08:04:20.0132011-11-15 22:16:52.567

  52014-08-04 08:04:20.0602011-11-15 22:16:52.590

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 10, 2014 at 11:16 am

  #1679266

  It seems that a simple CASE statement will do the trick. Because your Modified date and Created Date columns are non-nullable on all tables, the query becomes simpler.

  SELECT --DISTINCT

  P.PriceID

  , P.PriceHostRef

  --Commented out because it is not of interest for this issue

  /*

  , P.ParentPriceID

  , PA.CustomerID

  , PA.CustomerLocationID

  , PA.ProductID

  , PD.SeqNbr

  , PD.Qty

  , PD.Amt

  */

  ,CASE WHEN P.ModifiedDate >= PD.ModifiedDate AND P.ModifiedDate >= PA.ModifiedDate THEN P.ModifiedDate

  WHEN PD.ModifiedDate >= PA.ModifiedDate THEN PD.ModifiedDate

  ELSE PA.ModifiedDate END ModifiedDate

  ,CASE WHEN P.CreatedDate >= PD.CreatedDate AND P.CreatedDate >= PA.CreatedDate THEN P.CreatedDate

  WHEN PD.CreatedDate >= PA.CreatedDate THEN PD.CreatedDate

  ELSE PA.CreatedDate END CreatedDate

  --,P.ModifiedDate

  --,PD.ModifiedDate

  --,PA.ModifiedDate

  --,P.CreatedDate

  --,PD.CreatedDate

  --,PA.CreatedDate

  FROM dbo.Price P

  LEFT JOIN dbo.PriceDetail PD

  ON P.PriceID = PD.PriceID

  LEFT JOIN dbo.PriceAssign PA

  ON P.PriceID = PA.PriceID

  If I'm missing something, don't hesitate on mentioning it.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• logitestus

  SSCrazy

  Points: 2928

  More actions

  January 10, 2014 at 11:37 am

  #1679270

  Thank you Luis. <insert facepalm here/> That was exactly what was in UDF, I guess thats what I get when I assume.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 10, 2014 at 11:50 am

  #1679274

  or this, which I personally find more "readable", but that's probably just me...

  , (select max(moddate) from (values(P.ModifiedDate),(PD.ModifiedDate),(PA.ModifiedDate)) a(moddate)) AS ModifiedDate

  , (select max(credate) from (values(P.CreatedDate),(PD.CreatedDate),(PA.CreatedDate)) a(credate)) AS CreatedDate

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]