Querty to select records for every month in current year

[oleanganesh]

Hi

How to select records for each month of current year in stored procedure. (starting from 1st month of the current year to current month)
for eg: i have a sample query for a specific month with hardcoded date range
select sum(subtotal) from table1 where (invodate >= '09/01/2004' and invodate <= '09/30/2004') and (ordrdate >= '09/01/2004' and ordrdate <= '09/30/2004')
I need to display the subtotal for everymonth in a report
Any help appreciated
Thanks

 

[SQLSister]

Let's start with a question. Are you using a fiscal year that is different from the actual year?



Questions about posting. See faq183-874

 

[oleanganesh]

No. I need to fetch the records for actual year (jan - current month)

 

[SQLSister]

The kind of query you need is a cross tab query. THe following FAQs can give you some help with that.
Crosstab Query - PART I (Code Generator) faq183-5269 donutman 7/18/04 --
Crosstab Query - PART II (Dynamic Execution) faq183-5278

To get the actual dates for the between clause you may need to use getdate() and datepart() to find the current month and year. Then you can build the actual dates you want.

It is probably simpler to build your btween clause more in the followingg pattern:
where (invodate >= '09/01/2004' and invodate < '10/1/2004')

This is becasue it is easier to say less than the first day of teh next month than to figure out exactly waht the last day of the month is, espcially in leap years. In doing so, don't forget to account for what happens at the end of the year.

Questions about posting. See faq183-874

 

[ESquared]

Not to mention that '<=9/30/2004' doesn't select anything after 12am on 9/30...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)