Sql query for finding entries in bills

[R. Victor Klassen]

For some time, I’ve had the question “What did we charge for x” or “What did we pay for y”?   The way I have typically answered it was to go to the account where x or y would be found in the description field, and then find an invoice number or bill number from the register, and then go to the search invoice or search bill menu items, giving the bill or invoice number.  This works, but is somewhat tedious, especially if I should want to check more than one bill or invoice.   The trouble being, of course, that just looking at the split doesn’t tell be how many of x or y were being bought/sold in that transaction, so I can’t see the unit price without the extra step.

So I bit the bullet and figured out how to do an Sqlite3 query directly to get what I want to know for invoices.   It appears to work, although it is not brief or immediately obvious to those not versed in sql:

.separator :
.headers on
select ( date / 10000000000 ) as year, /* strip apart the date into year month day to make it more readable */
        ( date - ( date / 10000000000 ) * 10000000000 ) / 100000000  as month ,
        ( date - ( date / 100000000 ) * 100000000 ) / 1000000 as day,
        description,  ( i_price_num + 0.0 ) /  i_price_denom as price,  /* convert rational price to decimal, add 0.0 so as to get non-integer division */
        name, I.id
        from entries as E /* An entry is a line in an invoice or bill */
                join invoices as I on E.invoice=I.guid /* the invoice field in the entry is the invoice's guid */
                join customers as C on C.guid=I.owner_guid /* the owner guid in the invoice identifies the vendor */
        where date > 20140101000000 
        and date < 20170731000000
 	and description like '%eat%’ /* % is a 0 or more matching wild card */
        limit 40;
.exit

I can change the date range or the pattern in the description and all is good.  It may not be elegant, but it works.   Here’s my question, and I suspect there are only two or three folks out there who know the answer:  When I change the word “customers” to “vendors” I get garbage.   By which I mean that the name fields appear to have the names of vendors, as expected, but the description fields are from entries in invoices, not bills: they are things we only sell, never buy.   Ideas?