April 26, 2016 at 4:18 pm
I'm trying to figure out the correct way to use a case statement in an order by to pick the most recent date when there are two date columns in each row. I think my solution is pretty close, but just not sure if it will work in all cases (so to speak).
Example below.
Thanks!
declare @widgits table (
Id int primary key not null,
Name varchar(25) not null,
OnHand int not null,
AddDt datetime not null,
UpdateDt datetime null
)
--* Insert all the new widgits
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(1, 'Widgit 1', 50, '20150101 09:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(2, 'Widgit 2', 50, '20150101 09:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(3, 'Widgit 3', 50, '20150101 09:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(4, 'Widgit 4', 50, '20150101 09:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(5, 'Widgit 5', 50, '20150101 09:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(6, 'Widgit 6', 50, '20150102 14:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(7, 'Widgit 7', 50, '20150102 14:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(8, 'Widgit 8', 50, '20150102 13:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(9, 'Widgit 9', 50, '20150102 13:00:00.000', null)
insert into @widgits(Id, Name, OnHand, AddDt, UpdateDt)
values(10, 'Widgit 10', 50, '20150102 12:00:00.000', null)
--* Now update a few
update @widgitsset OnHand = 20, UpdateDt = GETDATE() where Id = 3
update @widgitsset OnHand = 44, UpdateDt = getdate() where Id = 5
update @widgitsset OnHand = 3, UpdateDt = GETDATE() where Id = 9
--* Now I need a query that lists the widits and order by most recently changed.
--* By changed I mean either "Add" or "Update". This is the best I could come up with.
--* Is this the best way? It seems to work in this simple case.
select * from @widgits order by
case
when AddDt > UpdateDt then AddDt
else
IsNull(UpdateDt, AddDt)
end desc
.
April 26, 2016 at 5:25 pm
Logically - can UpdateDt be possibly less than AddDt?
_____________
Code for TallyGenerator
April 26, 2016 at 9:08 pm
Only if there's a bug in the code.
.
April 26, 2016 at 10:10 pm
Misread the problem.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 27, 2016 at 5:34 am
BSavoie (4/26/2016)
Only if there's a bug in the code.
Considering, ORDER BY clause is not the place where we fix bugs (if you cannot rely on quality of the code, a check constraint can be your defence line), it should be simply:
order by ISNULL (UpdateDt, AddDt) desc
But I personally wou;d not allow NULLS into UpdateDt and made it = AddDt on a first insert.
It would be better for query performance too (less fragmentation).
_____________
Code for TallyGenerator
April 27, 2016 at 1:37 pm
Thanks for the response SSCertifiable. The order by also needs to choose the greater of AddDt & UpdateDt. I'll definitely try and get the nullability squared away.
.
April 27, 2016 at 2:19 pm
BSavoie (4/27/2016)
Thanks for the response SSCertifiable. The order by also needs to choose the greater of AddDt & UpdateDt. I'll definitely try and get the nullability squared away.
As you said, UpdateDt is always greater than AddDt, unless there is a mistake in code.
To prevent mistakes establish a check constraint on the table.
Then you'll never need to check which of the dates is greater ever again.
_____________
Code for TallyGenerator
April 27, 2016 at 9:20 pm
Oh right! Duh!
Thanks
.
April 28, 2016 at 4:16 pm
Shoot, I wanted to say it was because you spelled Widget wrong 😛
April 29, 2016 at 7:20 am
I would turn it into a column and order by it. Cleaner this way.
SELECT
(CASE WHEN AddDt > UpdateDt
THEN AddDt
ELSE ISNULL(UpdateDt, AddDt)
END) AS MaxDt,
Id, Name, OnHand, AddDt, UpdateDt
FROM @widgits
ORDER BY 1
April 29, 2016 at 9:00 am
cmick 77911 (4/29/2016)
I would turn it into a column and order by it. Cleaner this way.
SELECT
(CASE WHEN AddDt > UpdateDt
THEN AddDt
ELSE ISNULL(UpdateDt, AddDt)
END) AS MaxDt,
Id, Name, OnHand, AddDt, UpdateDt
FROM @widgits
ORDER BY 1
Using ordinal position is a bad practice. Anytime, anyone can change the column order and the results would change without notification. To prevent this, use column alias.
SELECT
(CASE WHEN AddDt > UpdateDt
THEN AddDt
ELSE ISNULL(UpdateDt, AddDt)
END) AS MaxDt,
Id, Name, OnHand, AddDt, UpdateDt
FROM @widgits
ORDER BY MaxDt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply