Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get total of months, like Year to Day in cross tab query? Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 8:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:27 AM
Points: 306, Visits: 580
I used code below to create cross tab query. It works fine.
How to add code to get total of months, like Year to Day?

SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,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
Post #1469546
Posted Tuesday, July 2, 2013 10:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 321, Visits: 577
Any chance you could provide us with some sample data so we can test the query that you've written?

SQL SERVER Central Forum Etiquette
Post #1469666
Posted Tuesday, July 2, 2013 10:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:27 AM
Points: 306, Visits: 580
I found the code here but no test data only screen shot.

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
Post #1469669
Posted Tuesday, July 2, 2013 11:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 321, Visits: 577
This is quick and dirty, but since the number of months in a year isn't going to change any time soon you can do this:

;With Invoice AS
(
SELECT '20130101' [InvoiceDate], 100 [InvoiceAmount] UNION ALL
SELECT '20120102', 101 UNION ALL
SELECT '20120202', 140 UNION ALL
SELECT '20120302', 130 UNION ALL
SELECT '20120402', 120 UNION ALL
SELECT '20120502', 110 UNION ALL
SELECT '20130102', 101 UNION ALL
SELECT '20130202', 140 UNION ALL
SELECT '20130302', 130 UNION ALL
SELECT '20130402', 120 UNION ALL
SELECT '20130502', 110 UNION ALL
SELECT '20130502', 111
)


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]



SQL SERVER Central Forum Etiquette
Post #1469671
Posted Tuesday, July 2, 2013 12:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:27 AM
Points: 306, Visits: 580
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]

)
Post #1469708
Posted Tuesday, July 2, 2013 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:47 PM
Points: 13,246, Visits: 12,085
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 

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]



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469712
Posted Tuesday, July 2, 2013 12:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:27 AM
Points: 306, Visits: 580
Thank you very much. Now all is working.
The key is "You need to give you table an alias"
Post #1469716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse