[mb2000inc]

I have this SELECT statement that is using a CASE WHEN condition.
The query returns MOSTLY correct data, but in two rows, there's duplicate information in most of the columns, but one of the columns is pulling the wrong info.

I've tried to upload a copy of the results, but was unable to. If you would like, I will Message you a copy of the results in excel format.

Any suggestions you have, would be greatly appreciated.

Here is my query:
(more comments below)

SELECT  DISTINCT  FL.[Event Profile Contact],
		FL.[Rebate ID],
		FL.[Promo Name],
		CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS 'Start Date',
		CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS 'End Date',
		CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS 'Final Date',
		COUNT(FL.[Rebate ID]) AS 'FY # Of RBTs',
		SUM(FL.[Total Rebate Amount])/(COUNT(FL.[Rebate ID])) AS 'FY AVG $ PER RBT',
		SUM(FL.[Total Rebate Amount]) AS 'FY TTL DOLLARS',
		CASE
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
			ELSE
				FL.[BUDGET FY'2012 or Accrued from FY'2010]
		END AS 'Budget Amount',
		CASE
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
			ELSE ISNULL(FL.[BUDGET FY'2012 or Accrued from FY'2010], 0.00) - SUM(FL.[Total Rebate Amount])
		END AS 'Variance TO Budget',
		CASE
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
			WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
			ELSE
				FL.[BUDGET FY'2012 or Accrued from FY'2010]
		END AS 'Ending Budget FY2011'
FROM vw_FlashReport_Part1_Select FL
WHERE FL.[Counter Set Date] BETWEEN '2011-07-01' AND '2012-02-01' --fiscal ytd
Group By FL.[Event Profile Contact],
		 FL.[Rebate ID],
		 FL.[Promo Name],
		 FL.[BUDGET FY'2012 or Accrued from FY'2010], 
		 FL.[Budget Amount],
		 FL.[Promo Start Date], 
		 FL.[Promo End Date], 
		 FL.[Promo Final RDMT Date]

The reason for the CASE WHEN is to say, that if there is any $value in the [BUDGET FY'2012 or Accrued from FY'2010] field, to use it, otherwise use the [Budget Amount] field. (and yes, in some cases, both fields have the same value)

Thanks in advance.

[e_i_pi]

Hmm, tough problem because I don't have all the information in front of me, but I'll give it a go. It looks similar to the sort of things I do at work every day (budget aggregation, conditional fields based on rolling budget values, etc.)

I won't say that this will solve your problem, but I'll ask you to try this, and report back with any problems that come up:

SELECT
	FL.[Event Profile Contact],
	FL.[Rebate ID],
	FL.[Promo Name],
	CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS 'Start Date',
	CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS 'End Date',
	CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS 'Final Date',
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS 'Budget Amount',
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
		ELSE ISNULL(FL.[BUDGET FY'2012 or Accrued from FY'2010], 0.00) - SUM(FL.[Total Rebate Amount])
	END AS 'Variance TO Budget',
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS 'Ending Budget FY2011'
FROM vw_FlashReport_Part1_Select FL
INNER JOIN (
	SELECT
		InnerFL.[Rebate ID],
		COUNT(InnerFL.[Rebate ID]) AS 'FY # Of RBTs',
		SUM(InnerFL.[Total Rebate Amount])/COUNT(InnerFL.[Rebate ID]) AS 'FY AVG $ PER RBT',
		SUM(InnerFL.[Total Rebate Amount]) AS 'FY TTL DOLLARS'
	FROM vw_FlashReport_Part1_Select InnerFL
	GROUP BY InnerFL.[Rebate ID]
) AS RBTAggregate ON RBTAggregate.[Rebate ID] = FL.[Rebate ID]
WHERE FL.[Counter Set Date] >= '2011-07-01'
AND FL.[Counter Set Date] <= '2012-02-01' -- Fiscal YTD

I have made the following changes:

• Moved the aggregation data into a subquery. I have found this to be both efficient and easy to manage with this sort of scenario
  
• Changed the BETWEEN clause to a dual <= and >=. In my experience, BETWEEN behaves inefficiently, and is best replaced with double boundary conditions. Don't ask me why, it just does in my experience. I haven't bothered looking into why SQL behaves in this fashion.
  
• Changed line 8 of your code, which is line 28 of my code, as it seemed to be wrong. I may be wrong in doing this, but I think your algorithm for calculating the average may have been off.

Now, in making these changes I haven't assumed that Rebate ID is unique, hence the GROUP BY clause at the end. If it is unique, no harm done, though I imagine there are multiple rows with the same Rebate ID.

Try the query out, see how it goes. This is the first attempt, and sometimes it takes a while to hit the nail on the head, so don't despair. If there are problems, post them back here, with an accompanying dataset result if possible.

Also, can I ask which columns were producing the "wrong" data in the first place? It's hard to tell from your original post.

[mb2000inc]

Wow. I never would have thought about doing it in the where clause.
I will give this a shot, and let you know how it turns out.
I'm kind of a novice at SQL. Being a programmer, I should know this, but have always had a SQL DBA to handle these things.

Anywho, the duplicate info. When I get back into work, I can message you a copy of the data and show you what is wrong.

Thanks again.

[baavgai]

There's a lot of crap in that group by. Also, as field name of [BUDGET FY'2012 or Accrued from FY'2010]??? Do you hate yourself?

Let's boil this down to the data you're using:

SELECT  
		FL.[Event Profile Contact],
		FL.[Rebate ID],
		FL.[Promo Name],
		FL.[Promo Start Date], 
		FL.[Promo End Date], 
		FL.[Promo Final RDMT Date],
		FL.[BUDGET FY'2012 or Accrued from FY'2010] as BudAmt1,
		FL.[Budget Amount] as BudAmt2
	FROM vw_FlashReport_Part1_Select FL
	WHERE FL.[Counter Set Date] BETWEEN '2011-07-01' AND '2012-02-01'

This should be the data you want, only ungrouped. If it is not, then you have a problem. Your group by and distinct are munging up a lot of the result, so start here.

Step back again and ask what exactly is a group by being used for? Doing a group by on budget amounts hardly makes sense... Is there any kind of unique key you can work from? Anything?


You should be aware that a null in any of those group by columns will probably blow you up. Since you're doing a group by [Rebate ID] and a count on it, it's probably meaningless in context.

Right, back to those cases. Use the boiled down version as a base.

SELECT  FL.[Event Profile Contact],
		FL.[Rebate ID],
		FL.[Promo Name],
		CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS 'Start Date',
		CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS 'End Date',
		CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS 'Final Date',
		COUNT(FL.[Rebate ID]) AS 'FY # Of RBTs',
		SUM(FL.[Total Rebate Amount])/(COUNT(FL.[Rebate ID])) AS 'FY AVG $ PER RBT',
		SUM(FL.[Total Rebate Amount]) AS 'FY TTL DOLLARS',
		FL.[Budget Amount],
		FL.[Budget Amount] - SUM(FL.[Total Rebate Amount]) AS 'Variance TO Budget',
		FL.[Budget Amount] AS 'Ending Budget FY2011'
	FROM (
		SELECT  
				[Event Profile Contact],[Rebate ID],[Promo Name],
				[Promo Start Date], [Promo End Date], [Promo Final RDMT Date],
				CASE
					WHEN [BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL([Budget Amount], 0.00)
					WHEN [BUDGET FY'2012 or Accrued from FY'2010] = [Budget Amount] THEN ISNULL([Budget Amount], 0.00)
					ELSE [BUDGET FY'2012 or Accrued from FY'2010]
				END AS 'Budget Amount',
			FROM vw_FlashReport_Part1_Select
			WHERE [Counter Set Date] BETWEEN '2011-07-01' AND '2012-02-01'
		) FL
	Group By FL.[Event Profile Contact], FL.[Rebate ID], FL.[Promo Name], FL.[Budget Amount],
		FL.[Promo Start Date], FL.[Promo End Date], FL.[Promo Final RDMT Date]

Avoid distinct, it hides what's going on. If have to use distinct to have to output come out "right", there's probably a problem elsewhere.

Hope this helps.

[mb2000inc]

Let's start simple: [BUDGET FY'2012 or Accrued from FY'2010] is not mine, lol. This data all came from an excel spreadsheet.
Without distinct, I get 19 rows back. When I should only get 14.
With distinct, I'm getting 16 rows back......

This has to be grouped by the event profile contact field. This is the most important thing. Because we are showing how many of which rebate per person for the fiscal year (which started on 7/1/11)

The null values are no longer an issue. I took those out late last night, and started playing with it more. But my end results are still the same.

Let me have a look at both of your items and see what's what.
I will post back any findings... And if either of you want to see the data I am pulling, I will send you a message with an attachment

[mb2000inc]

@e_i_pi
First Error: (red squiggley line under "AS RBTAggregate")

Quote

No Column was specified for column 3 of 'RBTAggregate'

Tried to execute it anyway and got this error:

Quote

Column 'vw_FlashReport_Part1_Select.Event Profile Contact' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Apparently, it all has to be in a group by.

After adding the group by, I executed it, and These fields didn't show up in the results:

INNER JOIN (
	
	SELECT
		
	    InnerFL.[Rebate ID],
		
	    COUNT(InnerFL.[Rebate ID]) AS 'FY # Of RBTs',
		
	    SUM(InnerFL.[Total Rebate Amount])/COUNT(InnerFL.[Rebate ID]) AS 'FY AVG $ PER RBT',
		
	    SUM(InnerFL.[Total Rebate Amount]) AS 'FY TTL DOLLARS'
	
	FROM vw_FlashReport_Part1_Select InnerFL
	
	GROUP BY InnerFL.[Rebate ID]
) AS RBTAggregate ON RBTAggregate.[Rebate ID] = FL.[Rebate ID]

AND the Budget amount is still selecting duplicate values.
With 16 records returned...
************

@baavgai
Looking at yours a little more closely...

SELECT  FL.[Event Profile Contact],
		FL.[Rebate ID],
		FL.[Promo Name],
		CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS 'Start Date',
		CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS 'End Date',
		CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS 'Final Date',
		COUNT(FL.[Rebate ID]) AS 'FY # Of RBTs',
		SUM(FL.[Total Rebate Amount])/(COUNT(FL.[Rebate ID])) AS 'FY AVG $ PER RBT',
		SUM(FL.[Total Rebate Amount]) AS 'FY TTL DOLLARS',
		FL.[Budget Amount],
		FL.[Budget Amount] - SUM(FL.[Total Rebate Amount]) AS 'Variance TO Budget',
		FL.[Budget Amount] AS 'Ending Budget FY2011'
	FROM (
		SELECT  
				[Event Profile Contact],[Rebate ID],[Promo Name],
				[Promo Start Date], [Promo End Date], [Promo Final RDMT Date],
				CASE
					WHEN [BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL([Budget Amount], 0.00)
					WHEN [BUDGET FY'2012 or Accrued from FY'2010] = [Budget Amount] THEN ISNULL([Budget Amount], 0.00)
					ELSE [BUDGET FY'2012 or Accrued from FY'2010]
				END AS 'Budget Amount',
			FROM vw_FlashReport_Part1_Select
			WHERE [Counter Set Date] BETWEEN '2011-07-01' AND '2012-02-01'
		) FL
	Group By FL.[Event Profile Contact], FL.[Rebate ID], FL.[Promo Name], FL.[Budget Amount],
		FL.[Promo Start Date], FL.[Promo End Date], FL.[Promo Final RDMT Date]

The first thing I get is the squiggley red line under "FL" after your From SELECT:

Quote

No column was specified for column 7 fo 'FL'

I decided to run it anyway and got this:

Quote

Incorrect syntax near the keyword 'FROM'.

Found you had an extra comma in your sub select's FROM

Tried to run it again and got:

Quote

Invalid column name 'Total Rebate Amount'.

***********************************************

Now, I'm even more confused than before.

This post has been edited by mb2000inc: 16 February 2012 - 07:54 AM

[baavgai]

I see a bug in what I offered:

	END AS 'Budget Amount',
FROM vw_FlashReport_Part1_Select

See it? Ditch that comma! Then run the sub query by itself. If that runs, that you've narrowed down that any errors would reside elsewhere.

Debugging is a requirement, even in SQL. I can accept if you tell me what I gave you is crap, but you need to be able to tell me why it's crap. Not just tell you get red squigglies. What tool are you using that offers that notation, btw?

Edit, see you found that one. Invalid column? Means it should have been in the subquery and wasn't. Add it in.

[mb2000inc]

baavgai, on 16 February 2012 - 10:03 AM, said:

I see a bug in what I offered:

	END AS 'Budget Amount',
FROM vw_FlashReport_Part1_Select

See it? Ditch that comma! Then run the sub query by itself. If that runs, that you've narrowed down that any errors would reside elsewhere.

Debugging is a requirement, even in SQL. I can accept if you tell me what I gave you is crap, but you need to be able to tell me why it's crap. Not just tell you get red squigglies. What tool are you using that offers that notation, btw?

Edit, see you found that one. Invalid column? Means it should have been in the subquery and wasn't. Add it in.

Cool! I will try it when I get back to my desk!
Thanks :-)

[mb2000inc]

BTW, I'm running SQL SERVER 2008.
(Note: I didn't tell you it was crap, lol. Any help is better than no help.:-) )
The reason, I'm in here, is because I need lots o' help.
I used to have a SQL DBA to rely on, but I no longer have one.
I realize that, as a programmer, I should know this... but it's been YEARS since I've had to do any SQL stuff.

Anywho... I'm sending both of you PM's to show you what I'm trying to do, and what the data looks like when I do it.

I will also try to run your sub select, here, shortly - with your helpful advice.

[mb2000inc]

Ok, I ran the sub select. It worked.
I added the one missing field, ran the full query.
It ran, but I still get duplicate values.

Check your message in box to see what I'm talking about.
:-)

Thanks.

[mb2000inc]

AH-HA! I Figured out WHERE I amn getting duplicate values from!!!!
I went back to the original query that drives the view that I'm pulling from and there's a column called "Counter Set Date".

There are A LOT of values in that column for each Rebate ID... some are the same, and others are different.

Ok, now that I've narrowed it down to that ONE column...

I know that I'm not selecting it anywhere in the new query which is pulling from the view.. but in my where clause, I'm using it to put constraints on the data.

SO! How can I get it (in the where clause?) to not look at that column... or tell the query that if the counter set date for a given Rebate ID has more than one value, to not look at it or take it into consideration when pulling the data?

[e_i_pi]

There were some small errors in the query I posted. For the record, here is a fixed version"

Spoiler

SELECT
	FL.[Event Profile Contact],
	FL.[Rebate ID],
	FL.[Promo Name],
	CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS [Start Date],
	CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS [End Date],
	CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS [Final Date],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS [Budget Amount],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN ISNULL(FL.[Budget Amount], 0.00) - SUM(FL.[Total Rebate Amount])
		ELSE ISNULL(FL.[BUDGET FY'2012 or Accrued from FY'2010], 0.00) - RBTAggregate.[FY TTL DOLLARS]
	END AS [Variance TO Budget],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS [Ending Budget FY2011]
FROM vw_FlashReport_Part1_Select FL
INNER JOIN (
	SELECT
		InnerFL.[Rebate ID],
		COUNT(InnerFL.[Rebate ID]) AS [FY # Of RBTs],
		SUM(InnerFL.[Total Rebate Amount])/COUNT(InnerFL.[Rebate ID]) AS [FY AVG $ PER RBT],
		SUM(InnerFL.[Total Rebate Amount]) AS [FY TTL DOLLARS]
	FROM vw_FlashReport_Part1_Select InnerFL
	GROUP BY InnerFL.[Rebate ID]
) AS RBTAggregate ON RBTAggregate.[Rebate ID] = FL.[Rebate ID]
WHERE FL.[Counter Set Date] >= '2011-07-01'
AND FL.[Counter Set Date] <= '2012-02-01' -- Fiscal YTD

Regardless of that, I would be asking why there are multiple rows in the view that have different Counter Set Dates. Are the rows identical in every way except for that one column? If so then I would use SELECT DISTINCT, but I would want to know why you have essentially duplicate rows in that view.

[mb2000inc]

The original view that drives this "madness" is derived from several tables (and some imported tables from Excel). The Counter Set Date comes from other applications and is used to pull the appropriate data into the applications.... I guess.

A little history of this madness: Essentially what we are trying to do, is automate a report that is being manually generated in Excel.

In this workbook, there are no duplicates... the data that is pulled and exported to excel from another application is 1) dependent on the counter set date for whatever reason... and 2) is de-duped somehow in SQL before it gets pulled into the application (I can't locate the original Stored Proc to see it). So, I'm re-inventing the wheel, so to speak.

I sent you a PM with the data and all queries that drive this - including the original view and a PDF version of the output.

Also, I've been toying with MIN/MAX dates for this and it's whittled my results, Happily down to 12 good rows of data (but, yes, there are still dupes).

I've also been toying with something like this:

IF (SELECT COUNT([Rebate ID) FROM #flTempTable WHERE [Rebate ID] = ft.[Rebate ID]) > 1
		BEGIN SELECT TOP 1 [Rebate ID] FROM #flTempTable WHERE [Rebate ID] = ft.[Rebate ID]
		END AS 'Rebate ID',

And this:

WHERE FL.[Counter Set Date] = 
(     
      select MIN([Counter Set Date]) 
      from vw_FlashReport_Part1_Select 
            where [Event Profile Contact] = FL.[Event Profile Contact]
            group by [Event Profile Contact]
)

I'm still confused.

Any other thoughts would be helpful. I will be signing off for the evening and will check in tomorrow morning.

Thanks to both of you for your help, thus far.

[e_i_pi]

You need a compound key for your view. A compound key is a unique identifier, that you can use to narrow down a selection to one particular row. I'm assuming in this case, a compound key can be comprised of the Rebate ID and the Counter Set Date. To cater for the fact that there may be the same Counter Set Date more than once for the same Rebate ID, I've added a DISTINCT clause at the top of the query.

I will warn you that your view may be poorly constructed, given that I've had to adopt this approach. In my experience, views are good but can lead to a false sense of security, as you can encounter problems to do with unique identification.

Here's an amended query, see if this works:

SELECT DISTINCT
	FL.[Event Profile Contact],
	FL.[Rebate ID],
	FL.[Promo Name],
	CONVERT(VARCHAR (10), FL.[Promo Start Date], 111) AS [Start Date],
	CONVERT(VARCHAR (10), FL.[Promo End Date], 111) AS [End Date],
	CONVERT(VARCHAR (10), FL.[Promo Final RDMT Date], 111) AS [Final Date],
	RBTAggregate.[Counter Set Date],
	RBTAggregate.[FY No Of RBTs],
	RBTAggregate.[FY Avg PER RBT],
	RBTAggregate.[FY TTL DOLLARS],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS [Budget Amount],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN ISNULL(FL.[Budget Amount], 0.00) - RBTAggregate.[FY TTL DOLLARS]
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN ISNULL(FL.[Budget Amount], 0.00) - RBTAggregate.[FY TTL DOLLARS]
		ELSE ISNULL(FL.[BUDGET FY'2012 or Accrued from FY'2010], 0.00) - RBTAggregate.[FY TTL DOLLARS]
	END AS [Variance TO Budget],
	CASE
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] IS NULL THEN FL.[Budget Amount] 
		WHEN FL.[BUDGET FY'2012 or Accrued from FY'2010] = FL.[Budget Amount] THEN FL.[Budget Amount]
		ELSE FL.[BUDGET FY'2012 or Accrued from FY'2010]
	END AS [Ending Budget FY2011]
FROM vw_FlashReport_Part1_Select FL
INNER JOIN (
	SELECT
		InnerFL.[Rebate ID],
		MIN(InnerFL.[Counter Set Date]) AS [Counter Set Date],
		COUNT(InnerFL.[Rebate ID]) AS [FY No Of RBTs],
		SUM(InnerFL.[Total Rebate Amount])/COUNT(InnerFL.[Rebate ID]) AS [FY Avg PER RBT],
		SUM(InnerFL.[Total Rebate Amount]) AS [FY TTL DOLLARS]
	FROM vw_FlashReport_Part1_Select InnerFL
	WHERE InnerFL.[Counter Set Date] >= '2007-07-01'
	AND InnerFL.[Counter Set Date] <= '2012-02-01'
	GROUP BY InnerFL.[Rebate ID]
) AS RBTAggregate
	ON RBTAggregate.[Rebate ID] = FL.[Rebate ID]
	AND RBTAggregate.[Counter Set Date] = FL.[Counter Set Date]

As baavgai pointed out before, resorting to using DISTINCT is not good in cases like this. Generally I would only use DISTINCT when trying to find out, say, all the different PersonTypeIDs from a Persons table, to populate a dropdown filter list for example.

[mb2000inc]

Compound Key, you say?
I will take a look and let you know what I come up with.

Thanks a bunch!

(PS - by compound key, do you mean Primary key?)

This post has been edited by mb2000inc: 17 February 2012 - 07:15 AM