Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
3
Erster Beitrag:
vor 2 Jahren
Letzter Beitrag:
vor 1 Jahr, 12 Monaten
Beteiligte Autoren:
Al, Piet van Zanten

[WD20] Best way to find out if a date is between 2 other dates

Startbeitrag von Al am 23.04.2016 01:56

Hello All

The situation is to find a sell price for a stock item.
There is a price contract record attached to a debtor and then stock items are attached to the price contract with prices valid for that contract. The contract has a start and finish date which can be a few months or a year or so.
A debtor invoice has a transaction date so when a line on the invoice requests a sell value for a stock item I need to check if there is a current contract for the debtor where the invoice date is between the start and end dates of the contract.

At the moment I am using Hfilter()

sBrowseKeyItem is string
sBrowseKeyItem = HFilter(DSrvCont, "DRCLEUNIK='"+Debtor.DRCLEUNIK+"'"...
+" AND StartDate="+InvoiceDate+""...
+" AND ActiveStatus=1")
IF HReadFirst(DSrvCont, sBrowseKeyItem)
//There is a valid contract
//Go and see if there is a stock item match
ELSE
//There is no contract so get the value another way
END //IF HReadFirst(DSrvCont, sBrowseKeyItem)


I have also used a query and then tested to see if a record was returned.

Both methods work but seem clumsy so I am asking if there is a better way ?

Regards
Al

Antworten:

Hi Al,

In the case that contract periods don't overlap (there can only be one contract in a certain period of time), you can create a query that holds all articles linked to contracts between two dates and sort the result on article number or whatever key it has. Such a query can easily be generated by the query editor.
All you have to do then is run the query and do a hReadSeekFirst for the article number in the query.
If the article was found you can find the price in the query, otherwise you need to use the regular price.

If there can be more contracts in one period you need to run a query on the contracts only, sorted on date and do a hreadlast.
If one is found, then you do a query on the contract details and find the article.with hreadseekfirst.

Regards,
Piet

von Piet van Zanten - am 23.04.2016 09:22
Hello Piet

Thanks for the idea it got me thinking of other ways. The price contracts are rarely updated so I will look into a query and also have a look into the materialized views as they are more "permanent"

Regards
Al

von Al - am 23.04.2016 22:54
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.