Skip to content

Instantly share code, notes, and snippets.

@tax

tax/Pat noder.sql

Created June 25, 2009 15:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tax/135922 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tax/135922 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Pat noder.sql
select
pt.name AS 'Part type',
count(*) AS 'Qty processed',
'Passed' = (
select count(*)
from tbl_rmaproducts_generic rp2, tbl_parts p2, tbl_part_type pt2, tbl_rma rma2
where swapgroup_id = 8
and p2.id = rp2.part_id
and pt2.id = p2.parttype_id
and rma2.id = rp2.rma_id
and YEAR(rma2.receivedate) = 2009
and shiptocustomer_id = 9
and rp2.received_wo is not null
and rp2.received_wo = rp2.shipped_wo
and pt2.name = pt.name
group by pt2.name
),
'NPF' = (
select count(*)
from tbl_rmaproducts_generic rp2, tbl_parts p2, tbl_part_type pt2, tbl_rma rma2
where swapgroup_id = 8
and p2.id = rp2.part_id
and pt2.id = p2.parttype_id
and rma2.id = rp2.rma_id
and YEAR(rma2.receivedate) = 2009
and shiptocustomer_id = 9
and rp2.received_wo is not null
and rp2.received_wo = rp2.shipped_wo
and rp2.received_wo in (
select workorder_id from tbl_repairlines where repairaction_id IN(38,41,43)
)
and pt2.name = pt.name
group by pt2.name
),
'Screen +' = (
select count(*)
from tbl_rmaproducts_generic rp2, tbl_parts p2, tbl_part_type pt2, tbl_rma rma2
where swapgroup_id = 8
and p2.id = rp2.part_id
and pt2.id = p2.parttype_id
and rma2.id = rp2.rma_id
and YEAR(rma2.receivedate) = 2009
and shiptocustomer_id = 9
and rp2.received_wo is not null
and rp2.received_wo = rp2.shipped_wo
and rp2.received_wo in (
select workorder_id from tbl_repairlines where repairaction_id = 20
)
and pt2.name = pt.name
group by pt2.name
)
from tbl_rmaproducts_generic rp, tbl_parts p, tbl_part_type pt, tbl_rma rma
where swapgroup_id = 8
and p.id = rp.part_id
and pt.id = p.parttype_id
and rma.id = rp.rma_id
and YEAR(rma.receivedate) = 2009
and shiptocustomer_id = 9
AND pt.name not like '%NEO%'
group by pt.name
order by 'Qty processed' desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment