I have this Query:select WS_CONTRACTORCUSTOMER, SALESLINE.SALESSTATUS, SUM(CASE WHEN SALESLINE.SALESSTATUS =1 THEN REMAINSALESPHYSICAL ELSE REMAINSALESFINANCIAL END * SALESLINE.SALESPRICE) as totalfrom SALESLINE left outer join SALESTABLE ON SALESLINE.SALESID = SALESTABLE.SALESID and SALESTABLE.DATAAREAID ='ES75' left outer join INVENTTABLE on SALESLINE.ITEMID = INVENTTABLE.ITEMID and INVENTTABLE.DATAAREAID ='ES75'where SALESLINE.DATAAREAID ='ES75' and (SALESLINE.SALESSTATUS =1 OR SALESLINE.SALESSTATUS =2) and (SALESLINE.SALESTYPE =3 OR SALESLINE.SALESTYPE = 4) and (SalesTable.ReturnStatus = 3 OR ISNULL(SalesTable.ReturnStatus,0) = 0) and WK_SalesVariant <> 5 and (ItemGroupID LIKE 'I%' OR ItemGroupID = 'SV-CERT' OR ItemGroupID = 'SV-SERV') and SALESLINE.SHIPPINGDATECONFIRMED <= GETDATE()GROUP BY WS_CONTRACTORCUSTOMER, SALESLINE.SALESSTATUSORDER BY WS_CONTRACTORCUSTOMER, SALESLINE.SALESSTATUS
Now I need to substitute the reference-date GETDATE() with the date returned by this query: select TOP 1 Invoicedate, COUNT (INVOICEDATE) as cnt from (select TOP 600 INVOICEDATE from CUSTINVOICETRANS where DATAAREAID = 'ES75' and Invoicedate <= '2013-07-29' ORDER BY INVOICEDATE desc)aGROUP BY INVOICEDATEORDER BY cnt desc
If I do it by just wrapping the query into brackets I get this error message: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.. I was trying that but don't succeed, cause I never used EXISTS in such a context.Martin