Get the MAX value of the first price
-
Hi,
I have the following thing:
In my DWH I have a Table FACTTicket and a Table called DimRate, I can join it on customerID but get to much rates, so the solution:
You see that the 2478063548 has 3 value:
2005-10-01
2012-01-01
2018-01-01
The ticket is on 2018-09-25.
That means that the startdaterate will be:
2018-01-01
-
What's the question and expected result? Don't use the title to provide info, ask a question.
-
The question is: How can I get to the closest startdaterate with customerdateticket?
I just find out datediff but I dont think you can fixed with MIN/MAX
select datediff(DAY, CustomerDateTicketas,StartRatedate) as diffdate, StartRatedate, CustomerDateTicket, FactKey
from
[dbo].[Rates] a
inner join [dbo].[Fact_Ticket] b
on b.[CustomerCode] = a.CustomercodeThe result must be here the first one because its the closest to the startRateDate.
Other Example:
The result must be here the second one because its the closest to the startratedate.
I hope this is better, the fact is 15mil rows big.
What I need is only the values that comes closest to the startratedate with customerdateticket.
-
A CTE with ROW_NUMBER() would do it.
With testCTE as (
Select row_number() over (partition by factkey order by StartRateDate DESC) RN, *
from factview
where StartRateDate=<CustomerDateTicket)
Select * from testCTE wehre rn=1----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I like Matt's solution. Once you decide on ordering and know what item you want, row_number() gives you a value that you can use in the WHERE clause.
-
Depends on what you mean by "closest". If it doesn't matter if it's over or under, you could use
ABS(DATEDIFF(...))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply