Is there anyway to get a query like this to work? 1

[hoggle]

here is the query:
===========================================================
select
@NextWeek01=IsNull(Sum(Case When Year(InvoiceDate)=(select (Year(frcstDate)-1) from SISLogic..SIS_MRPDates where frcstIndex = @CrossIndex) Then ExtendedAmount Else 0 End),0)
from SISReports..vu_SISInvoices
where Item = @ItemNum
===========================================================

I keep getting the error:
Error 130: Cannot perform an aggregate function on an expression containing an aggregate or subquery.

I Understand why the error is there, I just can't really think of another way to do this query.

Basically what I want is to get a sum sales (ExtendedAmount)from my Inovices where the year (InvoiceDate) is a year earlier than the Forecasted year (frcstDate), but to get the Forecasted year I have to go to a lookup table (SIS_MRPDates).

I cannot change the layout of the tables, so I'll have to grab this information through a query...The above query should give me the required results, except it won't work

Thanks for any help

 

[cehepker]

Hello,

Could you do two select statements? In the first you could pull the forecasted date - 1 into a variable, and then use that variable in the second select.

Carla

Documentation: A Shaft of light into a Coded world

 

[zhavic]

just for fun, try this:

SELECT ISNULL( SUM( CASE WHEN YEAR( vu_SISInvoices.InvoiceDate ) = YEAR( SIS_MRPDates.frcstDate ) - 1 THEN vu_SISInvoices.ExtendedAmount ELSE 0 END ), 0 )
	FROM SISReports..vu_SISInvoices
		LEFT JOIN SISLogic..SIS_MRPDates ON SIS_MRPDates.frcstIndex =  @CrossIndex
	WHERE vu_SISInvoices.Item = @ItemNum

[\code]

    Zhavic

---------------------------------------------------------------
[i]In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.[/i]

 

[billchris]

Try this:

  select
    @NextWeek01 = IsNull(Sum(extAmt.ExtendedAmount), 0)
  from
    SISLogic..SIS_MRPDates dat
    inner join
    (  
      select
        year(InvoiceDate) as InvoiceYear,
        ExtendedAmount
      from
        SISReports..vu_SISInvoices inv
      where
        Item = @ItemNum
    ) extAmt on extAmt.InvoiceYear = year(dat.frcstDate)-1
    where
      dat.frcstIndex = @CrossIndex

I have no way to test it, but it should give you the result you are looking for.

“I have always found that plans are useless, but planning is indispensable.” --Dwight Eisenhower

 

[hoggle]

That works billChris!...I knew there had to be a way
Thanks