asked on
SELECT *
FROM
(
SELECT
SalesLineItems.[Item Class Code],
SalesLineItems.[Item Number]
,SalesLineItems.[Customer Name]
,(SELECT CASE ISNULL ([Tax Exempt 1 from Customer Master], '')
WHEN ''
THEN NULL
ELSE CAST ([Tax Exempt 1 from Customer Master] AS DECIMAL (10,2))
END) as [Dispenser Cost]
,SalesLineItems.[GL Posting Date]
,SalesLineItems.[SOP Number]
,CASE WHEN LEFT (SalesLineItems.[SOP Number],2)='CR' THEN sum(SalesLineItems.[Extended Price]*-1) ELSE sum(SalesLineItems.[Extended Price])END as [Extended Price]
,datename(month,SalesLineItems.[GL Posting Date]) as FMonth
,datename(year,SalesLineItems.[GL Posting Date]) as FYear
,CASE WHEN LEFT (SalesLineItems.[SOP Number],2)='CR' THEN sum(SalesLineItems.[QTY]*-1) ELSE sum(SalesLineItems.[QTY])END as [QTY]
FROM
SalesLineItems
WHERE
SalesLineItems.[SOP Type] IN ('Invoice','Return')
AND SalesLineItems.[Void Status] <> 'Voided'
AND SalesLineItems.[Document Status] = N'Posted'
AND SalesLineItems.[Extended Price] != 0
AND SalesLineItems.[Item Class Code] IN (@ItemClassID)
AND datename(year,SalesLineItems.[GL Posting Date]) = @FYear
GROUP BY
SalesLineItems.[Item Class Code]
,SalesLineItems.[Item Number]
,SalesLineItems.[Customer Name]
,SalesLineItems.[Tax Exempt 1 from Customer Master]
,SalesLineItems.[GL Posting Date]
,SalesLineItems.[SOP Number]
) as DerivedTable
PIVOT (sum([Extended Price]) for FMonth in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))
AS NEWMONTH
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY