Today i got a adhoc request from the marketing team to get some details of few customers.
i.e The frequency of a customer filing their GST returns.
Just so that i can relate to things lot more easily i have used the Adventureworks database to explain the query.
Days before SQL 2012 this would have been a quite a bit of an effort. With the Lead function been available with SQL 2012 things are lot more easier.
"For XML" was used to loop tough the data set and grab the results to a single row while LEAD was used to get the next/previous record for the difference.
Select CustomerID , OrderFrequency
from Sales.Customer SH
cross apply
(
select STUFF((
(
SELECT ', ' + CAST(isnull(
DATEDIFF(dd,OrderDate ,
LEAD(SH1.OrderDate) OVER (
PARTITION BY SH1.CustomerID
ORDER BY SH1.OrderDate))
,0)
AS VARCHAR(50))
FROM Sales.SalesOrderHeader SH1 WHERE SH1.CustomerID = SH.CustomerID FOR XML PATH(''))
), 1, 1, '') AS OrderFrequency
) sod
from Sales.Customer SH
cross apply
(
select STUFF((
(
SELECT ', ' + CAST(isnull(
DATEDIFF(dd,OrderDate ,
LEAD(SH1.OrderDate) OVER (
PARTITION BY SH1.CustomerID
ORDER BY SH1.OrderDate))
,0)
AS VARCHAR(50))
FROM Sales.SalesOrderHeader SH1 WHERE SH1.CustomerID = SH.CustomerID FOR XML PATH(''))
), 1, 1, '') AS OrderFrequency
) sod
Comments
Post a Comment