Determine the most recent datetime out of 3 columns

  • 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

  • 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
  • Thank you Luis. <insert facepalm here/> That was exactly what was in UDF, I guess thats what I get when I assume.

  • 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]

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

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

  • 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]

  • 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.

  • Viewing 8 posts - 1 through 7 (of 7 total)

    You must be logged in to reply to this topic. Login to reply