We help IT Professionals succeed at work.

Merging SELECT statements

169 Views
Last Modified: 2013-01-08
Hi,

There is probably a simple solution to this but I need to resolve very urgently. Unfortunately I'm not too good with SQL!

I have a T-SQL query for SQL 2008

DECLARE @thismonth varchar(7)

SET @thismonth = CONVERT(varchar(4),GETDATE(),120) + '-' + CONVERT(varchar(2),GETDATE(),101)

SELECT [Target] AS [Budget GP] FROM dbo.tblTarget
WHERE [SalesChannel] = 'HR' AND [Period] = @thismonth

SELECT
SUM(ISNULL(CASE WHEN [Trx Type]='credit' THEN (GP * -1) WHEN [Trx Type]='invoice' THEN (GP) END , 0)) AS [Billing] , 

SUM(ISNULL(CASE WHEN [Trx Type]='booking' THEN GP END , 0)) AS [Booking] 

FROM  dbo.tblTransactions 

WHERE [Sales Channel] = 'HR' AND [Doc Date YYYY-MM] = @thismonth

Open in new window

How can I merge the two select statements into one so I see the Budget / Billing / Booking columns in the same result?

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
You dont need merge ... what you need is join for two select statements
Can you paste the results for both of your select statements?
CERTIFIED EXPERT

Commented:
One thing I forgot, since you're using SUM for two of the fields, you'll have to add:
GROUP BY Target

Author

Commented:
Thanks all!

Cluskitt's solution worked (once the GROUP BY clause was added). I needed to change the line ON T1.SalesChannel=T2.SalesChannel AND T1.Period=T2.[Doc Date YYYY-MM] to ON T1.[SalesChannel]=T2.[Sales Channel] AND T1.Period = @thismonth AND T2.[Doc Date YYYY-MM] = @thismonth as it was showing all targets and sales info rather than for the current month. But it works so thanks for your help as it got me out of a jam!
CERTIFIED EXPERT

Commented:
You should leave the on clause as it was. It was my bad that you should have added:
AND T1.Period=@thismonth
to the WHERE clause (you could also compare to the doc date. Either would be fine but only one is necessary because of the join).

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.