Joining Two Queries - 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 2000 Forums

SQL Server Development (2000)

Joining Two Queries

Author

Topic

UnathiM
Starting Member

8 Posts

Posted - 2006-11-14 : 01:21:07

Hi Guys

I have two queries that I want to join. I have tried using UNION but then some of the data comes as a row instead of a column. Any ideas and suggestions will be most welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 01:23:52

Post the queries here.
I have never heard of a UNION converting a row to a column. You most probably have a syntax problem in your query.

Peter Larsson
Helsingborg, Sweden

UnathiM
Starting Member

8 Posts

Posted - 2006-11-14 : 02:12:06

select datepart(yy, date) as 'Year', datepart(mm, date) as 'Month',
case when Btype in ('A','B', 'C') then 'DA'
when Btype in ('D','E', 'F') then 'DS'
when Btype in ('G','H','I') then 'DC'
else 'other '
end,
count(*) as 'Total Approvals'
from Table A join Table B on A.dealer = B.branch
join Table C on A.refno = C.refno
where date >= '14 Jan 2006'
and scoredatetime = (select min (scoredatetime) from Table C where refno = a.refno )
and decision = 'A APPROVE'
group by datepart(yy, date), datepart(mm, date),
case when Btype in ('A','B', 'C') then 'DA'
when Btype in ('D','E', 'F') then 'DS'
when Btype in ('G','H','I') then 'DC'
else 'other '
end

union
select datepart(yy, date), datepart(mm, date),
case when Btype in ('A','B', 'C') then 'DA'
when Btype in ('D','E', 'F') then 'DS'
when Btype in ('G','H','I') then 'DC'
else 'other '
end,
count(*)as 'TotalApps'
from Table A join Table B on A.dealer = B.branch
join Table C on A.refno = C.refno
where date >= '14 Jan 2006'
and scoredatetime = (select min (scoredatetime) from Table C where refno = a.refno )
group by datepart(yy, date), datepart(mm, date),
case when Btype in ('A','B', 'C') then 'DA'
when Btype in ('D','E', 'F') then 'DS'
when Btype in ('G','H','I') then 'DC'
else 'other '
end
order by datepart(yy, date), datepart(mm, date),
case when Btype in ('A','B', 'C') then 'DA'
when Btype in ('D','E', 'F') then 'DS'
when Btype in ('G','H','I') then 'DC'
else 'other '
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 02:15:57

Do you self-join table "TABLE" two times?
Or is it different tables?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 02:21:15

Your query can be somewhat simplified like this.
YOU NEED TO LEARN TO PREFIX YOUR COLUMNS WHEN USING MORE THAN ONE TABLE IN A QUERY.

select		datepart(yy, xx.date) as 'Year',
		datepart(mm, xx.date) as 'Month', 
		case
			when xx.Btype in ('A','B', 'C') then 'DA' 
			when xx.Btype in ('D','E', 'F') then 'DS'
			when xx.Btype in ('G','H','I') then 'DC' 
			else 'other ' 
		end,
		sum(case when xx.decision = 'A APPROVE' then 1 else 0 end) as 'Total Approvals',
		count(*) as 'TotalApps'
from		Table A
inner join	Table B on A.dealer = B.branch
inner join	Table C on A.refno = C.refno
where		xx.date >= '14 Jan 2006'
		and xx.scoredatetime = (select min (d.scoredatetime) from Table D where d.refno = a.refno )
group by	datepart(yy, xx.date),
		datepart(mm, xx.date),
		case
			when xx.Btype in ('A','B', 'C') then 'DA' 
			when xx.Btype in ('D','E', 'F') then 'DS'
			when xx.Btype in ('G','H','I') then 'DC' 
			else 'other ' 
		end

Substitute "xx." with the tablename or table alias you want to use.

Peter Larsson
Helsingborg, Sweden

UnathiM
Starting Member

8 Posts

Posted - 2006-11-14 : 03:13:00

THANK YOU VERY MUCH!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 09:44:54

>>where xx.date >= '14 Jan 2006'
I prefer using YYYYMMDD or YYYY-MM-DD format because DD MMM YYYY may not work properly in non-English Servers

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources