Count distinct customer based on last three month sales

Question

question

shabah99 asked Jul 6, '14

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-2008 query tsql

10 |1200

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

Answer 1 · 2014-07-06T10:00:27Z

Squirrel answered Jul 6, '14

; 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

10 |1200

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

nidheesh.r.pillai commented · Jul 07, 2014 at 07:53 AM

+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 ·

question