Confused about merging views - 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 2005 Forums

Transact-SQL (2005)

Confused about merging views

Author

Topic

Mickybee
Starting Member

29 Posts

Posted - 2008-11-20 : 15:56:37

Hi all,
Whenever I get stuck I always seem to come back here to you guru's. Sorry but Im stuck again.

I have two views. One contains credits and the other contains debits.

The views have the same columns:
id MonthYear Value

Credits looks like this
id MonthYear Value
1 May 2008 30.00
2 Jun 2008 150.00
3 Aug 2008 20.00

Debits looks like this
id MonthYear Value
1 Apr 2008 -20.00
2 Jun 2008 -95.00
3 Jul 2008 -55.00
4 Aug 2008 -30.00

What I need to do is to create two additional views. The first looking as follows and which shows the transactions each month e.g.
id MonthYear Value
1 Apr 2008 -20.00
2 May 2008 30.00
3 Jun 2008 150.00
4 Jun 2008 -95.00
5 Jul 2008 -55.00
6 Aug 2008 20.00
7 Aug 2008 -30.00

The second with the credits and debits as separate columns e.g.
id MonthYear Credit Debit
1 Apr 2008 -20.00
2 May 2008 30.00
3 Jun 2008 150.00 -95.00
5 Jul 2008 -55.00
6 Aug 2008 20.00 -30.00

Im not a sql expert im afraid and I know this is probably not the right thing to do but there will only ever be a maximum of 12 records in each source view so can somebody help me work out the syntax for each please (i know the basics but not much mroe)

Many thanks as always,
Mike

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-20 : 16:09:56

[code]
select * from
(select c.monthyear, c.value from @credit c
union all
select d.monthyear, d.value from @debit d)t
order by cast(monthyear as datetime)

select case when c.monthyear is not null then c.monthyear else d.monthyear end as monthyear,
c.value as credit, d.value as debit
from @credit c full join @debit d
on c.monthyear = d.monthyear
order by cast(case when c.monthyear is not null then c.monthyear else d.monthyear end as datetime)
[/code]

Mickybee
Starting Member

29 Posts

Posted - 2008-11-20 : 16:20:39

Many thanks for helping hanbingl,

Im using SQL Server Management Studio Express to create the view. I am getting an error message for both @credit and @debit. The error states must declare the table variable.

Any idea how I can do this in the sql view generator

Thanks
Mike

Mickybee
Starting Member

29 Posts

Posted - 2008-11-20 : 16:21:47

WHOOPS SORRY!!!!

I removed the @ and it works brilliantly.

Many thanks again
Mike

Mickybee
Starting Member

29 Posts

Posted - 2008-11-21 : 02:40:45

Ive found a bit of a flaw, the views I described had a non-displayed ID. Both the Credits and Debits Views have a foreignKey called Person_id.

Basically it holds credits and debits for multiple customers.

I worked out how to display the ID on the view (checked the visible box).

Can you help one more time please. I have worked out how to display and include the Person_id on the first query as follows

select * from
(select Person_id c.monthyear, c.value from @credit c
union all
select d.monthyear, d.value from @debit d)t
order by cast(monthyear as datetime)

I dont have any idea how to modify the following query to incorporate the Person_id

select case when c.monthyear is not null then c.monthyear else d.monthyear end as monthyear,
c.value as credit, d.value as debit
from @credit c full join @debit d
on c.monthyear = d.monthyear
order by cast(case when c.monthyear is not null then c.monthyear else d.monthyear end as datetime)

It would be fantastic if the end view could look like this:

id MonthYear Credit Debit Person_id
1 Apr 2008 -20.00 1
2 May 2008 30.00 1
3 Jun 2008 150.00 2
4 Jun 2008 -95.00 1
5 Jul 2008 -55.00 2
6 Aug 2008 20.00 -30.00 2

Basically, where the credit or debit relates to the same person for the month then keep the to together as in Aug above, where they relate to different customers sepearate them as in June.

The thing I really need is the ability to show all credits/debits for a particular person.

Very many thanks, Im sorry for being such a pain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources