SQL oh SQL...
Monday, March 23, 2009 by aiManYgCoMeL
select case b.region_code
when 'KDP' then 'Kedah Perlis'
when 'PRK' then 'Perak'
when 'PNG' then 'Penang'
when 'SBH' then 'Sabah'
when 'SWK' then 'Sarawak'
when 'CK' then 'CKSB Head Quarters'
when '001' then 'Oout Source'
when 'HQ' then 'Head Quarters'
end as region,
a.location_code, b.location_name,
year(a.po_date) as POYear,
datename(mm,a.po_date) as POMonth,
a.po_no, a.po_date, a.vendor_code, l.vendor_name, j.budget_code as Account_No, c.item_code,m.description, (c.unit_cost * c.ordered_qty) as po_value,d.wr_no,e.wr_date,
case e.type
when 'BD' then 'Unscheduled'
when 'PM' then 'Scheduled'
when 'IN' then 'Internal'
when 'OA' then 'Other Activity'
end as WR_Type,
case e.category
when 'BD' then 'BreakDown'
when 'CM' then 'Corrective'
when 'II' then 'Investigation'
when 'MN' then 'Maintenance'
when 'N' then 'BreakDown'
when 'PM' then 'Preventive'
when 'PR' then 'Predictive'
when 'RI' then 'Routine Inspection'
when 'RW' then 'Reimbursable Work'
when 'SC' then 'Schedule Corrective'
when 'SP' then 'Safety And Performance'
when 'TA' then 'Tecnical Advice'
when 'TC' then 'Testing And Commissioning'
when 'UR' then 'User Requests'
when 'UT' then 'User Training'
else 'breakdown'
end as WR_Category,
e.asset_no,k.description,g.type_code,
case g.wg_code
when 'ADMIN' then 'ADMIN'
when 'ADMN' then 'ADMN'
when 'BMED' then 'BMED'
when 'CIVIL' then 'CIVIL'
when 'CIVL' then 'CIVL'
when 'CSST' then 'CSST'
when 'DFWG1' then 'DFWG1'
when 'eFEMS' then 'eFEMS'
when 'ELEC' then 'ELEC'
when 'GRD' then 'GRD'
when 'GRND' then 'GRND'
when 'HSKP' then 'HSKP'
when 'INVT' then 'INVT'
when 'LAUD' then 'LAUD'
when 'MECH' then 'MECH'
when 'MGMT' then 'MGMT'
when 'PEST' then 'PEST'
when 'WAST' then 'WAST'
end as Workgroup,
case g.asset_status
when 'S0' then 'Disposed'
when 'S1' then 'Functioning'
when 'S2' then 'Not Functioning'
when 'S3' then 'Not In Use'
when 'S4' then 'Transferred'
when 'S5' then 'Disposed'
end as status,
case g.asset_category
when 'A' then 'Equipment'
when 'S' then 'System'
when 'B' then 'Building'
when 'L' then 'Land'
when 'N' then 'Location'
end as category,
case f.status
when 'C1' then 'Good'
when 'C2' then 'Request For Exemption'
when 'C3' then 'Exemption Approved'
when 'C4' then 'Request for BER Certification'
when 'C5' then 'BER Certification Approved'
when 'C6' then 'Request for Condemn'
when 'C7' then 'Condemn approved'
end as cond_status,
case g.under_warranty
when 'N' then 'No'
when 'Y' then 'Yes'
else 'No'
end as warranty_flg,
case h.variation_status
when 'V1' then 'Existing / No Claim'
when 'V2' then 'Replacement (with higher or lower specification)'
when 'V3' then 'Added Installed Facilities (New)'
when 'V4' then 'Decommisined Installed Facility'
when 'V5' then 'Transfered To Other Hospital'
when 'V6' then 'Transfered From Other Hospital'
when 'V7' then 'Added (Donated by Others)'
when 'V8' then 'Upgraded Installed Facility'
end
as variation_status,
g.pur_date,g.warranty_start_date,g.warranty_end_date,
(year(getdate())-year(pur_date)) as asset_age
from prom..pom_po_hdr a
left join common..csec_location_master b
on (a.company_code = b.company_code and a.location_code = b.location_code)
left join prom..pom_po_det c
on (a.company_code = c.company_code and a.location_code = c.location_code
and a.po_no = c.po_no)
left join prom..pom_request_det j
on (a.company_code = j.company_code and a.location_code = j.location_code
and a.pr_no = j.pr_no and c.item_code = j.item_code and c.serial_no = j.serial_no)
left join common..eng_wr_prpo_det d
on (a.company_code = d.company_code and a.location_code = d.location_code
and a.po_no = d.po_no and c.serial_no = d.row_no)
left join eng_alor_setar..eng_work_request_hdr e
on (a.company_code = e.company_code and a.location_code = e.location_code
and d.wr_no = e.wr_no)
left join eng_alor_setar..eng_asset_master g
on (a.company_code = g.company_code and a.location_code = g.location_code and e.asset_no = g.asset_no)
left join eng_alor_setar..eng_asset_cond_status f
on (g.company_code = f.company_code and g.location_code = f.location_code and g.asset_no = f.assetno)
left join eng_alor_setar..eng_asset_variation_det h
on (g.company_code = h.company_code and g.location_code = h.location_code
and g.asset_no = h.asset_no)
left join common..eng_asset_type_master k
on (g.company_code = k.company_code and g.type_code = k.type_code and g.service = k.service and g.asset_category = k.asset_category)
left join common..pom_vendor_master l
on (a.company_code = l.company_code and a.vendor_code = l.vendor_code)
left join common..ims_company_item_master m
on (a.company_code = l.company_code and c.item_code = m.item_code)
where a.company_code = 'FMSGPFM1'
and g.ownership = 'M'
and e.wr_no = 'MWR010/08/000160'
--and a.po_date BETWEEN '2008-01-01 00:00:00.000' AND '2008-12-31 00:00:00.000'
/*
select * from prom..pom_po_det
where parts_req_flag = 'Y'*/
ni la coding SQL yang aku br generate..
nak kene run utk 4 negeri lak tu
satu negeri x kurang 30k data...
so bayangkan betapa lama nya nak kuar semua data..
pastu kene export ke excel..
tp apa leh wat..keje tetap keje..
bersyukur la pada Yang Maha Esa...
when 'KDP' then 'Kedah Perlis'
when 'PRK' then 'Perak'
when 'PNG' then 'Penang'
when 'SBH' then 'Sabah'
when 'SWK' then 'Sarawak'
when 'CK' then 'CKSB Head Quarters'
when '001' then 'Oout Source'
when 'HQ' then 'Head Quarters'
end as region,
a.location_code, b.location_name,
year(a.po_date) as POYear,
datename(mm,a.po_date) as POMonth,
a.po_no, a.po_date, a.vendor_code, l.vendor_name, j.budget_code as Account_No, c.item_code,m.description, (c.unit_cost * c.ordered_qty) as po_value,d.wr_no,e.wr_date,
case e.type
when 'BD' then 'Unscheduled'
when 'PM' then 'Scheduled'
when 'IN' then 'Internal'
when 'OA' then 'Other Activity'
end as WR_Type,
case e.category
when 'BD' then 'BreakDown'
when 'CM' then 'Corrective'
when 'II' then 'Investigation'
when 'MN' then 'Maintenance'
when 'N' then 'BreakDown'
when 'PM' then 'Preventive'
when 'PR' then 'Predictive'
when 'RI' then 'Routine Inspection'
when 'RW' then 'Reimbursable Work'
when 'SC' then 'Schedule Corrective'
when 'SP' then 'Safety And Performance'
when 'TA' then 'Tecnical Advice'
when 'TC' then 'Testing And Commissioning'
when 'UR' then 'User Requests'
when 'UT' then 'User Training'
else 'breakdown'
end as WR_Category,
e.asset_no,k.description,g.type_code,
case g.wg_code
when 'ADMIN' then 'ADMIN'
when 'ADMN' then 'ADMN'
when 'BMED' then 'BMED'
when 'CIVIL' then 'CIVIL'
when 'CIVL' then 'CIVL'
when 'CSST' then 'CSST'
when 'DFWG1' then 'DFWG1'
when 'eFEMS' then 'eFEMS'
when 'ELEC' then 'ELEC'
when 'GRD' then 'GRD'
when 'GRND' then 'GRND'
when 'HSKP' then 'HSKP'
when 'INVT' then 'INVT'
when 'LAUD' then 'LAUD'
when 'MECH' then 'MECH'
when 'MGMT' then 'MGMT'
when 'PEST' then 'PEST'
when 'WAST' then 'WAST'
end as Workgroup,
case g.asset_status
when 'S0' then 'Disposed'
when 'S1' then 'Functioning'
when 'S2' then 'Not Functioning'
when 'S3' then 'Not In Use'
when 'S4' then 'Transferred'
when 'S5' then 'Disposed'
end as status,
case g.asset_category
when 'A' then 'Equipment'
when 'S' then 'System'
when 'B' then 'Building'
when 'L' then 'Land'
when 'N' then 'Location'
end as category,
case f.status
when 'C1' then 'Good'
when 'C2' then 'Request For Exemption'
when 'C3' then 'Exemption Approved'
when 'C4' then 'Request for BER Certification'
when 'C5' then 'BER Certification Approved'
when 'C6' then 'Request for Condemn'
when 'C7' then 'Condemn approved'
end as cond_status,
case g.under_warranty
when 'N' then 'No'
when 'Y' then 'Yes'
else 'No'
end as warranty_flg,
case h.variation_status
when 'V1' then 'Existing / No Claim'
when 'V2' then 'Replacement (with higher or lower specification)'
when 'V3' then 'Added Installed Facilities (New)'
when 'V4' then 'Decommisined Installed Facility'
when 'V5' then 'Transfered To Other Hospital'
when 'V6' then 'Transfered From Other Hospital'
when 'V7' then 'Added (Donated by Others)'
when 'V8' then 'Upgraded Installed Facility'
end
as variation_status,
g.pur_date,g.warranty_start_date,g.warranty_end_date,
(year(getdate())-year(pur_date)) as asset_age
from prom..pom_po_hdr a
left join common..csec_location_master b
on (a.company_code = b.company_code and a.location_code = b.location_code)
left join prom..pom_po_det c
on (a.company_code = c.company_code and a.location_code = c.location_code
and a.po_no = c.po_no)
left join prom..pom_request_det j
on (a.company_code = j.company_code and a.location_code = j.location_code
and a.pr_no = j.pr_no and c.item_code = j.item_code and c.serial_no = j.serial_no)
left join common..eng_wr_prpo_det d
on (a.company_code = d.company_code and a.location_code = d.location_code
and a.po_no = d.po_no and c.serial_no = d.row_no)
left join eng_alor_setar..eng_work_request_hdr e
on (a.company_code = e.company_code and a.location_code = e.location_code
and d.wr_no = e.wr_no)
left join eng_alor_setar..eng_asset_master g
on (a.company_code = g.company_code and a.location_code = g.location_code and e.asset_no = g.asset_no)
left join eng_alor_setar..eng_asset_cond_status f
on (g.company_code = f.company_code and g.location_code = f.location_code and g.asset_no = f.assetno)
left join eng_alor_setar..eng_asset_variation_det h
on (g.company_code = h.company_code and g.location_code = h.location_code
and g.asset_no = h.asset_no)
left join common..eng_asset_type_master k
on (g.company_code = k.company_code and g.type_code = k.type_code and g.service = k.service and g.asset_category = k.asset_category)
left join common..pom_vendor_master l
on (a.company_code = l.company_code and a.vendor_code = l.vendor_code)
left join common..ims_company_item_master m
on (a.company_code = l.company_code and c.item_code = m.item_code)
where a.company_code = 'FMSGPFM1'
and g.ownership = 'M'
and e.wr_no = 'MWR010/08/000160'
--and a.po_date BETWEEN '2008-01-01 00:00:00.000' AND '2008-12-31 00:00:00.000'
/*
select * from prom..pom_po_det
where parts_req_flag = 'Y'*/
ni la coding SQL yang aku br generate..
nak kene run utk 4 negeri lak tu
satu negeri x kurang 30k data...
so bayangkan betapa lama nya nak kuar semua data..
pastu kene export ke excel..
tp apa leh wat..keje tetap keje..
bersyukur la pada Yang Maha Esa...
