Nu giver det hele lidt mere mening.
Du kan ikke både assign en variable og select den.
Her er det tilrettet SQL:
Declare @LedgerAccount varchar(8)
Declare @FiscalYear int
Declare @FiscalMonth int
SELECT
[OrderID]
,[CustomerID]
,[CustomerName]
,[CustomerCountry]
,[OfficeID]
,[OfficeName]
,[PartnerID]
,[PartnerName]
,[OrderID]
,[Expiration]
,[LicenseID]
,SUM([RetailTotal]) as RetailTotal
,SUM([PartnerTotal]) as PartnerTotal
,SUM([OfficeTotal]) as Officetotal
,[InvoiceDate]
,@LedgerAccount = (CASE WHEN LedgerAccount IS NULL THEN
(CASE
WHEN [OrderLineType] = 'product' THEN '1010'
WHEN [OrderLineType] = 'maintenance' AND [Expiration] > [InvoiceDate] + 540 THEN 'LTM1020'
WHEN [OrderLineType] = 'upgrade' AND [Expiration] IS NOT NULL THEN '1020'
WHEN [OrderLineType] = 'upgrade' THEN '1010'
WHEN [OrderLineType] = 'maintenance' THEN '1020'
ELSE [OrderLineType]
END)
ELSE
LedgerAccount
END
)
,(CASE WHEN
OrderId = LicenseId
AND LedgerAccount = 1020
AND SUM([RetailTotal]) > 0
AND (SELECT SUM(RetailTotal) FROM [LicenseAnalysis].[dbo].[OrderDetails] as P2 WHERE P2.OrderId = P1.OrderId AND P2.LedgerAccount = 1010) > 0
THEN 1
ELSE 0
END) as FirstYearMaintenance
,(CASE WHEN
OrderId = LicenseId
AND LedgerAccount = 1020
AND SUM([RetailTotal]) > 0
AND (SELECT SUM(RetailTotal) FROM [LicenseAnalysis].[dbo].[OrderDetails] as P2 WHERE P2.OrderId = P1.OrderId AND P2.LedgerAccount = 1010) > 0
THEN '1010'
Else @LedgerAccount END) as calcLedger
,(CASE WHEN InvoiceDate IS NOT NULL THEN YEAR(InvoiceDate) ELSE 0 END) as Year
,(CASE WHEN InvoiceDate IS NOT NULL THEN MONTH(InvoiceDate) ELSE 0 END) as Month
,(CASE WHEN InvoiceDate IS NOT NULL THEN YEAR(InvoiceDate)-1+FLOOR(MONTH(InvoiceDate)/7) ELSE 0 END) as FiscalYear
,(CASE WHEN InvoiceDate IS NOT NULL THEN FLOOR(((Month(InvoiceDate)+5) % 12)/3+1) ELSE 0 END) as FiscalQuarter
,(CASE WHEN InvoiceDate IS NOT NULL THEN (((Month(InvoiceDate)+5) % 12)+1) ELSE 0 END) as FiscalMonth
FROM [LicenseAnalysis].[dbo].[OrderDetails] as P1
WHERE [InvoiceDate] > '20120630'
GROUP BY [OrderID]
,[LedgerAccount]
,[OrderLineType]
,[Expiration]
,[CustomerID]
,[CustomerName]
,[CustomerCountry]
,[OfficeID]
,[OfficeName]
,[PartnerID]
,[PartnerName]
,[OrderID]
,[Expiration]
,[LicenseID]
,[InvoiceDate]
Udover det ... så kommer den fejl jeg snakker om nu:
Msg 141, Level 15, State 1, Line 5
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Ergo, noget der returnere mere end 1 row kan ikke bruges hvis du laver en assignment i din SQL query.
Du kan heller ikke bruger en variable i samme query hvor du har assigned den.
Du kan måske lave en SQL Function(
http://technet.microsoft.com/en-us/library/aa214363(v=sql.80).aspx), hvor at genbruge samme logik til at regne den ud med.
