[freeside-users] Query of invoice line items for service broadband

[Jeff Finucane]

On Sat, Jun 09, 2012 at 12:26:17PM -0400, Gilbert Rebeiro <gilbert at dido.ca> wrote:
+----------
| I can see the string on a sample invoice but I don't know the proper
| query to get the historical line items.
| 
| as an example
| this is custnum 305
| invoice num 16559
| The line item shows
| 2675     Cable Hi-Speed  (2009/02/01 - 2009/03/01)    $19.12
|     - Videotron Cable: SN: 101740526 - VLJMBYYY - customer name
|     - customer address
| 
| So I need a query that shows all invoice numbers and invoice dates
| that were issued with VLJMBYYY in the description.
+----------

Try

SELECT DISTINCT cust_bill.invnum,cust_bill._date FROM cust_bill JOIN cust_bill_pkg USING(invnum) JOIN cust_bill_pkg_detail USING(billpkgnum) WHERE detail LIKE '%VLJMBYYY%';

-- 
jeff at cmh.net

"There is no worse tyranny than to force a man to pay for what he does
 not want merely because you think it would be good for him." 

 Professor Bernardo de le Paz
  [ R.A. Heinlein -- "The Moon is a Harsh Mistress" ]