VIEW - UNION ALL - 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

Transact-SQL (2000)

VIEW - UNION ALL

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2012-05-23 : 16:20:30

Am developing an inventory application using VB6 and MSSQL2000.
I have two tables namely dbo.AStockCard01 and dbo.StockCard02.
Each table has the same number of columns and names, but values.
I created a view using UNION ALL in order to merge the two tables.
QtyIN is the Input value (including beginning balance) while QtyOUT
is the Output value or sales.I want it in such a way that those Warehouses
that doesn't have forward balance for a particular item, should insert a row,
and fill the fields with the same date of the available warehouse of the same item.

SUMMARY
1. Order by ItemID, Date1
2. When no forward balance(FWB), should insert a row. use the same date with the other warehouse(s) of the same ItemID. Then let QtyIN = 0, QtyOUT = 0, DocID = 'FWB', DocType = Forward Balance.

See the script below:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwStockCard]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwStockCard]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockCard01]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockCard01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockCard02]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockCard02]
GO

CREATE TABLE [dbo].[StockCard01] (
[Date1] [datetime] NULL ,
[ItemID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QtyIN] [float] NULL ,
[QtyOUT] [float] NULL ,
[Warehouse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WayBillID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeliveryID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StockCard02] (
[Date1] [datetime] NULL ,
[ItemID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QtyIN] [float] NULL ,
[QtyOUT] [float] NULL ,
[Warehouse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WayBillID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeliveryID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW vwStockCard
AS
Select Date1,ItemID,QtyIN,QtyOUT,(QtyIN-QtyOUT) AS Bal,Warehouse,
DocID =
CASE
WHEN WayBillID <> ''
THEN WayBillID
WHEN DeliveryID <> ''
THEN DeliveryID
ELSE
'FWB'
END,

DocType =
CASE
WHEN WayBillID <> ''
THEN 'Way Bill'
WHEN DeliveryID <> ''
THEN 'Delivery Notes'
ELSE
'Forward Balance'
END
FROM StockCard01
UNION ALL
Select Date1,ItemID,QtyIN,QtyOUT,(QtyIN-QtyOUT) AS Bal,Warehouse,
DocID =
CASE
WHEN WayBillID <> ''
THEN WayBillID
WHEN DeliveryID <> ''
THEN DeliveryID
ELSE
'FWB'
END,

DocType =
CASE
WHEN WayBillID <> ''
THEN 'Way Bill'
WHEN DeliveryID <> ''
THEN 'Delivery Notes'
ELSE
'Forward Balance'
END
FROM StockCard02

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

TABLE 1: StockCard01

Date1 ItemID QtyIN QtyOUT Warehouse WayBillID DeliveryID
03/09/2011 000001 10 0 WH1
03/09/2011 000001 40 0 WH2
03/09/2011 000001 30 0 WH3
03/09/2011 000002 100 0 WH1
04/09/2011 000001 35 0 WH1 D00001
04/09/2011 000001 20 0 WH1 D00001
04/09/2011 000002 50 0 WH3 D00001
06/09/2011 000003 15 0 WH2
10/09/2011 000001 0 30 WH1 W00001

TABLE 2: StockCard02

Date1 ItemID QtyIN QtyOUT Warehouse WayBillID DeliveryID
05/10/2011 000001 20 0 WH2 D00004

RESULT
Date1 ItemID QtyIN QtyOUT Bal Warehouse DocID DocType
03/09/2011 000001 10 0 10 WH1 FWB Forward Balance
03/09/2011 000001 40 0 40 WH2 FWB Forward Balance
03/09/2011 000001 30 0 30 WH3 FWB Forward Balance
03/09/2011 000002 100 0 100 WH1 FWB Forward Balance
04/09/2011 000001 35 0 35 WH1 D00001 Delivery Notes
04/09/2011 000001 20 0 20 WH1 D00001 Delivery Notes
04/09/2011 000002 50 0 50 WH3 D00001 Delivery Notes
06/09/2011 000003 15 0 15 WH2 FWB Forward Balance
10/09/2011 000001 0 30 -30 WH1 W00001 Way Bill
05/10/2011 000001 20 0 20 WH2 D00004 Delivery Notes

EXPECTED RESULT
Date1 ItemID QtyIN QtyOUT Bal Warehouse DocID DocType
03/09/2011 000001 10 0 10 WH1 FWB Forward Balance
03/09/2011 000001 40 0 40 WH2 FWB Forward Balance
03/09/2011 000001 30 0 30 WH3 FWB Forward Balance
03/09/2011 000002 100 0 100 WH1 FWB Forward Balance
03/09/2011 000002 0 0 0 WH2 FWB Forward Balance
03/09/2011 000002 0 0 0 WH3 FWB Forward Balance
04/09/2011 000001 35 0 35 WH1 D00001 Delivery Notes
04/09/2011 000001 20 0 20 WH1 D00001 Delivery Notes
04/09/2011 000002 50 0 50 WH3 D00001 Delivery Notes
06/09/2011 000003 0 0 0 WH1 FWB Forward Balance
06/09/2011 000003 15 0 15 WH2 FWB Forward Balance
06/09/2011 000003 0 0 0 WH3 FWB Forward Balance
10/09/2011 000001 0 30 -30 WH1 W00001 Way Bill
05/10/2011 000001 20 0 20 WH2 D00004 Delivery Notes

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-23 : 16:29:10

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175077

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources