On 11/6/2010 6:09 PM, Udai Gupta wrote:
Hi,I am wondering if someone on the list have a query using which we can get number of active client on a particular day. I want to know count of client whose status was ACTIVE on particular day using a query in Mifos database. Now, the catch is that some clients could have been active in the past and today their status could be inactive/close etc. So, the query should be able to use the status change from change_logs table. Thanks, Udai ------------------------------------------------------------------------------ The Next 800 Companies to Lead America's Growth: New Video Whitepaper David G. Thomson, author of the best-selling book "Blueprint to a Billion" shares his insights and actions to help propel your business during the next growth cycle. Listen Now! http://p.sf.net/sfu/SAP-dev2dev
Probably something like this |select enrolledClients.clients-dropouts.clients as activeClients from ( SELECT CUSTOMER.CUSTOMER_LEVEL_ID AS levelId,count(*) as clients FROM CUSTOMER WHERE CUSTOMER_LEVEL_ID=1 and created_date < '2010-05-11' )enrolledClients left join (SELECT CUST.CUSTOMER_LEVEL_ID AS levelId,count(*) as clients FROM CUSTOMER CUST,CUSTOMER_FLAG_DETAIL CFD,CUSTOMER_STATE_FLAG CSF WHERE CUST.CUSTOMER_ID=CFD.CUSTOMER_ID AND CFD.FLAG_ID=CSF.FLAG_ID AND CUST.CUSTOMER_LEVEL_ID=1 AND CSF.FLAG_ID IN (6,7,8,9,10) AND CFD.CREATED_DATE < '2010-05-11') dropouts on enrolledClients.levelId=dropouts.levelId Regards. Vishwas | -- Thanks& Regards, Vishwas 9986077286
------------------------------------------------------------------------------ The Next 800 Companies to Lead America's Growth: New Video Whitepaper David G. Thomson, author of the best-selling book "Blueprint to a Billion" shares his insights and actions to help propel your business during the next growth cycle. Listen Now! http://p.sf.net/sfu/SAP-dev2dev
_______________________________________________ Mifos-users mailing list Mifos-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/mifos-users
