BRILLIANT!!

Thanks a lot 😉

this

RN = 11 - ROW_NUMBER() OVER(PARTITION BY HostIp ORDER BY CreateDate DESC)

creates a ranked list based on the ip

then, all you needed to do is pivot the table!!

GREAT!!

THANKS 😉

GREAT!!!! 😀

I knew it was a matter of pivoting...but couldn't figure out how to deal with the dates...

do you mind to give an explanation? I think i sort of understood...

Well..i know how recursive CTEs work 😀

i am interested in the select on the rCTE.

Anyway... now it works on the real problem as well 😉

and it's going to take me quite some time to adapt it to real tables and rules (as i mentioned, i proposed here a simplified version) but this is something...

Brilliant!!!

😉

works like a charm

I think this is not a problem that can be solved with a set based programming language. Gianluca, your solution doesn't work. Returns the correct values for 1.1.1.1 but fails...

Well... go on then 😉 i'm working on a solution as well 🙂

hey wait... i still have to test your last solution 😛

i think i'm going back to cursors.

do you have any idea why my first solution returns a null?

Test is the function

mind that:

there might be more than 2 occurrences

there might be more that 2 iteration

i.e.

1.1.1.1 ...

This function

BEGIN

--declare @tmpresult nvarchar(50)

declare @result nvarchar(50)

if...

Sorry guys... can't get the hang of it.

hallidayd i get an error, can you explain me how your snippet works? AFAIK coalesce need more than just one argument

I'm now giving...

I see!! 😉

Thanks, i'll do some test with the posted code! 🙂

Uhmm...not sure i made myself clear 🙂

Anyway: this is a simplified version of the working UDF

create function [dbo].[alias2dnsname]

(

@alias nvarchar(50)

)

returns nvarchar(50)

as

BEGIN

declare @tmpresult nvarchar(50)

declare @result nvarchar(50)

if exists(select * from dnszone as D...