You must Sign In to post a response.
  • Category: SQL Server

    Query for Expire Items ?

    Hi All
    I'm developing a program for my grocery.

    I want to get expire items.

    I'm using the following tables

    tblSTOCK
    _____________________________________________

    SID Name CSB
    28 aaaaa 18


    tblPURCHASE
    _____________________________________________

    id SID EXPDATE PRICE QTY
    73 28 2017-07-02 45.00 10
    95 28 2017-07-14 45.00 10


    before my second purchase I sold 02 items.
    then i did my second purchase and now I have total 18 items.

    I used the following query

    SELECT S.SID, S.Name, S.CSB, P.EXPDATE
    FROM tblPURCHASE AS P INNER JOIN tblSTOCKAS S ON P.SID = S.SID
    WHERE P.EXPDATE between '2017-07-01' and '2017-07-10'

    and it returns

    SID Name CSB EXPDATE
    _________________________________________
    28 aaaaa 18 2017-07-10


    BUT CORRCET ANSWER IS CSB=8 NOT CSB=18


    Please help me to fix this problem
  • #769361
    Hi,
    Thanks for posting on DotNetSpider. Please use [ code ] and [ / code ] before and after code snippet. This will make your code formatted as above and more understandable.
    Coming to your question, As much I can understand, there is no relation between the available balance and expiry date. Either you should be saving the remaining quantity against each purchase. If that was the case, you can calculate the expiry date or remaining validity of the product using DateDiff function.

    SELECT
    LogID,
    DATEDIFF(hh,LogInOn,LogOutOn) AS LogInDuration
    FROM
    SessionLog;


    As you can see, in above query used, I just fetched the hours of users active, you can use this function as in below way and get this done.

    SELECT 
    LogID,
    DATEDIFF(dd,EXPDATE,getdate()) AS ExpiryDays
    FROM
    tblPURCHASE;

    Refer to below article for more on DateDiff function. https://www.pyarb.com/sql/get-difference-two-date-times-hours-sql-datediff.html

    -------------
    Glad to be,
    John Bhatt
    Editor - DNS Forums
    https://www.pyarb.com


  • Sign In to post your comments