On Wed, Jan 26, 2011 at 12:06 PM, majstor <majstoru@...> wrote:

>
>
> Hi,
>
> Maybe it is not clearly explained. If I have two pricelist which is valid
> for a different period and I have one article which is used in both list I
> need to get proce of this article form corect pricelist.
>
> for example:
>
> pricelist 1 is valid from 01.01. to 15.01.
> pricelist 2 is valid from 16.01. to 31.01.
>
> select statement must know wich pricelist to use acording of date given by
> user.
>
> Thanks again...
>
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> Thomas Steinmaurer <ts@...> wrote:
> >
> > > I keep some pricelists into tables which is contain date from wich is
> valid to date to which is valid.
> > > I need to write select statement which will contain all data from
> articles table and price from pricelist table where will be choosen prices
> between valid dates.
> > > For example, I have 2 pricelist one which is valid between 01.01.2011
> and 15.01.2011 and another pricelist which is valid between 16.01.2011 and
> 31.01.2011. When I call aritcles and prices with actual date I need
> pricelist with articles from valid pricelist.
> > >
> > > articles
> > > id, name, descript, qty, comment
> > > pricelist
> > > id, name, velid_begin, valid_end
> > > pricelist items
> > > id, pricelist_id, articles_id, price
> > >
> > > Thanks in advanced...
> >
> > select
> > a.*
> > , pli.price
> > from
> > pricelist pl
> > join pricelistitems pli on (pl.id = pli.pricelist_id)
> > join articles a on (a.id = pli.articles_id)
> > where
> > current_date between pl.valid_begin and pl.valid_end
> > ...
> >
> >
> > If I got your question right. ;-)
> >
> > Replace current_date with a date if it isn't the current system date.
> >
> >
> > --
> > With regards,
> >
> > Thomas Steinmaurer
> > Upscene Productions
> > http://www.upscene.com
> > http://blog.upscene.com/thomas/
> >
> > Download LogManager Series, FB TraceManager today!
> > Continuous Database Monitoring Solutions supporting
> > Firebird, InterBase, Advantage Database, MS SQL Server
> > and NexusDB
>

How about this version:

select a.id, a.descript, i.price
from price_list_items i
inner join articles a on a.id = i.articles_id
inner join pricelist p on p.id = i.pricelist_id
where
current_date between p.valid_begin and p.valid_end

Thomas Clarke


[Non-text portions of this message have been removed]