Monthly Subtotals from date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Monthly Subtotals from date range

Author

Topic

mkm88414
Starting Member

1 Post

Posted - 2007-12-05 : 02:11:30

Hi,

My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'

I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this

Maybe a case?

Thanks for your help guys. Let me know if any more info would help.

JasonL
Starting Member

35 Posts

Posted - 2007-12-14 : 16:31:38

select rental_date_column_but_converted_to_year_month, count(*)
from table_x
where rental_date between input_date1 and input_date2
group by rental_date_column_but_converted_to_year_month
order by ...

in your example result would something like

2007-11 120
2007-12 150
2008-01 112

good luck and you may want to readup a bit more on T-SQL and understand a bit on SET theory.

see also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94216
by jezemine
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources