Stock View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stock View

Author

Topic

studyy
Starting Member

16 Posts

Posted - 2010-05-12 : 07:59:01

Hi

Is it possible to show last record value to 2nd row ?

currently i use table to store theses values but i want to make use of view so we get result from direct db without any fear of database corruption in inventory table.

Current Stock Table Is :( Which i want to simulate with "view")

Date MaterialName OpeningStock ClosingStock
15-05-2010 Soap 20 15
16-05-2010 Soap 15 10

Source Table /name : TranactionsPurchase

Id Date MaterialName Qty
202 15-05-2010 Saop 5

Source Table /name : TranactionsSales

Id Date MaterialName Qty
220 15-05-2010 Soap 10

sorry if i m not able to explain the situation correctly but i m only trying to ask how to manage stock balance with views ?

Thanks For Help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-12 : 08:09:25

quote:
Date MaterialName OpeningStock ClosingStock
15-05-2010 Soap 20 15
16-05-2010 Soap 15 10

Where & how is the date 16-05-2010 coming from ? It is not in the sample data that you provided.

KH
[spoiler]Time is always against us[/spoiler]

studyy
Starting Member

16 Posts

Posted - 2010-05-12 : 13:36:26

Hi

Sorry i missed that line for table "TranactionsSales"

Source Table /name : TranactionsSales

Id Date MaterialName Qty
220 15-05-2010 Soap 10
221 16-05-2010 Soap 5

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 13:45:58

do you mean?


SELECT s.Date,
s.MaterialName,
s.OpeningStock,
s.OpeningStock - COALESCE(sl.SalQty,0) + COALESCE(p.PurQty,0) AS ClosingStock
FROM Stock s
OUTER APPLY (SELECT SUM(Qty) AS PurQty
             FROM TranactionsPurchase
             WHERE Date = s.Date
             AND MaterialName = s.MaterialName	
            )p
OUTER APPLY (SELECT SUM(Qty) AS SalQty
             FROM TranactionsSales
             WHERE Date = s.Date
             AND MaterialName = s.MaterialName	
            )sl

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

studyy
Starting Member

16 Posts

Posted - 2010-05-14 : 09:15:53

Hi Visakh

The View I m Getting is this

Date,MaterialName,OpeningStock,ClosingStock

13/May/10 12:00:00 AM Soap NULL NULL
14/May/10 12:00:00 AM Soap NULL NULL

TranactionsSales Records

SalesID,Date,MaterialName,Qty
1 13/May/10 12:00:00 AM Soap 2
2 14/May/10 12:00:00 AM Soap 6

TranactionsPurchase

1 13/May/10 12:00:00 AM Soap 30
2 14/May/10 12:00:00 AM Soap 10

The Query is farmatted like this by sqlManagement :

SELECT s.Date, s.MaterialName, s.OpeningStock, s.OpeningStock - COALESCE (sl.SalQty, 0) + COALESCE (p.PurQty, 0) AS ClosingStock
FROM Stock s OUTER APPLY
(SELECT SUM(Qty) AS PurQty
FROM tranactionspurchase
WHERE Date = s.Date AND MaterialName = s.MaterialName) p OUTER APPLY
(SELECT SUM(Qty) AS SalQty
FROM tranactionssales
WHERE Date = s.Date AND MaterialName = s.MaterialName) sl

Is it possible to eliminate the stock table & get Balances using view only ?

I tried it to mysql too but it dont support outer apply :(

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 13:16:43

which view? if view has date and materialname you can use it instead of stock table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

studyy
Starting Member

16 Posts

Posted - 2010-05-15 : 01:12:30

Hi

Maintain Stock detail in table is not good & data may be inaccurate.

so what i want that to create a view which can simulate stock table from getting data from purchase & sales table & Calulate opening stock & closing stock on base of previous record.

studyy
Starting Member

16 Posts

Posted - 2010-05-15 : 01:22:05

Hi

I changed sql & added Salqty &Purqty to list.

SELECT s.Date, s.MaterialName, s.OpeningStock, sl.SalQty,p.PurQty, s.OpeningStock - COALESCE (sl.SalQty, 0) + COALESCE (p.PurQty, 0) AS ClosingStock
FROM Stock s OUTER APPLY
(SELECT SUM(Qty) AS PurQty
FROM tranactionspurchase
WHERE Date = s.Date AND MaterialName = s.MaterialName) p OUTER APPLY
(SELECT SUM(Qty) AS SalQty
FROM tranactionssales
WHERE Date = s.Date AND MaterialName = s.MaterialName) sl

Now Data which i get is this

Date, MaterialName,OpeningStock,SalQty,PurQty,ClosingStock
2010-05-13 00:00:00.000 Soap NULL 2 30 NULL
2010-05-14 00:00:00.000 Soap NULL 6 10 NULL

I think its near about to result that i want.

one thing need to be calculated is that opening stock & the closing stock.

I want that for 1st record (because it is first then our opening stock starts from "0" & closing will show "28"
then 2nd one show opening "28" & closing shows "32" & so on..

Hope i mentioned what i need correctly

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:56:52

[code]
; WIth CTE (Date,MaterialName,SalQty,PurQty)
AS
(
SELECT s.Date, s.MaterialName, sl.SalQty,p.PurQty
FROM Stock s OUTER APPLY
(SELECT SUM(Qty) AS PurQty
FROM tranactionspurchase
WHERE Date = s.Date AND MaterialName = s.MaterialName) p OUTER APPLY
(SELECT SUM(Qty) AS SalQty
FROM tranactionssales
WHERE Date = s.Date AND MaterialName = s.MaterialName) sl
)

SELECT c1.Date,c1.MaterialName,c1.SalQty,c1.PurQty,
ISNULL(c2.TotalPrev,0) AS OpeningStock,
ISNULL(c2.TotalPrev,0) + ISNULL(c1.PurQty,0)-ISNULL(c1.SalQty,0) AS ClosingStock
FROM CTE c1
OUTER APPLY(SELECT SUM(PurQty-SalQty) AS TotalPrev
FROM CTE
WHERE Date< c1.Date
AND MaterialName = c1.MaterialName
)c2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

studyy
Starting Member

16 Posts

Posted - 2010-05-15 : 12:41:29

Hi visakh

Thanks Its working now as required

Thanks Thanks Thanks Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:50:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources