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

SQL Server Development (2000)

VIEW - UNION ALL

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2012-05-23 : 16:05:23

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 15:48:37

looks like what you need is a cross join between Date1 ItemID fields and then left join result to your table to capture rest of table info if present or else fill them with 0 using isnull() or coalesce()

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

noblemfd
Starting Member

38 Posts

Posted - 2012-05-27 : 15:05:16

pls enlighten me more. how do i go about that

quote:
Originally posted by visakh16

looks like what you need is a cross join between Date1 ItemID fields and then left join result to your table to capture rest of table info if present or else fill them with 0 using isnull() or coalesce()

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-27 : 15:17:11

[code]

CREATE VIEW vw_YourView
AS
SELECT *
FROM table1

UNION ALL

SELECT *
FROM table2
GO

SELECT t.Date1,
t.ItemID,
t.Warehouse,
COALESCE(t1.QtyIN,0) AS QtyIN,
COALESCE(t1.QtyOUT,0) AS QtyOUT,
COALESCE(t1.Bal,0) AS Bal,
....
FROM
(
SELECT Date1,ItemID,Warehouse
FROM (SELECT DISTINCT Date1,ItemID FROM vw_YourView)m
CROSS JOIN (SELECT DISTINCT Warehouse FROM vw_YourView)n
)t
LEFT JOIN vw_YourView t1
ON t1.Date1 = t.Date1
AND t1.ItemID = t.ItemID
AND t1.Warehouse = t.Warehouse
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources