On May 4, 8:29 am, mwlang88 <[email protected]> wrote:
> 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)
You can use the sql_expr extension and do
filter((Date.today - 60).sql_expr > last_printed).
> 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 `>'
The sql_expr code above will fix this.
> I also tried redefining last_printed as:
>
> last_printed = DB[:'billing history'].
> filter(:billing => :billing).
> max(:'date printed')
The filter is pointless, unless you are attempting to remove NULL
billing values in a non-obvious manner (all columns equal themselves,
except NULL columns). Even then, it will only give you a single date,
the maximum value of date printed in the billing history table.
> 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 1 is for true, and it's because the date comparison is done in
ruby, since you are calling Date#> with another Date (I'm surprised
you get 1 instead of 0, though).
> 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).
This should give you all billings that have a positive balance and
haven't been billed in the last 60 days:
DB[:"billing header___b"].
join(DB[:"billing history"].
select{[billing, max(:'date printed').as(mdp)]}.as(:bh),
:billing=>:billing).
filter{mdp < Date.today - 60}.
filter(:"balance due".identifier > 0)
Hopefully that should be enough to get you started. If you have
further questions, please let me know.
Jeremy
--
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.
