Trying to do something in SQL, SSRS

I will try and explain,

i have some customers and i need to group to show how many new accounts we have in a month and how many lapsing accounts.

I can get the max and min invoice dates and can show for any given month how many customers are new or lapsing, but the problem i am having is seeing how i can group this month on month? so that i can then plot a graph showing the trend.

Any one able to offer a pointer?

Use a group by clause as follows ?

group by (datename ( year, invoice_date_column) + datename ( month, invoice_date_column))

where invoice_date_column is the column on your table containing the date you’re interested in ?

disclaimer: not a SQL server bod, never used it 🙂

Check out this guide where they use date use month

Also not a SQL Server user

not quite what i am getting at.

In month one i need to know the count of accounts whose min purchase date falls between 0 and 1 month ago, i also need the number of accounts whose max purchase date is between 4 and 5 months ago.

In month two i need to know the count of accounts whose min purchase date falls between 1 and 2 month ago, i also need the number of accounts whose max purchase date is between 5 and 6 months ago.

repeat.

Then plot these numbers on a graph.

Difficult to say from the information given, but I think you’ll be wanting to use datediff rather than max and min

Any one able to offer a pointer?

You should be able to achieve that with a Common Table Expression (CTE) Query.

Google search for “SQL CTE”. The first link returned is the MSDN page with a good example.

Would need some idea of your data structure to give detailed help but whatever you do that requirement is going to leave you with a right old mess of a query unless your data structures are tailored for it

yup just post up your ip address and sa password and we’ll do the rest

I guess your users table has some fields like joined_date and left_date.

So just count and groupby month on joined_date for new customers.

And same on left_date for lapsed?

If you don’t have fields analogous to those then what fields do you have to work it out?

Not sure about server SQL but I used Year, Switch and DatePart to create a Year, Month grouping to then follow with Count.

So:

SELECT Year(LRPDate) AS [Year], Switch(DatePart(‘m’,LRPDate)=1,”01 January”,DatePart(‘m’,LRPDate)=2,”02 February”… …, Count(*)

Group By Year etc etc

all i need is in the single sales table,

The plan is count of accounts grouped by first sale date. That is the easy bit.

the hard bit is i need to look at the data month by month and ignore everything that is more recent.

So if we start in February, how many new customers were there, a new customer is one who bought in february or january.

Then i need to show the situation in January, ie in January how many new customers were there, ie whose first purchase was in January or December.

Then repeat for December…..

I then need to do the same thing based on the last sale date.

What I wrote would seem to fit what you need. I guess you could have already run a separate query to group the preceding months together…