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.tblTargetWHERE [SalesChannel] = 'HR' AND [Period] = @thismonthSELECTSUM(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
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
You dont need merge ... what you need is join for two select statements
RehanYousaf
Can you paste the results for both of your select statements?
Cluskitt
One thing I forgot, since you're using SUM for two of the fields, you'll have to add:
GROUP BY Target
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
gremlin150
ASKER
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!
Cluskitt
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).