November 27, 2014 at 7:38 am
Hi Folks,
I was trying different things but looks like I am stuck.
Maybe somebody will be able to point me in right direction. I pull orders for period of time. Results are filtered in the group setting so I only see if customers that have 2 or more orders.
I also want to see day difference between these orders for each customer(more then 2 orders) my expression for day difference is ;
DateDiff("d",previous(Fields!orderdate.value),Fields!orderdate.value)
but this also pulls date from previous customer for first order and looks like its pulling possible other hidden value
Day Difference
customer id 1
Orderdate/ 12/09/2014 /Day Difference 735487
Orderdate/ 18/11/2014 /Day Difference 67
customer id 2
Orderdate/ 01/09/2014 /Day Difference -42
Orderdate/ 09/10/2014 /Day Difference 38
should this calculation be in another sub group?
November 27, 2014 at 5:44 pm
What version of SQL Server are you using? If you're using 2012, you could use something like this as the source for your dataset:
SELECT
custID
,OrderID
,OrderDate
,LAG(OrderDate) OVER (PARTITION BY custID ORDER BY OrderDate) AS PrevDate
,DATEDIFF(d,LAG(OrderDate) OVER (PARTITION BY custID ORDER BY OrderDate),OrderDate) As DaysSincePrevious
FROM Sales.Orders
ORDER BY custID, OrderDate;
(I'm cheating and using the TSQL2012 database from MS SQL 2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan.
Definitely worth a read if you do this kind of thing a lot.
November 28, 2014 at 6:29 am
Thank you very much for quick reply and tip. I am running 2012 SSRS but data is in on 2008R2.
looks like handy function
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply