Determine the most recent datetime out of 3 columns

Question

Post reply

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 PriceID ModifiedDate CreatedDate 1 2013-07-10 08:04:26.167 2011-11-15 22:16:52.427 2 2014-07-16 08:04:19.230 2011-11-15 22:16:52.520 3 2014-08-04 08:04:19.953 2011-11-15 22:16:52.540 4 2014-08-04 08:04:20.013 2011-11-15 22:16:52.567 5 2014-08-04 08:04:20.060 2011-11-15 22:16:52.590
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]

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

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 · Answer 2

logitestus

SSCrazy

Points: 2928

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: 47068 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

mister.magoo (1/10/2014)

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

That's clever. I wonder if performance is affected for better or worse. Have you done any tests? I might do some.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

logitestus (1/10/2014)

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

There's still much room for performance improvement. You said that your developers were using a SCALAR udf for this. Turn it into an iTVF (Inline Table Valued Function) and modify the code to use it in a FROM clause and it should run about 7 times faster and use a whole lot less resources particularly in the area of logical reads.

--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)
Intro to Tally Tables and Functions

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

Luis Cazares (1/10/2014)

mister.magoo (1/10/2014)

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

That's clever. I wonder if performance is affected for better or worse. Have you done any tests? I might do some.

I haven't done any performance tests - never used the code myself 😉

MM

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

logitestus SSCrazy Points: 2928 More actions · Answer 7

logitestus

SSCrazy

Points: 2928

January 11, 2014 at 3:59 pm

#1679396

Thank you for the suggestions. I am interested in testing out the different suggestions you make on my test data set (200 million rows).

I can always rely on SSC to help me get a different perspective on SQL problems.