TSQL - proper query

Hi Experts,

I have this bit of code:
Declare
@Date2 As Date = '12/31/2015'
SELECT 
		Item.[Description] + ' in print' AS Description,
		YEAR((Item.Expiration)) AS Year,
		2 AS SRT1, 1 AS SRT2, Item.Code2 AS SRT3
		,ShipToContact
From v_NAsInvoiceItem Item INNER JOIN
				tblNAsProgramCodes ON Item.ProgramCodeID = tblNAsProgramCodes.ProgramCode
	WHERE    FormatCode = 'Subs' AND Item.Code1 = 'JOURNAL' And Year(Item.Expiration) = Year(@Date2)
		And Not Exists
			( Select 1 From tblNAsAffiliations mem Where Item.ShipToContact = Mem.Contact1 And  Mem.Date2 >= GetDate())
		And Exists
			(Select 1 From v_NAsInvoiceItem Inv Where Item.ShipToContact = Inv.ShiptoContact And Max(Inv.Expiration) = Year(@Date2) Group By Expiration)

Open in new window


But I am getting an error on:
			(Select 1 From v_NAsInvoiceItem Inv Where Item.ShipToContact = Inv.ShiptoContact And Max(Inv.Expiration) = Year(@Date2) Group By Expiration)

Open in new window


Msg 147, Level 15, State 1, Line 14
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

please help and thanks
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Eyeballeth the below

Select 1
From v_NAsInvoiceItem Inv
Where Item.ShipToContact = Inv.ShiptoContact And Max(Inv.Expiration) = Year(@Date2)
Group By Expiration

You can't use an aggregate such as MAX, SUM, COUNT, etc. in a WHERE clause, hence the error.
That needs to go in the HAVING clause, which allows filtering based on aggregates.

Also, what's the purpose for both GROUP BY and MAX on column Expiration?

For more info check out my article SQL Server GROUP BY Solutions
0
typetoitCommented:
Your query has multiple problems.  

First in the select you need: Select 1 Max(Inv.Expiration) plus another field to group by.  
Second Group by Expiration does not make sense when you are using that field in the max calculation.  
Third in the where clause there is the "item" table that has not be declared in this query.  
Fourth all you need to do is move the Max(Inv.Expiration) = Year(@Date2) into a Having statement which goes after the Group by statement.
0
Amour22015Author Commented:
Ok,

So now I have this:
Declare
@Date2 AS Date = '12/31/2015'
SELECT 
		Item.[Description] + ' in print' AS Description,
		YEAR((Item.Expiration)) AS Year,
		2 AS SRT1, 1 AS SRT2, Item.Code2 AS SRT3
		,ShipToContact
From v_NAsInvoiceItem Item INNER JOIN
				tblNAsProgramCodes ON Item.ProgramCodeID = tblNAsProgramCodes.ProgramCode
	WHERE    FormatCode = 'Subs' AND Item.Code1 = 'JOURNAL' --And Year(Item.Expiration) = Year(@Date2)
		--And Not Exists
		--	( Select 1 From tblNAsAffiliations mem Where Item.ShipToContact = Mem.Contact1 And  Mem.Date2 >= GetDate())
		And Exists
			(Select Max([Expiration]), ShiptoContact From v_NAsInvoiceItem Inv Where Item.ShipToContact = Inv.ShiptoContact Group By ShiptoContact Having Max(Year(Inv.Expiration)) = Year(@Date2) )
			Order By ShipToContact

Open in new window


But am getting this:
Year	SRT1	SRT2	SRT3	ShipToContact
1994	   2	           1	           IS	           1000
1995	2	1	IS	1000
1996	2	1	IS	1000
1997	2	1	IS	1000
1998	2	1	IS	1000
1999	2	1	IS	1000
2007	2	1	CS	1000
2007	2	1	IS	1000
2008	2	1	CS	1000
2008	2	1	IS	1000
2009	2	1	CS	1000
2009	2	1	IS	1000
2006	2	1	CS	1000

Open in new window


When the year should only be 2015

Please help and thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>--And Year(Item.Expiration) = Year(@Date2)
That's likely because you commented out ( -- ) the part of the WHERE clause that filters by year.
0
Scott PletcherSenior DBACommented:
Declare
@Date2 AS Date = '12/31/2015'
SELECT
            Item.[Description] + ' in print' AS Description,
            YEAR((Item.Expiration)) AS Year,
            2 AS SRT1, 1 AS SRT2, Item.Code2 AS SRT3
            ,ShipToContact
From v_NAsInvoiceItem Item INNER JOIN
                        tblNAsProgramCodes ON Item.ProgramCodeID = tblNAsProgramCodes.ProgramCode
      WHERE    FormatCode = 'Subs' AND
               Item.Code1 = 'JOURNAL' AND
               Item.Expiration >= DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date2), 0) AND
               Item.Expiration < DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date2) + 1, 0)
            --And Not Exists
            --      ( Select 1 From tblNAsAffiliations mem Where Item.ShipToContact = Mem.Contact1 And  Mem.Date2 >= GetDate())
            And Exists
                  (Select Max([Expiration]), ShiptoContact
                   From v_NAsInvoiceItem Inv
                   Where Item.ShipToContact = Inv.ShiptoContact And
                         Inv.Expiration >= DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date2), 0) AND
                         Inv.Expiration < DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date2) + 1, 0)
                   Group By ShiptoContact
                   )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
typetoitCommented:
What are you trying to accomplish with this query?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Amour22015, do you still need help with this query?
0
Amour22015Author Commented:
Thanks great..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.