[Closed] SQL help wanted please

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 [url= http://improve.dk/archive/2006/12/13/sql-server-datetime-rounding-made-easy.aspx ]guide[/url] 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...