Help with query for a view - 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

General SQL Server Forums

New to SQL Server Programming

Help with query for a view

Author

Topic

tig2810
Starting Member

9 Posts

Posted - 2009-03-24 : 05:49:07

Hi
I’m a little new to SQL and was looking for some assistance. I tried Google but I don’t know the correct functions or terminology to look for.

I have 2 tables as below.

--- Sales Revenue Table ---
ID Reference CostCode Amount
1 1234 1 10.00
2 4567 11 100.00
3 4567 11 120.00
4 8956 20 25.00
5 8956 12 15.00

--- Sales Costs Table ---
ID Reference CostCode Amount
1 1234 1 -15.00
2 4567 11 -80.00
3 8956 20 -15.00

I want to create a view and the the join would be on Reference. I want to show 1 line per cost code, being an total of all positive and negative figures per costcode.

--- Gross Profit View ---
Reference CostCode Amount
1234 1 -5.00
4567 11 205.00
8956 20 10.00
8956 12 15.00

Any help would be appreciated.
Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 06:21:09

Easiest way to get started creating Views is to use the wizard in Management Studio. Right click on Views / New View.
Click on the tables you want to add and the columns to display. Drag a join between the matching fields. Once saved you can look at the script by right-clicking on the View you created and choose:
Script View / Alter To.
In your example above the Amount would be a subtraction of two Amounts with an alias:
E.g
CREATE VIEW [dbo].[vwTest]
AS
Select a.Reference, a.CostCode, a.Amount - b.Amount as Amount
from a
JOIN b
ON
a.ID = b.ID

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-24 : 07:24:33

I do not understand where you get 4567 11 205.00 from but maybe you want something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.YourView
AS

SELECT Reference, CostCode, SUM(Amount) AS Amount
FROM
(
    SELECT Reference, CostCode, Amount
    FROM SalesRevenue
    UNION ALL
    SELECT Reference, CostCode, Amount
    FROM SalesCosts
) D
GROUP BY Reference, CostCode
GO

tig2810
Starting Member

9 Posts

Posted - 2009-03-24 : 13:42:13

Hi Thanks for that. It almost works but the costcode 12 is missing there is a line in sales revenue but not in sales costs. I do not know how to change the effective join in the query you sent?
thanks for your help

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-25 : 05:39:09

Again - if you need to learn joins etc you can use design wizard to help.
Right click on the view created and choose design. You can see the join in top pane. Right click on join and you are given options - e.g. "select all rows from SalesRevenue". If you choose this the query will be modified eg:
INNER JOIN becomes LEFT OUTER JOIN

So your view should look something like:

ALTER VIEW [dbo].[vwTest]
AS
Select a.Reference, a.CostCode, a.Amount - b.Amount as Amount
from SalesRevenue a
LEFT OUTER JOIN SalesCosts b
ON
a.ID = b.ID

tig2810
Starting Member

9 Posts

Posted - 2009-03-25 : 18:45:54

Hi
This works thanks but if there is a cost in one column but no revenue and vice versa, it will not show this line. I do not see how I can use/change the join with this syntax?

quote:

Originally posted by Ifor

I do not understand where you get 4567 11 205.00 from but maybe you want something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.YourView
AS

SELECT Reference, CostCode, SUM(Amount) AS Amount
FROM
(
    SELECT Reference, CostCode, Amount
    FROM SalesRevenue
    UNION ALL
    SELECT Reference, CostCode, Amount
    FROM SalesCosts
) D
GROUP BY Reference, CostCode
GO

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-26 : 08:29:13

<quote> if there is a cost in one column but no revenue and vice versa, it will not show this line</quote>

Would you care to prove this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources