Displaying monthwise sales details


I have salesorder table in which all the sales orders are stored. here is the design

Order_id   Order_qty   dealer_id   order_price  order_date

how do I display sum(order_price) based on the two dates monthwise.

What I mean is say i have two dates 01/01/2005 and  04/01/2005 it should fetch records in the below mentioned manner

Dealer_id     Jan'05     Feb'05    Mar'05    Apr'05
1                  1000       2000      40000      50000
2                  3000       1000      2000       10000

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is called crosstab process, for which you can use the SQL Server 2005 TRANSFORM + PIVOT syntax, or some procedure like this in general:

if you want it for 1 year (12 months), something like this:

select dealer_id
, sum ( case when datepart(month, order_date) = 1 then order_price else 0 end ) as Jan05
, sum ( case when datepart(month, order_date) = 2 then order_price else 0 end ) as Fev05
, sum ( case when datepart(month, order_date) = 3 then order_price else 0 end ) as Mar05
FROM yourtable
where order_date >= convert(datetime, '2005-01-01', 120) and order_date < convert(datetime, '2006-01-01', 120)
group by dealer_id
sureshrainaAuthor Commented:
Thanks for your response.

BUT, based on the dates provided only that number of months should be displayed.
For example if 01/01/2005 and 06/01/2005 are provided only 6 months should be show not the 12 months and 0s from the seventh month.

One more example
say if the dates passed are 03/02/06 and 07/02/06  then only months March,April, may june, july should be shown.

Pls help
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you have to use the crosstab function (link is above), as this is a dynamic query (variable number of columns)
this is how you can create dynamic query

declare @startMonth as tinyint,
      @query as varchar(4000)
set @startMonth=  month('2006-03-0')
set @query ='select dealer_id '
while @startmonth <= month('2006-07-01')

      select @query  = @query + ', sum ( case when datepart(month, order_date) =' +cast(startmonth as varchar(2))+ ' then order_price else 0 end ) as ' +
          case  @startMonth
            when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr'
            when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug'
            when 9 then 'Sep' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' end
set @startmonth =@startmonth + 1

set @query = @query +' FROM yourtable
where order_date >= convert(datetime, ''2006-03-02'', 120) and order_date < convert(datetime, ''2006-07-01'', 120)
group by dealer_id'

exec ('@query')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sureshrainaAuthor Commented:
Hello imran_fast,

there is an issue with this. though we are declaring @query it is again giving me an error saying @query must be declared.

pls help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.