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