|
Posted by Sad on January 29, 2009, 1:56 am
Please log in for more thread options thank you all, that was helpfull.
"convoluted" wrote:
> Hi Sad - my first suggestion would be to (if its in the budget) work with a
> RMS-certified partner that can help you build and maintain SQL queries....as
> SQL is nice to use but it can be tricky to manage. Joie makes a good point
> regarding triggers, as they can affect the performance of your database.
> Here's one possible option you can look into (without recurring to a trigger)
>
> The problem with the itemdynamic table is that it tracks several "last sold"
> dates, one for each store, so you have to "pick" the most recent "last sold"
> date.
>
> I would first create a view that summarizes for you the "last sold" date for
> all stores, then join that view to your item table on an update query that
> you would run as a batch file via the windows scheduler program.
>
> Here is the syntax for the view:
>
> create view item_last_sold
> as
> select id.itemid, i.itemlookupcode, MAX(id.lastsold) as last_sold
> from itemdynamic id
> inner join item i on id.itemid = i.id
> group by id.itemid, i.itemlookupcode
>
> Test the successful creation of the view by running
> select * from item_last_sold
>
> Here is the update query you can save on a notepad, save it as a .bat file,
> then schedule the .bat file to run after your stores poll with HQ, using the
> windows scheduler:
>
> update item
> set item.lastsold = item_last_sold.last_sold
> from item
> inner join item_last_sold on item.id = item_last_sold.itemid
>
> If you can, test these on a testing database first, and remember to backup
> your database prior to running the queries.
>
> But, really, this is something that Mickeysoft MUST fix on the next version
> of RMS...hope this helps.
>
> "Sad" wrote:
>
> > thank you joie
> > i am a biggener in sql, can you help me with that please.
> >
> > "Joie" wrote:
> >
> > > I have no idea about HQ
> > >
> > > However, in my opinion creating trigger can slow down database executions.
> > > Be careful with that
> > >
> > > May be it would be better if you create a stored procedure to contain the
> > > update logic and then schedule the stored procedure to run hourly or daily
> > > ?
> > >
> > > rgds,
> > > Joie
> > >
> > >
> > > > hi ,
> > > > i need to update automaticaly the last sold date in HQ from last sold
date
> > > > in store (since it is not updated from stores).
> > > > can we use item dynamic table to update item table ? if so can any one
> > > > give
> > > > a trigger to do that.
> > > > thanks
> > >
> > >
> > >
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap