help me optimise this query !!!

[hitro456]

I have a huge database which contains certain categories of products and their sales figures.
I want to find
CATEGORYWISE sale per Month.
I have written a query for that.
but I had to write 12 queries for 12 months.
This is a sample Query for Month April I am Posting.

select  cm.name as April,  sum(sd.Amount) as categorywisesell  from categorymaster cm  
INNER JOIN LobAttributeValue lav
on cm.categoryid = lav.valueid
INNER JOIN AttributeMaster am
on am.attributeid = lav.attributeid
INNER JOIN salesdetail sd
on sd.productid = lav.productid
INNER JOIN salesmaster sm
on sd.VoucherNo = sm.VoucherNo
where cm.name = 'Blow Plast Ltd' and lav.attributeid <> 4 and sm.Date > '2009-04-01 00:00:00.000' and sm.Date < '2009-04-30 00:00:00.000'
group by cm.name

This query is just for April.

I want one query to print all months sales figures.

Any Idea how can I print Sales figures for all months in single query.
Don't tell me to write procedure bcoz its not feasible for my applicaiton.

[darkagn]

The DATEPART function might come in handy here. Not sure exactly how you could do it without writing a procedure, but take a look at the MSSQL documentation for the DATEPART function and see if you get any inspiration from it.

[padtes]

I don't know of printing all months in single row returned (like sale of april, may, june... for each cm.name) dynamically without stored proc.
1. Assuming you want month-year + cm.name + sum() you can group by month(sm.date), year(sm.date)
2. If there are fixed number of months (less likely), you can use subqueries for that:

select cm.name
, (select sum(sm.amount from...sm... where sm.joinCol = cm.join_col... and month(sm.date)=4) april_amt, (select sum(sm.amount from...sm... where sm.joinCol = cm.join_col...and month(sm.date)=5) may_amt 
from ...cm...

[sknake]

Do something like this:

Select 
Invoice.LocNumber,
(Cast(Floor(Cast(OrderDate as float)) as datetime) - DatePart(day, OrderDate) + 1) As YrMo,
Sum(InvDetail.RptAmt) As Sales
From Invoice Inner Join InvDetail On (Invoice.InvNumber = InvDetail.InvNumber)
Group By Invoice.LocNumber, (Cast(Floor(Cast(OrderDate as float)) as datetime) - DatePart(day, OrderDate) + 1)
Order By Invoice.LocNumber, YrMo

This is what I used to group by a month:

(Cast(Floor(Cast(OrderDate as float)) as datetime) - DatePart(day, OrderDate) + 1)

That trims the time portion off the datetime and takes the date back the the first day of the month, which you can group on.