question

shabah99 avatar image
shabah99 asked

Count distinct customer based on last three month sales

I need to get monthly count of distinct customers based on last three month Sales. To show the result by adding current month customer count and adding last three month customer to the count as below: - In month of APRIL ,distinct customers count of (APRIL+MARCH+FEBRUARY) - In month of MAY,distinct customers count of (MAY+APRIL+MARCH) - In month of JUNE,distinct customers count of (JUNE+MAY+APRIL) - In month of JULY,distinct customers count of (JULY+JUNE+MAY) Here what I tried: SELECT MonNumber = MONTH(h.Invoicedate) , YearNumber = YEAR(h.Invoicedate) , PartyCount = ( SELECT COUNT(DISTINCT s.CustomerID) FROM salesdata s WHERE s.Invoicedate BETWEEN DATEADD(month, -6, h.Invoicedate) AND h.Invoicedate ) FROM salesdata h GROUP BY MONTH(h.Invoicedate) , YEAR(h.Invoicedate) ORDER BY YEAR(h.Invoicedate) , MONTH(h.Invoicedate) ---------------------------------- | Year | Month | COUNT | |-----------|----------|-------------| | 2014 | Jan | 6 | | 2014 | Feb | 6 | | 2014 | Mar | 6 | | 2014 | Apr | 4 | | 2014 | May | 6 | | 2014 | Jun | 6 | [View the table on SQL Fiddle][1] [1]: http://sqlfiddle.com/#!3/5eff1/2
sql-server-2008querytsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel avatar image
Squirrel answered
; with cte as ( select distinct InvoiceDte = dateadd(month, datediff(month, 0, InvoiceDate), 0) from salesdata ) select [Year] = Year(InvoiceDte), [Mth] = Month(InvoiceDte), CustCount from cte outer apply ( select CustCount = count(distinct CustomerID) from salesdata x where x.InvoiceDate >= dateadd(month, -3, InvoiceDte) and x.InvoiceDate < dateadd(month, 1, InvoiceDte) ) c order by [Year], [Mth]
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 to the answer. I am adding few notes here as sourced from the WWW for OUTER APPLY The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. In short, OUTER APPLY is semantically equivalent to LEFT OUTER JOIN. The need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.