[
http://mifosforge.jira.com/browse/MIFOS-3827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=60375#action_60375
]
Van Mittal-Henkle commented on MIFOS-3827:
------------------------------------------
Current work in progress:
Initially targeting PPI data at the following table:
CREATE TABLE `ppi_survey_results` (
`ppi_results_id` int(11) NOT NULL AUTO_INCREMENT,
`ppi_survey_name` varchar(45) DEFAULT NULL,
`date_survey_taken` varchar(45) DEFAULT NULL,
`client_id` int(11) DEFAULT NULL,
`Q1` varchar(200) DEFAULT NULL,
`Q2` varchar(200) DEFAULT NULL,
`Q3` varchar(200) DEFAULT NULL,
`Q4` varchar(200) DEFAULT NULL,
`Q5` varchar(200) DEFAULT NULL,
`Q6` varchar(200) DEFAULT NULL,
`Q7` varchar(200) DEFAULT NULL,
`Q8` varchar(200) DEFAULT NULL,
`Q9` varchar(200) DEFAULT NULL,
`Q10` varchar(200) DEFAULT NULL,
PRIMARY KEY (`ppi_results_id`)
) ENGINE=InnoDB
Note: eventually, the column with the date should be of type DATE.
Populating the table above with the following query:
insert into ppi_survey_results (ppi_survey_name, date_survey_taken, client_id,
Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10)
SELECT
"PPI_India_2010_01" as ppi_survey_name,
GROUP_CONCAT(if(qgr.sections_questions_id = 19, qgr.response, NULL)) AS
'date_survey_taken',
qgi.entity_id as 'client_id',
GROUP_CONCAT(if(qgr.sections_questions_id = 20, qgr.response, NULL)) AS 'Q1',
GROUP_CONCAT(if(qgr.sections_questions_id = 21, qgr.response, NULL)) AS 'Q2',
GROUP_CONCAT(if(qgr.sections_questions_id = 22, qgr.response, NULL)) AS 'Q3',
GROUP_CONCAT(if(qgr.sections_questions_id = 23, qgr.response, NULL)) AS 'Q4',
GROUP_CONCAT(if(qgr.sections_questions_id = 24, qgr.response, NULL)) AS 'Q5',
GROUP_CONCAT(if(qgr.sections_questions_id = 25, qgr.response, NULL)) AS 'Q6',
GROUP_CONCAT(if(qgr.sections_questions_id = 26, qgr.response, NULL)) AS 'Q7',
GROUP_CONCAT(if(qgr.sections_questions_id = 27, qgr.response, NULL)) AS 'Q8',
GROUP_CONCAT(if(qgr.sections_questions_id = 28, qgr.response, NULL)) AS 'Q9',
GROUP_CONCAT(if(qgr.sections_questions_id = 29, qgr.response, NULL)) AS 'Q10'
FROM question_group_response qgr, question_group_instance qgi
WHERE qgr.question_group_instance_id = qgi.id
GROUP BY question_group_instance_id;
> PPI spike: write SQL to calculate a PPI score for a completed India PPI survey
> ------------------------------------------------------------------------------
>
> Key: MIFOS-3827
> URL: http://mifosforge.jira.com/browse/MIFOS-3827
> Project: mifos
> Issue Type: Story
> Reporter: Van Mittal-Henkle
> Assignee: Van Mittal-Henkle
> Priority: Major
> Fix For: Release E - Iteration 8
>
>
> Get basic calculation working in SQL for calculating a PPI score from a
> completed India PPI survey.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://mifosforge.jira.com/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Mifos-issues mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-issues
