MySQL query to calculate historic membership figures from log data
$15-25 USD / jam
Briefly: I need to calculate how many members our membership organisation has had over time. I would like a MySQL query that shows a running total of membership numbers, grouped by whatever time period I specify.
In detail:
I have two tables with the following fields:
Membership Table:
-- ID
-- Member ID
-- Membership Start Date
-- Membership End Date
-- Member Status - an integer representing 'Active member', 'Expired', 'Cancelled', 'Deceased'
Membership Log Table:
-- ID
-- Membership ID (links to membership table)
-- New Membership Start Date
-- New Membership End Date
-- New Membership Status
Every time the Membership Table is updated, the Membership Log Table is updated with the *new* details (including when the membership is initially created). The Membership Log Table contains a complete record of all changes made to a membership.
I would like to graph our membership numbers over time using this data, so need a query to find these figures. So we'd have an output something like:
Date
Members gained
Members lost
Membership total
01-01-2010 -- 12 -- 20 -- 10500
08-01-2010 -- 15 -- 10 -- 10505
15-01-2010 -- 28 -- 18 -- 10525
And if possible I'd like to be able to edit the query to group by whatever time period I want - days, weeks or whatever.
Obviously this would be easier in a real programming language. But we have some great visualisation software that runs direct from sql queries, so I'm hoping there might be a clever way to do this in SQL...
ID Proyek: #6152546
Tentang proyek
9 freelancer rata-rata menawar $19/jam untuk pekerjaan ini
I have read the detail i will write this query in fifteen minutes, ready to start right now. I am expert in both web design and development, my expertise are Responsive desing, bootsrap, PHP, MYSQL, CUSTOM CMS, JOO Lebih banyak
Hello Sir, Surely can provide you the query as needed in MySQL or MySQLi. Thanks and regards, Mustafa
i am a senior software engineer and responsible for creating a web and desktop applications using different languages and technologies if u r interested just send me a message thank you and good luck
Hi, I have gone through your requirements.I am a DBA at CMMI-5 Level company since August 2010.I am working on MySql,SQL Server,Oracle database, I can do your job confidently. I am very much Interested to do your work, Lebih banyak
I have strong skill in MySQL, SQL Server and Oracle. I can build a query and trigger to do your requirement. I can start this job right away. Please contact me.
I can easily write the sql for you that can be customized easily and you can mention the group by whatever time period you need. Lets start. Thanks, Ammara
hi... i'm an database administrator.. i'm using navicat premium... hope you can send me the dump database. and i'm ensure you i could do this job. thank you.
sir this is very simple. i am query expert. i already develop library management system in php, mysql. and running successfully last 18 months.
Hello, Owasqa is a leading independent provider of IT services, Owasqa came into existence in 2011 to expand its operations in the worldwide Software Engineering Industry. Yes this is not very complex as seem it Lebih banyak
