Combine Query Results - 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)

Combine Query Results

Author

Topic

Tyork
Starting Member

6 Posts

Posted - 2011-06-21 : 13:08:41

I have two queries which result in the following fields:

Query 1

le_name
Bin_Volume
ReceiveDate
Ct_TotalApproved

Query 2

le_name
Bin_Volume
ReceivedDate
Bin_Execution
Ct_TotalBooked

I would like to combine the results so that I end up with one table consisting of

le_name
Bin_Volume
ReceivedDate
Bin_Execution
Ct_TotalBooked
Ct_TotalApproved

Join would be on

le_name
Bin_Volume
ReceivedDate

I tried setting up one of the queries with a 'WITH' statement and trying to query the result from the second query however it didnt work out. Any help would be greatly appreciated. I've included the standalone queries below for reference.


	/*QUERY 1*/
	
	SELECT
		le_name,
		CASE
			WHEN booked_amount < 10000 THEN 0
			WHEN booked_amount < 25000 AND booked_amount >= 10000 THEN 10000
			WHEN booked_amount < 50000 AND booked_amount >= 25000 THEN 25000
			WHEN booked_amount < 100000 AND booked_amount >= 50000 THEN 50000
			WHEN booked_amount < 250000 AND booked_amount >= 100000 THEN 100000
			ELSE 250000
		END as Bin_volume,
		CASE
			WHEN LEN(MONTH(application_received_date)) = 1 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + '0' + CONVERT(VARCHAR(2), MONTH(application_received_date))
			WHEN LEN(MONTH(application_received_date)) = 2 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + CONVERT(VARCHAR(2), MONTH(application_received_date))
		END as ReceivedDate,
		SUM(Approved_No) as Ct_TotalApproved
	FROM
		dbo.application
		WITH (nolock)
	WHERE 
		application_received_date > getdate() - 390
		AND left(le_name,3) <> 'zzz'
	GROUP BY
		le_name,
		CASE
			WHEN booked_amount < 10000 THEN 0
			WHEN booked_amount < 25000 AND booked_amount >= 10000 THEN 10000
			WHEN booked_amount < 50000 AND booked_amount >= 25000 THEN 25000
			WHEN booked_amount < 100000 AND booked_amount >= 50000 THEN 50000
			WHEN booked_amount < 250000 AND booked_amount >= 100000 THEN 100000
			ELSE 250000
		END,
		CASE
			WHEN LEN(MONTH(application_received_date)) = 1 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + '0' + CONVERT(VARCHAR(2), MONTH(application_received_date))
			WHEN LEN(MONTH(application_received_date)) = 2 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + CONVERT(VARCHAR(2), MONTH(application_received_date))
		END

		
		/*QUERY 2*/
		
SELECT
	dbo.application.le_name,
	CASE
		WHEN booked_amount < 10000 THEN 0
		WHEN booked_amount < 25000 AND booked_amount >= 10000 THEN 10000
		WHEN booked_amount < 50000 AND booked_amount >= 25000 THEN 25000
		WHEN booked_amount < 100000 AND booked_amount >= 50000 THEN 50000
		WHEN booked_amount < 250000 AND booked_amount >= 100000 THEN 100000
		ELSE 250000
	END as Bin_volume,
	CASE
		WHEN DaysToBooking = 0  THEN 0
		WHEN DaysToBooking >= 1 AND DaysToBooking < 5 THEN 1
		WHEN DaysToBooking >= 5 AND DaysToBooking < 10 THEN 5
		WHEN DaysToBooking >= 10 AND DaysToBooking < 15 THEN 10
		WHEN DaysToBooking >= 15 AND DaysToBooking < 20 THEN 15
		WHEN DaysToBooking >= 20 AND DaysToBooking < 25 THEN 20
		WHEN DaysToBooking >= 25 AND DaysToBooking < 30 THEN 25
		WHEN DaysToBooking >= 30 AND DaysToBooking < 45 THEN 30
		WHEN DaysToBooking >= 45 AND DaysToBooking < 60 THEN 45
		WHEN DaysToBooking >= 60 AND DaysToBooking < 75 THEN 60
		WHEN DaysToBooking >= 75 AND DaysToBooking < 90 THEN 75
		WHEN DaysToBooking >= 90 THEN 90
	END as Bin_Execution,
	CASE
		WHEN LEN(MONTH(application_received_date)) = 1 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + '0' + CONVERT(VARCHAR(2), MONTH(application_received_date))
		WHEN LEN(MONTH(application_received_date)) = 2 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + CONVERT(VARCHAR(2), MONTH(application_received_date))
	END as ReceivedDate,
	SUM(BookedCount) as Ct_Booked
FROM
	dbo.application
	WITH (nolock)
WHERE 
	application_received_date > getdate() - 390
	AND left(dbo.application.le_name,3) <> 'zzz'
GROUP BY
	dbo.application.le_name,
	CASE
		WHEN booked_amount < 10000 THEN 0
		WHEN booked_amount < 25000 AND booked_amount >= 10000 THEN 10000
		WHEN booked_amount < 50000 AND booked_amount >= 25000 THEN 25000
		WHEN booked_amount < 100000 AND booked_amount >= 50000 THEN 50000
		WHEN booked_amount < 250000 AND booked_amount >= 100000 THEN 100000
		ELSE 250000
	END,
	CASE
		WHEN DaysToBooking = 0  THEN 0
		WHEN DaysToBooking >= 1 AND DaysToBooking < 5 THEN 1
		WHEN DaysToBooking >= 5 AND DaysToBooking < 10 THEN 5
		WHEN DaysToBooking >= 10 AND DaysToBooking < 15 THEN 10
		WHEN DaysToBooking >= 15 AND DaysToBooking < 20 THEN 15
		WHEN DaysToBooking >= 20 AND DaysToBooking < 25 THEN 20
		WHEN DaysToBooking >= 25 AND DaysToBooking < 30 THEN 25
		WHEN DaysToBooking >= 30 AND DaysToBooking < 45 THEN 30
		WHEN DaysToBooking >= 45 AND DaysToBooking < 60 THEN 45
		WHEN DaysToBooking >= 60 AND DaysToBooking < 75 THEN 60
		WHEN DaysToBooking >= 75 AND DaysToBooking < 90 THEN 75
		WHEN DaysToBooking >= 90 THEN 90
	END,
	CASE
		WHEN LEN(MONTH(application_received_date)) = 1 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + '0' + CONVERT(VARCHAR(2), MONTH(application_received_date))
		WHEN LEN(MONTH(application_received_date)) = 2 THEN CONVERT(VARCHAR(4), YEAR(application_received_date)) + CONVERT(VARCHAR(2), MONTH(application_received_date))
	END

Tyork

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2011-06-21 : 14:12:11

Try this.. I haven't tested..

Select a.le_name,a.Bin_Volume,a.ReceivedDate,b.Bin_Execution,b.Ct_TotalBooked,a.Ct_TotalApproved
from (Query1) as a Join (Query2) as b on a.le_name=b.le_name ad a.Bin_Volume=b.Bin_Volume,a.ReceivedDate=b.ReceivedDate

Looks like both the queies are similar except the Bin_Executio on the second query.. can't you select it in a single query?

==============================
Work smarter not harder take control of your life be a super achiever

Tyork
Starting Member

6 Posts

Posted - 2011-06-21 : 14:40:33

Thanks, that works perfectly.

I can't handle in one query because Ct_totalApproved is grouped only by bin_volume and ReceivedDate whereas Ct_booked is grouped by an additional dimension. I wouldnt get the same numbers as I would by joining the two queries.

Tyork

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources