Category: SQL Server

29 Aug 2017 16:14

Joined:	23/02/2008
Level:	Bronze
Points:	73

Points:	4

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

15 Sep 2017 12:02

Joined:	17/09/2012
Level:	Gold
Points:	3360

Points:	4

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

#769383

28 Sep 2017 18:47

Joined:	10/01/2008
Level:	Silver
Points:	102

Points:	2

Hi Dinesh,

You will have to change your table structure. Because in the stock table you have no track of which Purchase ID stock you have sold. Either you want to change the structure of stock table, or you want to add sales table to keep a track PID.

Thanks | Regards,
Srinivasan

#769397

11 Oct 2017 15:35

Joined:	21/07/2014
Level:	Silver
Points:	155

Points:	4

Hi dinesh,
Where is your sales table. please paste your sales table also. and at your end hand please add your all purchased quantity on the basis of date and then subtract your sales quantity on the basis of date.
Here is a sample-

SELECT  t.SID, t1.Name, t.CSB, P.EXPDATE from tblSTOCKAS t1 join
(SELECT  p.SID, (Sum(p.Qty)-s.sale)csb , P.EXPDATE FROM tblPURCHASE P 
INNER JOIN (Select SId,Sum(SaleQnty)sale from SalesTable WHERE EXPDATE>='2017-07-01' and P.EXPDATE<='2017-07-10' group by SId)S ON P.SID = S.SID
WHERE P.EXPDATE>='2017-07-01' and P.EXPDATE<='2017-07-10' group by p.SId,P.EXPDATE)t on t1.SID = t.SID

Query for Expire Items ?

About dotnetspider.com

Quick Links

General

Services