Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Window function on different date ranges Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, February 9, 2014 5:49 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 You can use FIRST_VALUE for the first case or MIN for either case.`WITH PreAggregate AS( SELECT CustomerID, OrderDate=MIN(OrderDate) --FROM #SalesOrderHeader FROM [Sales].[SalesOrderHeader] WHERE OrderDate > '2012-01-15' GROUP BY CustomerID )SELECT * ,EarliestOrderDate1=FIRST_VALUE(a.OrderDate) OVER(PARTITION BY a.CustomerID ORDER BY a.OrderDate) ,EarliestOrderDate1=MIN(a.OrderDate) OVER(PARTITION BY a.CustomerID ORDER BY a.OrderDate) ,OrderDate1=MIN(CASE WHEN a.OrderDate > '2012-01-15' THEN a.OrderDate END) OVER(PARTITION BY a.CustomerID ORDER BY a.OrderDate) ,OrderDate2=b.OrderDate--FROM #SalesOrderHeader aFROM [Sales].[SalesOrderHeader]JOIN PreAggregate b ON a.CustomerID = b.CustomerIDORDER BY a.CustomerID, a.OrderDate;`However if you need to apply the order date > 2012-01-15 to all rows, I believe you'll need to use the pre-aggregate. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1539607
 Posted Tuesday, February 11, 2014 1:59 PM
 Grasshopper Group: General Forum Members Last Login: Monday, September 22, 2014 2:15 PM Points: 18, Visits: 282
 Thank you very much, it works like a charm! That helped me a lot and I learned something new - as always when I open a topic in this forum.
Post #1540443

 Permissions