sql query(with views) - 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 2000 Forums

SQL Server Administration (2000)

sql query(with views)

Author

Topic

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-09 : 07:34:22

Hi I had made views 1 for opening stock and other for closing stock
from the table inventory.I want to create a new view combining both the views such that both opening and closing stock comes together along with the name of the items there.

Todays stock will be counted as closingstock
and day before today will be counted as openingstock

openingstock(View1)
ItemCode Description ItemType BrandName Size openingqty
I001 Monitor CTF LG 12 inch 6
I002 CPU Intel 16

Closingstock(View2)
ItemCode Description ItemType BrandName Size Closingqty
I001 Monitor CTF LG 12 inch 16
I002 CPU Intel 100
I003 Keyboard Normal Frontech 8

The new Views i want to create
openingandclosingstock
ItemCode Description ItemType BrandName Size Openingqty Closingqty
I001 Monitor CTF LG 12 inch 6 16
I002 CPU Intel 18 100
I003 Keyboard Normal Frontech 8

please help How shall i create the new view(openingandclosingstock)

Keshab

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-09 : 08:42:54

try this...
if u need add extra columns in join condition....

SELECT v2.*, v1.Closingqty
FROM View2 v2
LEFT JOIN View1 v1 ON v1.ItemCode = v2.ItemCode
AND v1.ItemType = v2.ItemType
AND v1.BrandName = v2.BrandName

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-11 : 01:20:31

That query is okay but all new items added in the View2 which is not in View1 is not displayed.opening and closing qty of Items along with there details already added in View1 is only displayed.How can i retify this problem.

Keshab

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 01:36:36

quote:
but all new items added in the View2 which is not in View1 is not displayed

The query Peter posted will be able to handle that.

Post the sample record of the problem scenario that you are facing

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

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-11 : 02:29:47

Here i explain my problem in details again

First i Had made a table named inventory with following fields
(ID VoucherID POID noteno SID VoucherDate GroupID ItemCode ItemType Brandname Size qty CreatedBy CreatedDate ModifiedBy ModifiedDate Section Remarks)

From this table I had created two View View1(Openingstock) and
View2(Closingstock)

Todays Stock is considered as Closingstock which is hold by the
View named View2 and
Stock record of day before todays is in View1(OpeningStock)

This query is for view1(Openingsock)
SELECT dbo.Inventory.ItemCode, dbo.ItemMaster.Description, dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, SUM(dbo.Inventory.qty)
AS Totalqty
FROM dbo.Inventory INNER JOIN
dbo.ItemMaster ON dbo.Inventory.ItemCode = dbo.ItemMaster.ItemCode
WHERE (NOT (dbo.Inventory.Voucherdate = CONVERT(varchar(20), GETDATE(), 103))) AND (dbo.Inventory.Voucherdate > CONVERT(varchar(20), GETDATE(), 101))
GROUP BY dbo.Inventory.ItemCode, dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, dbo.ItemMaster.Description

This query is for view2(ClosingStock)

SELECT DISTINCT
dbo.Inventory.ItemCode, dbo.ItemMaster.Description, dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, SUM(dbo.Inventory.qty)
AS Closingqty
FROM dbo.Inventory RIGHT OUTER JOIN
dbo.ItemMaster ON dbo.Inventory.ItemCode = dbo.ItemMaster.ItemCode
WHERE (dbo.Inventory.Voucherdate = CONVERT(varchar(20), GETDATE(), 103))
GROUP BY dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, dbo.ItemMaster.Description, dbo.Inventory.ItemCode

I want to make View3 combining both the fields in view1
and view2.Such that both openingstock and closingstock comes together.

I had made the View3 like this

SELECT v2.ItemCode, v2.Description, v2.ItemType, v2.BrandName, v2.Size, v2.Totalqty, v1.Closingqty
FROM dbo.ViewStock AS v2 LEFT OUTER JOIN
dbo.ClosingStock AS v1 ON v1.ItemCode = v2.ItemCode AND v1.ItemType = v2.ItemType AND v1.BrandName = v2.BrandName

But the problem here is new items added in view2 which is not in view1 don't get displayed.if it is in View1 only than it is displayed.I hope this will help u to understand my problem.

Keshab

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 03:22:26

You have told about two views and in final query you have used a third one (ViewStock). WHat is query used for this view? or is this same as openingstock? If thats the case then according to your requirement shouldnt the tables be used in reverse order in join?
i.e ClosingStock LEFT OUTER JOIN ViewStock (opening stock) so as to get all items in view2 (closingstock) which is not in view1 (openingstock) also?

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-11 : 03:42:02

Hi The 3rd query is like this

SELECT v2.ItemCode, v2.Description, v2.ItemType, v2.BrandName, v2.Size, v2.Totalqty, v1.Closingqty
FROM dbo.View1 AS v2 LEFT OUTER JOIN
dbo.View2 AS v1 ON v1.ItemCode = v2.ItemCode AND v1.ItemType = v2.ItemType AND v1.BrandName = v2.BrandName

Keshab

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:01:09

quote:
Originally posted by Keshaba

Hi The 3rd query is like this

SELECT v2.ItemCode, v2.Description, v2.ItemType, v2.BrandName, v2.Size, v2.Totalqty, v1.Closingqty
FROM dbo.View1 AS v2 LEFT OUTER JOIN
dbo.View2 AS v1 ON v1.ItemCode = v2.ItemCode AND v1.ItemType = v2.ItemType AND v1.BrandName = v2.BrandName

Keshab

As expected this doent return you items in View2 thats not in View1. For returning those items, you need to reverse order of views in join

SELECT fields
FROM dbo.View2 AS v1 LEFT OUTER JOIN
dbo.View1 AS v2
ON v1.ItemCode = v2.ItemCode AND v1.ItemType = v2.ItemType AND v1.BrandName = v2.BrandName

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-11 : 05:17:40

Hi I tried the query as u had written But all the fields Available in View2 are still not available equally in all rows.When i run the query as you had written I get the reports like this...

I010 Monitor C.T.F L.G 12 15 25
NULL NULL NULL NULL NULL NULL 15

Keshab

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 07:09:51

I think you jsut updated your last query with what i gave you. use like this & see:-

SELECT v2.ItemCode, v2.Description, v2.ItemType, v2.BrandName, v2.Size, v2.Totalqty, v2.Closingqty
FROM dbo.View2 AS v2 LEFT OUTER JOIN
dbo.View1 AS v1 ON v1.ItemCode = v2.ItemCode AND v1.ItemType = v2.ItemType AND v1.BrandName = v2.BrandName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources