--- In firebird-support@yahoogroups.com, Thomas Clarke <thomas.p.clarke@...> wrote:
>
> 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]
>