RE: How to get total of months, like Year to Day in cross tab query?

SSC Guru

Points: 286573

July 2, 2013 at 12:34 pm

adonetok (7/2/2013)
It is working great.
One more question, I want to insert the result into a temp table but code below did not work.
Something wrong with my code?
select * into #myinvoice from ( //your code below
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
)

You need to give you table an alias OR just do this as an insert.

select * into #myinvoice from ( //your code below

SELECT *,ISNULL(jan,0) +

ISNULL(feb,0) +

ISNULL(mar,0) +

ISNULL(apr,0) +

ISNULL(may,0) +

ISNULL(jun,0) +

ISNULL(jul,0) +

ISNULL(aug,0) +

ISNULL(sep,0) +

ISNULL(oct,0) +

ISNULL(nov,0) +

ISNULL(dec,0) [YearToDate]

FROM (

SELECT

year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],

InvoiceAmount as Amount

FROM Invoice

) as s

PIVOT

(

SUM(Amount)

FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)

)AS [pivot]

) as MYALIAS

Or the simpler version....

INSERT into #myinvoice