Please Read. I need help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please Read. I need help!

Author

Topic

sikharma13
Starting Member

44 Posts

Posted - 2013-10-29 : 21:55:50

I have a table for education non compliant.
here's the structure

DROP TABLE IF EXISTS `s_abcdeeh`.`ed_abcdeeh`;
CREATE TABLE `s_abcdeeh`.`ed_abcdeeh` (
`PROVINCE` varchar(20) default NULL,
`MUNICIPALITY` varchar(25) default NULL,
`BARANGAY` varchar(20) default NULL,
`HOUSEHOLD_ID` varchar(35) default NULL,
`ENTRY_ID` varchar(15) default NULL,
`MONTH1` varchar(10) default NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
under ed_abcdeeh, there is a month1, month2..

this is the sample data that is displayed under ed_abcdeeh

province|municipality|barangay|household_id|entry_id|month1|
sn.diego|san fernando|concepci|12345-678901|11122233|-july-|----records 1 & 2 are with the same household id,
sn.diego|san fernando|concepci|12345-678901|13214553|-july-|----but with different entry_id

sn.diego|san fernando|concepci|12321-673121|13214123|-july-|
sn.diego|san fernando|concepci|22321-973224|13124130|-july-|
sn.diego|san fernando|concepci|44421-123225|16541220|------|

this is the structure of py_abcdeeh
DROP TABLE IF EXISTS `s_abcdeeh`.`py_abcdeeh`;
CREATE TABLE `s_abcdeeh`.`py_abcdeeh` (
`PROVINCE` varchar(30) NOT NULL default '',
`MUNICIPALITY` varchar(30) NOT NULL default '',
`BARANGAY` varchar(30) NOT NULL default '',
`HOUSEHOLD_ID` varchar(30) NOT NULL default '',
`ENTRY_ID` varchar(12) NOT NULL default '',
`NO_ELI_FOR_EDUC_GRANT` varchar(1) NOT NULL default '',
`NO_NCOM_EDUC_M1` varchar(5) NOT NULL default '0',
`EDUC_GRANT_M1` varchar(5) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

this is the sample data that is displayed under my "current" py_abcdeeh
which is e_grant_m1 = no_eli_for_educ_grant * 300

legend: no_ncom_educ_m1 = number of non compliant for education month 1
------: e_grant_m1 = education grant for month 1

province|municipality|barangay|household_id|entry_id|no_eli_for_educ_grant|no_ncom_educ_m1|e_grant_m1|
sn.diego|san fernando|concepci|12345-678901|11122233|--------3------------|-------0-------|---900----|
sn.diego|san fernando|concepci|12321-673121|13214123|--------2------------|-------0-------|---600----|
sn.diego|san fernando|concepci|22321-973224|13124130|--------1------------|-------0-------|---300----|
sn.diego|san fernando|concepci|44421-123225|16541220|--------0------------|-------0-------|----0-----|

if you link the table ed_abcdeeh and py_abcdeeh.. here is my expected result

province|municipality|barangay|household_id|entry_id|no_eli_for_educ_grant|no_ncom_educ_m1|e_grant_m1|
sn.diego|san fernando|concepci|12345-678901|11122233|--------3------------|-------2-------|---300----|
sn.diego|san fernando|concepci|12321-673121|13214123|--------2------------|-------1-------|---300----|
sn.diego|san fernando|concepci|22321-973224|13124130|--------1------------|-------1-------|----0-----|
sn.diego|san fernando|concepci|44421-123225|16541220|--------0------------|-------0-------|----0-----|

here is my current query... how to insert it here..

insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,no_eli_for_educ_grant)
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m2.count
from m_abcdeeh m1
inner join (select household_id,sum(case when eligible_for_educ_grant = 'yes' then 1 else 0 end) as count
FROM m_abcdeeh
GROUP BY household_id
)m2
on m2.household_id = m1.household_id
where m1.grantee = 'yes'

please help... thanks in advance..

VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 01:58:35

this?


insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,no_eli_for_educ_grant,no_ncom_educ_m1,e_grant_m1) 
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m2.count,COALESCE(e.mnthcnt,0),(m2.count - COALESCE(e.mnthcnt,0)) * 300
from m_abcdeeh m1
inner join (select household_id,sum(case when eligible_for_educ_grant = 'yes' then 1 else 0 end) as count 
FROM m_abcdeeh 
GROUP BY household_id
)m2
on m2.household_id = m1.household_id
LEFT JOIN (SELECT province,municipality,barangay,household_id,COUNT(month1) AS mnthcnt
           FROM s_abcdeeh.ed_abcdeeh 
           GROUP BY province,municipality,barangay,household_id
          )e
ON e.province = m1.province,,m1.barangay,m1.household_id
AND e.municipality = m1.municipality
AND e.barangay = m1.barangay 
AND e.household_id = m1.household_id 
where m1.grantee = 'yes'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sikharma13
Starting Member

44 Posts

Posted - 2013-10-30 : 02:26:03

seems to have error sir visakh...

have u tried it urself in ms sql? just askin... thank you! :)

VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 03:29:33

quote:
Originally posted by sikharma13

seems to have error sir visakh...

have u tried it urself in ms sql? just askin... thank you! :)

VFP9.0 via MySQL 5.0

atleast post the error message rather than telling its causing error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources