January 11, 2012 at 12:05 pm
I have a query with a series of nested queries designed to give monthly totals.
Select ItemName
,(select sum(linetotal) from Tab1 where Year(docdate) = '2010' and Month(docdate) = '11') as '11/2010'
,(select sum(linetotal) from Tab1 where Year(docdate) = '2010' and Month(docdate) = '12') as '12/2010'
,(select sum(linetotal) from Tab1 where Year(docdate) = '2011' and Month(docdate) = '1') as '1/2011'
,(select sum(linetotal) from Tab1 where Year(docdate) = '2011' and Month(docdate) = '2') as '2/2011'
from Tab1
Is there a way to simplify such a query so that it would iterate through the months, etc. Secondly, is there a way to dynamically name the columns?
Randy Davis
January 11, 2012 at 12:23 pm
In order to dynamically name the columns, you would need to use dynamic sql. Search BOL or this site for more details on that.
Otherwise, this gets what you want. **WHERE clause is optional of course
SELECT
ItemName
,YEAR(docdate) AS docYear
,MONTH(docdate) AS docMonth
,SUM(linetotal) AS lineTotal
FROM Tab1
WHERE
(YEAR(docdate) = 2011 AND MONTH(docdate) IN (11,12))
OR
(YEAR(docdate) = 2010 AND MONTH(docdate) IN (1,2))
GROUP BY
ItemName
,YEAR(docdate)
,MONTH(docdate)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 11, 2012 at 12:27 pm
Jason Selburg (1/11/2012)
In order to dynamically name the columns, you would need to use dynamic sql. Search BOL or this site for more details on that.Otherwise, this gets what you want. **WHERE clause is optional of course
SELECT
ItemName
,YEAR(docdate) AS docYear
,MONTH(docdate) AS docMonth
,SUM(linetotal) AS lineTotal
FROM Tab1
WHERE
(YEAR(docdate) = 2011 AND MONTH(docdate) IN (11,12))
OR
(YEAR(docdate) = 2010 AND MONTH(docdate) IN (1,2))
GROUP BY
ItemName
,YEAR(docdate)
,MONTH(docdate)
This is great to insert into a temp table, then the OP needs to crosstab it. So, once you have the aggregates you can crosstab it without all of those individual queries.
Jared
CE - Microsoft
January 11, 2012 at 12:33 pm
SQLKnowItAll (1/11/2012)
Jason Selburg (1/11/2012)
In order to dynamically name the columns, you would need to use dynamic sql. Search BOL or this site for more details on that.Otherwise, this gets what you want. **WHERE clause is optional of course
SELECT
ItemName
,YEAR(docdate) AS docYear
,MONTH(docdate) AS docMonth
,SUM(linetotal) AS lineTotal
FROM Tab1
WHERE
(YEAR(docdate) = 2011 AND MONTH(docdate) IN (11,12))
OR
(YEAR(docdate) = 2010 AND MONTH(docdate) IN (1,2))
GROUP BY
ItemName
,YEAR(docdate)
,MONTH(docdate)
This is great to insert into a temp table, then the OP needs to crosstab it. So, once you have the aggregates you can crosstab it without all of those individual queries.
OOPS, I missed that part. (crosstabing/pivoting into one row) .... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 11, 2012 at 12:47 pm
There's actually a recent thread with some discussion on this: http://www.sqlservercentral.com/Forums/Topic1233529-391-1.aspx
Jared
CE - Microsoft
January 11, 2012 at 1:24 pm
Maybe this can be an option too
Declare @SeriesStartDate datetime
Declare @SeriesEndDate datetime
Select @SeriesStartDate = '2010-11-01'
, @SeriesEndDate = DATEADD(mm, 4, @SeriesStartDate)
Declare @DynamicPivot varchar(1000)
declare @Separator char(1)
Select @DynamicPivot = ''
, @Separator = ''
Select @DynamicPivot=@DynamicPivot + @Separator + '[' + CONVERT(char(10), TrxMonth,121) + ']'
, @Separator = ','
from ( Select DATEADD(mm, DATEDIFF(mm, 0, docdate), 0) as TrxMonth
from #tab1
where docdate >= @SeriesStartDate and docdate < @SeriesEndDate
group by DATEADD(mm, DATEDIFF(mm, 0, docdate), 0)
) as SourceInfo
order by trxmonth
Declare @MyPivotSQL nvarchar(4000)
Select @MyPivotSQL = 'Select *
from (
Select itemname, linetotal, DATEADD(mm, DATEDIFF(mm, 0, docdate), 0) as TrxMonth
from #tab1
where docdate >= @SeriesStartDate and docdate < @SeriesEndDate
) as SourceInfo
Pivot
( SUM( linetotal ) FOR TrxMonth IN ( ' + @DynamicPivot + ') )
AS PivotT
order by itemname;'
print @MyPivotSQL
exec sp_executesql @command1=@MyPivotSQL , @params = N'@SeriesStartDate datetime, @SeriesEndDate datetime ', @SeriesStartDate=@SeriesStartDate,@SeriesEndDate=@SeriesEndDate
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2012 at 4:10 pm
Jason, thanks for the response. However, I'd already thought of this, but I need the end result to be horizontal, not vertical. I realize that I can transpose this in Excel, but at this point, it is an academic question. Is it possible to use some sort of iteration (or other process) to produce a query that has identifying leading columns (name, id, etc), followed by mulitple columns of monthly values?
January 11, 2012 at 7:07 pm
Johan's post should get you pretty much what you are asking for.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 12, 2012 at 1:01 am
As Jason stated, the query I provided should cover your quest.
Oh indeed. I forgot to mention this:
Test it, Test IT !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 12, 2012 at 11:00 am
Jason and Johan--
Thank you for all the information. Johan's solution looks rather advanced compared to my intermediate coding skills, so it will probably take me a while to understand it. But it was just the information that I was looking for. Thank you for giving you time and talent to this forum.
Randy
January 13, 2012 at 5:56 am
HTH
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply