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, 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!
>