SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Please Read. I need help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sikharma13
Starting Member

Philippines
44 Posts

Posted - 10/29/2013 :  21:55:50  Show Profile  Reply with Quote
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

Edited by - sikharma13 on 10/29/2013 21:58:08

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/30/2013 :  01:58:35  Show Profile  Reply with Quote
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
Go to Top of Page

sikharma13
Starting Member

Philippines
44 Posts

Posted - 10/30/2013 :  02:26:03  Show Profile  Reply with Quote
seems to have error sir visakh...

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

VFP9.0 via MySQL 5.0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/30/2013 :  03:29:33  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000