aparker81

QoQ

Feb 9th, 2012
343
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <cfquery datasource="myDSN" name="getParts">
  2. select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, sum(w.allocqty) as allocated, sum(w.onhandqty) as onhand
  3. from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
  4. partwhse w with(nolock)
  5. where p.company = 'PC68300'
  6. and w.company = p.company
  7. and w.partnum = p.partnum
  8. and p.partnum = t.partnum
  9. group by p.partnum, cast(p.partdescription as varchar(100))
  10. </cfquery>
  11.  
  12. <cfquery datasource="myDSN" name="getjobs">
  13. select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, count(j.jobnum) as jobcount, sum(requiredqty) as qtyNeeded, sum(issuedqty) as qtysent
  14. from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
  15. jobmtl j with(nolock)
  16. where p.company = 'PC68300'
  17. and j.company = p.company
  18. and j.partnum = p.partnum
  19. and p.partnum = t.partnum
  20. group by p.partnum, cast(p.partdescription as varchar(100))
  21. </cfquery>
  22.  
  23. <cfquery datasource="myDSN" name="getOrders">
  24. select p.partnum, cast(p.partdescription as varchar(100)) as partdescription, count(d.ordernum) as ordercount, sum(d.orderqty) as ordered, sum(s.ourinventoryshipqty + ourjobshipqty) as shipqty
  25. from part p with(nolock), [otherServer].internal.dbo.report_tracker t with(nolock),
  26. orderdtl d with(nolock) left join shipdtl s with(nolock) on (
  27.     d.company = s.company and d.partnum = s.partnum and d.ordernum = s.ordernum )
  28. where p.company = 'PC68300'
  29. and d.company = p.company
  30. and d.partnum = p.partnum
  31. and p.partnum = t.partnum
  32. group by p.partnum, cast(p.partdescription as varchar(100))
  33. </cfquery>
  34.  
  35. <cfquery datasource="myDSN" name="getIncoming">
  36. select partnum, sum(quantity) as recqty
  37. from partdtl with (nolock)
  38. where PartDtl.Company = 'PC68300'
  39. and PartDtl.Type = 'Mtl'
  40. and PartDtl.Plant = 'MfgSys'
  41. and PartDtl.RequirementFlag = 0
  42. and jobnum not like 'MRP%'
  43. and PartDtl.partnum in (select partnum from [otherServer].internal.dbo.report_tracker t with(nolock))
  44. group by partnum
  45. </cfquery>
Advertisement
Add Comment
Please, Sign In to add comment