maqskywalker
asked on
sql server 2008 query
Hi experts,
I'm using SQL Server 2008 and SSMS.
I need help on a query i'm creating. This query uses data from 5 different tables.
I have these tables inside of a database called TestDatabase
My 5 tables are the following:
Orders
OrderPayments
OrderAccounts
Vendors
PurchasesTable
For easy reference here is the script to create my 5 tables.
Query 1
So if I run this query:
I get the following result set:
Query 2
If I run this query:
I get the following result set:
Basically what I need is to add the columns called DepositAmount, CreditAmount, Balance from Query 2 to the result set of Query 1.
So my query should return the following result
which is basically joining Query 1 and Query 2.
But I'm not sure on how to join Query 1 with Query 2.
I placed Query 1 in a view called vwQuery1
and Query 2 in a view called vwQuery2
Here is the code to create these views:
Then I joined these two views like with this code:
and I get this result, which is my desired result set:
I was able to join my queries by putting them in a view, but in a view I can't use parameters so that is why I don't want to put them in a view.
Basically I just need join the result of Query 1 with the result of Query 2 using a normal query so then I can use it in a Stored Procedure that uses the parameters @MyYear and @Region
I need to join the result sets of these two queries just like I did in the join of the view above, where I used these columns
Q1.[Region] = Q2.[Region] AND
Q1.[OrderNumber] = Q2.[ReferenceNumber] AND
Q1.[MyYear] = Q2.[MyYear]
Can anyone help?
I'm using SQL Server 2008 and SSMS.
I need help on a query i'm creating. This query uses data from 5 different tables.
I have these tables inside of a database called TestDatabase
My 5 tables are the following:
Orders
OrderPayments
OrderAccounts
Vendors
PurchasesTable
For easy reference here is the script to create my 5 tables.
CREATE TABLE [dbo].[Orders](
[Region] [smallint] NOT NULL,
[MyYear] [smallint] NOT NULL,
[OrderNumber] [int] NOT NULL,
[OrderDate] [smalldatetime] NOT NULL,
[VendorNumber] [int] NOT NULL,
[OrderFlag] [char](1) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Orders] ([Region], [MyYear], [OrderNumber], [OrderDate], [VendorNumber], [OrderFlag]) VALUES (1, 2013, 1001, CAST(0xA0490000 AS SmallDateTime), 5, N'N')
INSERT [dbo].[Orders] ([Region], [MyYear], [OrderNumber], [OrderDate], [VendorNumber], [OrderFlag]) VALUES (1, 2013, 1002, CAST(0xA04D0000 AS SmallDateTime), 6, N'N')
GO
CREATE TABLE [dbo].[OrderPayments](
[Region] [smallint] NOT NULL,
[MyYear] [smallint] NOT NULL,
[ReferenceType] [char](4) NOT NULL,
[ReferenceNumber] [int] NOT NULL,
[PaymentId] [int] NOT NULL,
[PaymentAmount] [money] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 1, 9.7000)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'Voucher', 1001, 449, 220.0000)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 1, 1800.0000)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 2, 1600.0000)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 3, 3237.8900)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 3, 220.7400)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'Voucher', 1002, 451, 97.6700)
INSERT [dbo].[OrderPayments] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [PaymentId], [PaymentAmount]) VALUES (1, 2013, N'Voucher', 1002, 451, 54.3200)
GO
CREATE TABLE [dbo].[OrderAccounts](
[Region] [smallint] NOT NULL,
[MyYear] [smallint] NOT NULL,
[OrderNumber] [int] NOT NULL,
[POAmount] [money] NOT NULL,
[PONumber] [tinyint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[OrderAccounts] ([Region], [MyYear], [OrderNumber], [POAmount], [PONumber]) VALUES (1, 2013, 1001, 9.7000, 1)
INSERT [dbo].[OrderAccounts] ([Region], [MyYear], [OrderNumber], [POAmount], [PONumber]) VALUES (1, 2013, 1002, 1800.0000, 1)
INSERT [dbo].[OrderAccounts] ([Region], [MyYear], [OrderNumber], [POAmount], [PONumber]) VALUES (1, 2013, 1002, 1600.0000, 2)
INSERT [dbo].[OrderAccounts] ([Region], [MyYear], [OrderNumber], [POAmount], [PONumber]) VALUES (1, 2013, 1002, 3537.0900, 3)
GO
CREATE TABLE [dbo].[Vendors](
[Region] [smallint] NOT NULL,
[VendorNumber] [int] NOT NULL,
[VendorName] [char](30) NOT NULL,
[VendorLegalName] [char](30) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Vendors] ([Region], [VendorNumber], [VendorName], [VendorLegalName]) VALUES (1, 6, N'Dunder Mifflin', N'Dunder Mifflin Inc.')
INSERT [dbo].[Vendors] ([Region], [VendorNumber], [VendorName], [VendorLegalName]) VALUES (1, 5, N'Acme', N'Acme Inc.')
GO
CREATE TABLE [dbo].[PurchasesTable](
[Region] [smallint] NOT NULL,
[MyYear] [smallint] NOT NULL,
[ReferenceType] [char](14) NOT NULL,
[ReferenceNumber] [int] NOT NULL,
[DepositAmount] [money] NULL,
[CreditAmount] [money] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 50.0000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 0.0000, 50.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 50.0000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 0.0000, 50.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 9.7000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1001, 0.0000, 9.7000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1001, 220.0000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1001, 0.0000, 220.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 3537.0900, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 0.0000, 3237.8900)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 0.0000, 220.7400)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 1800.0000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 0.0000, 1800.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 1600.0000, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'PurchaseOrder', 1002, 0.0000, 1600.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1002, 54.3200, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1002, 0.0000, 54.3200)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1002, 97.6700, 0.0000)
INSERT [dbo].[PurchasesTable] ([Region], [MyYear], [ReferenceType], [ReferenceNumber], [DepositAmount], [CreditAmount]) VALUES (1, 2013, N'Voucher', 1002, 0.0000, 97.6700)Query 1
So if I run this query:
DECLARE @MyYear varchar (50), @Region varchar(50)
SET @MyYear = 2013
SET @Region = 1
-- Query 1
SELECT por.Region
,por.MyYear
,por.OrderNumber
,por.[OrderDate]
,por.VendorNumber
,v.VendorName
,v.VendorLegalName
,CASE por.[OrderFlag]
WHEN 'Y' THEN 'Void'
WHEN 'N' THEN 'Open'
END AS OrderFlag
,op.ReferenceType
,SUM(oa.POAmount) AS Amount
,SUM(op.PaymentAmount) AS Payment
FROM [TestDatabase].[dbo].[Orders] por
LEFT JOIN [TestDatabase].[dbo].[OrderAccounts] oa ON
oa.Region = por.Region AND
oa.OrderNumber = por.OrderNumber AND
oa.MyYear = por.MyYear
LEFT JOIN [TestDatabase].[dbo].[OrderPayments] op ON
oa.Region = op.Region AND
oa.MyYear = op.MyYear AND
oa.OrderNumber = op.ReferenceNumber AND
oa.PONumber = op.PaymentId
LEFT JOIN [TestDatabase].[dbo].[Vendors] v ON
v.Region = por.Region AND
v.VendorNumber = por.VendorNumber
WHERE op.ReferenceType = 'PurchaseOrder' AND por.MyYear = @MyYear AND por.Region = @Region
GROUP BY por.Region
,por.MyYear
,por.OrderNumber
,por.OrderDate
,por.VendorNumber
,v.VendorName
,v.VendorLegalName
,por.OrderFlag
,op.ReferenceTypeI get the following result set:
Query 2
If I run this query:
DECLARE @MyYear varchar (50), @Region varchar(50)
SET @MyYear = 2013
SET @Region = 1
-- Query 2
SELECT [Region]
,[MyYear]
,[ReferenceNumber]
,Sum([DepositAmount]) AS DepositAmount
,Sum([CreditAmount]) AS CreditAmount
,Sum([DepositAmount] - [CreditAmount]) AS Balance
FROM
(SELECT pt.[Region]
,pt.[MyYear]
,pt.[ReferenceNumber]
,Sum([DepositAmount]) AS DepositAmount
,0 AS CreditAmount
FROM [TestDatabase].[dbo].[PurchasesTable] pt
WHERE pt.[MyYear] = @MyYear
AND pt.[Region] = @Region
AND pt.[ReferenceType] = 'PurchaseOrder'
GROUP BY pt.[Region], pt.[MyYear], pt.[ReferenceNumber]
UNION
SELECT pt.[Region]
,pt.[MyYear]
,pt.[ReferenceNumber]
,0 AS DepositAmount
,Sum(pt.[CreditAmount]) AS CreditAmount
FROM [TestDatabase].[dbo].[PurchasesTable] pt
WHERE pt.[MyYear] = @MyYear
AND pt.[Region] = @Region
AND pt.[ReferenceType] = 'PurchaseOrder'
GROUP BY pt.[Region], pt.[MyYear], pt.[ReferenceNumber]
)
AS FinalSET
Group By [Region]
,[MyYear]
,[ReferenceNumber]I get the following result set:
Basically what I need is to add the columns called DepositAmount, CreditAmount, Balance from Query 2 to the result set of Query 1.
So my query should return the following result
which is basically joining Query 1 and Query 2.
But I'm not sure on how to join Query 1 with Query 2.
I placed Query 1 in a view called vwQuery1
and Query 2 in a view called vwQuery2
Here is the code to create these views:
CREATE VIEW [dbo].[vwQuery1]
AS
-- Query 1
SELECT por.Region
,por.MyYear
,por.OrderNumber
,por.[OrderDate]
,por.VendorNumber
,v.VendorName
,v.VendorLegalName
,CASE por.[OrderFlag]
WHEN 'Y' THEN 'Void'
WHEN 'N' THEN 'Open'
END AS OrderFlag
,op.ReferenceType
,SUM(oa.POAmount) AS Amount
,SUM(op.PaymentAmount) AS Payment
FROM [TestDatabase].[dbo].[Orders] por
LEFT JOIN [TestDatabase].[dbo].[OrderAccounts] oa ON
oa.Region = por.Region AND
oa.OrderNumber = por.OrderNumber AND
oa.MyYear = por.MyYear
LEFT JOIN [TestDatabase].[dbo].[OrderPayments] op ON
oa.Region = op.Region AND
oa.MyYear = op.MyYear AND
oa.OrderNumber = op.ReferenceNumber AND
oa.PONumber = op.PaymentId
LEFT JOIN [TestDatabase].[dbo].[Vendors] v ON
v.Region = por.Region AND
v.VendorNumber = por.VendorNumber
WHERE op.ReferenceType = 'PurchaseOrder' AND por.MyYear = 2013 AND por.Region = 1
GROUP BY por.Region
,por.MyYear
,por.OrderNumber
,por.OrderDate
,por.VendorNumber
,v.VendorName
,v.VendorLegalName
,por.OrderFlag
,op.ReferenceType
GO
CREATE VIEW [dbo].[vwQuery2]
AS
-- Query 2
SELECT [Region]
,[MyYear]
,[ReferenceNumber]
,Sum([DepositAmount]) AS DepositAmount
,Sum([CreditAmount]) AS CreditAmount
,Sum([DepositAmount] - [CreditAmount]) AS Balance
FROM
(SELECT pt.[Region]
,pt.[MyYear]
,pt.[ReferenceNumber]
,Sum([DepositAmount]) AS DepositAmount
,0 AS CreditAmount
FROM [TestDatabase].[dbo].[PurchasesTable] pt
WHERE pt.[MyYear] = 2013
AND pt.[Region] = 1
AND pt.[ReferenceType] = 'PurchaseOrder'
GROUP BY pt.[Region], pt.[MyYear], pt.[ReferenceNumber]
UNION
SELECT pt.[Region]
,pt.[MyYear]
,pt.[ReferenceNumber]
,0 AS DepositAmount
,Sum(pt.[CreditAmount]) AS CreditAmount
FROM [TestDatabase].[dbo].[PurchasesTable] pt
WHERE pt.[MyYear] = 2013
AND pt.[Region] = 1
AND pt.[ReferenceType] = 'PurchaseOrder'
GROUP BY pt.[Region], pt.[MyYear], pt.[ReferenceNumber]
)
AS FinalSET
Group By [Region]
,[MyYear]
,[ReferenceNumber]
GOThen I joined these two views like with this code:
SELECT Q1.[Region]
,Q1.[MyYear]
,Q1.[OrderNumber]
,Q1.[OrderDate]
,Q1.[VendorNumber]
,Q1.[VendorName]
,Q1.[VendorLegalName]
,Q1.[OrderFlag]
,Q1.[ReferenceType]
,Q1.[Amount]
,Q1.[Payment]
,Q2.[DepositAmount]
,Q2.[CreditAmount]
,Q2.[Balance]
FROM [TestDatabase2].[dbo].[vwQuery1] Q1
LEFT JOIN [TestDatabase2].[dbo].[vwQuery2] Q2 ON
Q1.[Region] = Q2.[Region] AND
Q1.[OrderNumber] = Q2.[ReferenceNumber] AND
Q1.[MyYear] = Q2.[MyYear]and I get this result, which is my desired result set:
I was able to join my queries by putting them in a view, but in a view I can't use parameters so that is why I don't want to put them in a view.
Basically I just need join the result of Query 1 with the result of Query 2 using a normal query so then I can use it in a Stored Procedure that uses the parameters @MyYear and @Region
I need to join the result sets of these two queries just like I did in the join of the view above, where I used these columns
Q1.[Region] = Q2.[Region] AND
Q1.[OrderNumber] = Q2.[ReferenceNumber] AND
Q1.[MyYear] = Q2.[MyYear]
Can anyone help?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
thanks Eugene.
Open in new window