Hello, All
I'm looking for some suggestions on how to solve a problem elegantly
as I'm not entirely pleased with my current solution, esp. when the
number of records involved is several thousand records. Here's the
scenario:
I have a :'billing header' table that is basically a table of
invoices. I also have a :'billing history' table that has one line
item for every time a billing is printed and notes the time of
printing by :'billing number'.
The :'billing header' table has a column :'print flag' where 0 means
do not print and 1 means print and we have a nightly batch process
that will find all :'print flag' = 1 and print them out, setting the
print flag to 0 after doing so as well as adding a new entry to
the :'billing history' table.
What I want to do is find all billings that have not been paid
(:'billing header__balance due' > 0) that have not been printed in 60
days to be reflagged to print so the nightly batch process picks it up
and prints again.
I solved this by first creating a middle table called :'last bill
history' and perform a select/insert max(:'date printed'), :'billing
number' to select the billing history with most recent print date.
After doing this, I then do an update on the :'billing header' joined
to :'last bill history' and set the :'print flag' => 1.
I tried to do this:
last_printed = DB[:'billing history'].
filter(:billing => :billing).
select{max(:'date printed')}
stale_billings = DB[:'billing header'].
filter(:'balance due' > 0.0).
filter(Date.today - 60 > last_printed).
select(:billing)
DB[:'billing header'].
filter(:billing => stale_billings).
update(:'print flag' => 1)
But this croaks on the "stale_billings" with:
ArgumentError: comparison of Date with Sequel::ODBC::MSSQL::Dataset
failed
from (irb):33:in `>'
I also tried redefining last_printed as:
last_printed = DB[:'billing history'].
filter(:billing => :billing).
max(:'date printed')
but then stale_billing immediately reduces to:
#<Sequel::ODBC::MSSQL::Dataset: "SELECT * FROM [BILLING HEADER]
WHERE (([TOTAL] > 0) AND 1)">
(note the "AND 1" in the where clause)
The above is just my attempt at the problem...if there's a better way
to select the last :'billing header' based on date printed being more
than 60 days ago and :'balance due' being > 0.0 that minimizes churn
on the DBMS, then please do share as I'm trying to improve my SQL
authoring in general (not just Sequel authoring).
Regards,
Michael
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
