Number of active clients in a payments table

[linker3000]

Hi Everyone,

 

I am using the following code to count the number of unique clients by ID that made payments during a specified period of time. What I really would like is a report that automatically does this for monthly periods eg: Jan 2006 - Jan 2007, Feb 2006 - Feb 2007 etc. and lists out a table of results for an entire year. Being relatively new to SQL programming I expect I would sort this out eventually but any shortcuts would be appreciated. This code is running on MySQL 4.1.20.

 

Also, any tips on better code or optimisation would be appreciated. The original data comes from elsewhere and is imported into MySQL as a CSV file - one issue I have had to deal with for this code is that some dates have a two-digit year value and some have 4 digits! Thanks for any advice offered.

 

SELECT  count( distinct a.clientid ) as numrows

FROM (

 

SELECT clientid,

CASE

WHEN length( date ) =8

THEN to_days( STR_TO_DATE( date, '%d-%c-%y' ) )

WHEN length( date ) =10

THEN to_days( STR_TO_DATE( date, '%d-%c-%Y' ) )

ELSE to_days( '1970-01-01' )

END AS maindate

FROM accbook

)a

/*

***** Set the date range below in the format YYYY-MM-DD *****

*/

 

WHERE a.maindate >= to_days( '2006-10-01' ) and a.maindate <= to_days('2007-10-01')

group by null

 

[fenway]

You can extract MONTH_YEAR from any date column.