Combine columns into result set.

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

Transact-SQL (2000)

Combine columns into result set.

Author

Topic

fireloard
Starting Member

23 Posts

Posted - 2005-08-16 : 09:55:21

Ok I have a table with lets say a date column and a quantity. I want a query that will show the total for a year I pass in and a total for the previous year in another column. This way the user can see the two totals side by side. So...

User 2005 Total 2004 Total
---- ---------- ----------
Me 1,000 1,500

The tables would look like this

Users
-----
User_id
Full Name

Sales
-----
user_id
date_id
quantity
amount

DateTable
---------
date_id
year
quarter
month

This is pretty close to the actual design but is not EXACTLY the same as the real database. I just wanted to give you an idea of how this was being joined together.

I tried multiple select's in query analyzer and I can't get them to merge or total correctly. I am starting to think i'll have to use a temp table where I total the current year in a stored a procedure and then total the previous year in another insert... THEN return the contents of that temp table to the user. Anyone have any suggetions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 09:57:47

>>I tried multiple select's in query analyzer and I can't get them to merge or total correctly.

Post the query you used

Madhivanan

Failing to plan is Planning to fail

fireloard
Starting Member

23 Posts

Posted - 2005-08-16 : 10:23:23

Didn't matter as they were all wrong attempts and I got rid of them. Just some quick tets I wrote up in QA.

rockystar
Starting Member

3 Posts

Posted - 2005-08-16 : 10:27:56

Are going to run this as a proc passing a year variable? If so try this:

declare @year int
set @year = 2005

select u.FullName
, sum(case when d.Year = @year then s.amount else 0 end) ThisYear
, sum(case when d.Year = @year-1 then s.amount else 0 end) LastYear
from #sales s inner join #users u
on s.UserID = u.UserID
inner join #dates d
on s.DateID = d.DateID
where
d.Year in (@year, @year-1)
group by u.FullName

or are you trying to create it as a view?

rockystar
Starting Member

3 Posts

Posted - 2005-08-16 : 10:39:43

this might also work:

select a1.FullName, a1.Year
, a1.amount ThisYear
, a2.amount LastYear
from
( select u.UserID, u.FullName, d.Year, sum(s.amount) amount
from #sales s inner join #users u
on s.UserID = u.UserID
inner join #dates d
on s.DateID = d.DateID
group by u.UserID, u.FullName, d.Year
) a1
left join
(select s2.UserID, d2.Year, sum(s2.amount) amount
from #sales s2 inner join #dates d2
on s2.DateID = d2.DateID
group by s2.UserID, d2.Year
) a2
on a1.UserID = a2.UserID
and a1.Year = a2.Year-1

fireloard
Starting Member

23 Posts

Posted - 2005-08-16 : 10:40:31

I'm creating this as a proc.

fireloard
Starting Member

23 Posts

Posted - 2005-08-16 : 10:46:48

This case statement works... thanks for the great idea. Saved me a lot of time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources