Y
YisMan
Hi everyone.
i'm racking my noodles on this one and am in need of an idea.
i ddeveloped an app which does sales, inventory etc. it also tracks customer
warranty information.
in the SaleProducts table there are among others the following fields
ItemCode=the id of the item sold
warranty=int of months of the warranty
isreturn= bool if the sale is a return sale (credit)
withitem=in case the item sold is the actual warranty certificate then this
field keeps the info of which item is being warrentied
to keep it short... in order to figure out when an items warranty expires, i
made a union query as such
SELECT "SP" & saleproductid as ActID, ItemCode, IsReturn, Warranty, DTStamp
FROM Sales INNER JOIN SaleProducts ON Sales.SaleID = SaleProducts.SaleCode
WHERE itemcode is not null and WithItem is null and closed=true
UNION select "SP" & saleproductid, withitem , isreturn, warranty, dtstamp
from sales inner join saleproducts on sales.saleid=saleproducts.salecode
where withitem is not null and closed=true;
this gives me a record for each waranty sale. item, warranty months, start
date and whether its a plus (isreturn=false) or minus (isreturn=true)
now from this system im trying to figure out how to know when the warranty
expires.
the considerations are:
a) an item may be bought on jan 1, 2000 with 12 mos warranty and returned
the next day. it can then be resold on jan 1 2008 with 12 mos warranty. so i
cant just sum up the warranty mos and add it to the first date of sale
b) a guy can buy the product on jan 1 2000 with 12 mos warranty and then on
feb 1 2000, buy another 12 mos. so i cant just substract elapsed months of
each line from the startdate as in this case the customer will lose twice on
each month from feb 1 and further.
please advise of a good idea. thanks.
i did not find anyone asking about this on the web. maybe there are similar
situations where the basic idea is the same?
if im not clear enough please let me know and ill be more then glad to
explain further.
with appreciation,
YisMan
i'm racking my noodles on this one and am in need of an idea.
i ddeveloped an app which does sales, inventory etc. it also tracks customer
warranty information.
in the SaleProducts table there are among others the following fields
ItemCode=the id of the item sold
warranty=int of months of the warranty
isreturn= bool if the sale is a return sale (credit)
withitem=in case the item sold is the actual warranty certificate then this
field keeps the info of which item is being warrentied
to keep it short... in order to figure out when an items warranty expires, i
made a union query as such
SELECT "SP" & saleproductid as ActID, ItemCode, IsReturn, Warranty, DTStamp
FROM Sales INNER JOIN SaleProducts ON Sales.SaleID = SaleProducts.SaleCode
WHERE itemcode is not null and WithItem is null and closed=true
UNION select "SP" & saleproductid, withitem , isreturn, warranty, dtstamp
from sales inner join saleproducts on sales.saleid=saleproducts.salecode
where withitem is not null and closed=true;
this gives me a record for each waranty sale. item, warranty months, start
date and whether its a plus (isreturn=false) or minus (isreturn=true)
now from this system im trying to figure out how to know when the warranty
expires.
the considerations are:
a) an item may be bought on jan 1, 2000 with 12 mos warranty and returned
the next day. it can then be resold on jan 1 2008 with 12 mos warranty. so i
cant just sum up the warranty mos and add it to the first date of sale
b) a guy can buy the product on jan 1 2000 with 12 mos warranty and then on
feb 1 2000, buy another 12 mos. so i cant just substract elapsed months of
each line from the startdate as in this case the customer will lose twice on
each month from feb 1 and further.
please advise of a good idea. thanks.
i did not find anyone asking about this on the web. maybe there are similar
situations where the basic idea is the same?
if im not clear enough please let me know and ill be more then glad to
explain further.
with appreciation,
YisMan