How can I list all months between 2 days? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

How can I list all months between 2 days?

Author

Topic

KinYeung
Starting Member

14 Posts

Posted - 2006-10-26 : 04:11:25

Hi all,

I have a table in following structure:

table (item, start date, end date, amount)
the amount in the table shows the monthly expenses between this period. For example:

'ITEM A', 2006-06-01 00:00:00.000, 2006-09-30 00:00:00.000, 200.0000000000000
'ITEM B', 2006-07-01 00:00:00.000, 2006-07-31 00:00:00.000, 300.0000000000000

which means, ITEM A requires $200 from 1/6/2006 to 30/9/2006 monthly.

Now I would like to create a sql which can list all the monthly sum:
(year, month, total monthly expense)
2006, 6, 200
2006, 7, 500
2006, 8, 200

but i really have no idea about how to list all months between 2 days by SQL. pls advice. thanks

Regards,

Kin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 04:26:58

[code]
declare @table table
(
item varchar(10),
start_date datetime,
end_date datetime,
amount decimal(10,2)
)

insert into @table
select 'ITEM A', '2006-06-01', '2006-09-30', 200 union all
select 'ITEM B', '2006-07-01', '2006-07-31', 300

declare @min_date datetime,
@max_date datetime

select @min_date = min(start_date) from @table
select @max_date = max(end_date) from @table

select YEAR_MONTH, total_monthly_expense = sum(amount)
from
(
select distinct YEAR_MONTH, t.amount
from F_TABLE_DATE(@min_date, @max_date) d inner join @table t
on d.DATE >= t.start_date
and d.DATE <= t.end_date
) a
group by YEAR_MONTH
/*
YEAR_MONTH total_monthly_expense
----------- ----------------------
200606 200.00
200607 500.00
200608 200.00
200609 200.00
*/
[/code]
Note : F_TABLE_DATE from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 04:38:33

[code]-- Prepare test data
declare @test table (item varchar(6), startdate datetime, enddate datetime, amount int)

insert @test
select 'ITEM A', '2006-06-01', '2006-09-30', 200 union all
select 'ITEM B', '2006-07-01', '2006-07-31', 300

-- Do the work
select y.[year] [Year],
m.[month] [Month],
isnull(sum(t.amount), 0) [Amount]
from (
select distinct number [month]
from master..spt_values
where number between 1 and 12
) m
CROSS JOIN (
SELECT 2006 [Year] union all select 2007
) y
left join (
select datepart(year, startdate) startyear,
datepart(month, startdate) startmonth,
datepart(year, enddate) endyear,
datepart(month, enddate) endmonth,
amount
from @test
) t on t.startmonth <= m.[month] and t.endmonth >= m.[month] and t.startyear = y.[year] and t.endyear = y.[year]
GROUP BY y.[year],
m.[month]
ORDER BY y.[year],
m.[month][/code]

Output:[code]Year Month Amount
2006 1 0
2006 2 0
2006 3 0
2006 4 0
2006 5 0
2006 6 200
2006 7 500
2006 8 200
2006 9 200
2006 10 0
2006 11 0
2006 12 0[/code]
Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 04:55:43

Version 2. Possible bug in version 1


declare @table table
(
	item		varchar(10),
	start_date	datetime,
	end_date	datetime,
	amount		decimal(10,2)
)

insert into @table
select	'ITEM A', '2006-06-01', '2006-09-30', 200 union all
select	'ITEM B', '2006-07-01', '2006-07-31', 300

declare @min_date	datetime,
	@max_date	datetime

select	@min_date = min(start_date) from @table
select	@max_date = max(end_date) from @table

select	d.YEAR, d.MONTH, 
	total_monthly_expense = sum(t.amount)
from
(
	select	YEAR, MONTH
	from	F_TABLE_DATE(@min_date, @max_date) d 
	group by YEAR, MONTH
) d
inner join @table t
on	d.YEAR	>= year(t.start_date)	and	d.YEAR  <= year(t.end_date)
and	d.MONTH	>= month(t.start_date)	and	d.MONTH	<= month(t.end_date)
group by d.YEAR, d.MONTH

/*
YEAR   MONTH total_monthly_expense                    
------ ----- ---------------------------------------- 
2006   6     200.00
2006   7     500.00
2006   8     200.00
2006   9     200.00

(4 row(s) affected)
*/

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 05:10:05

Version 3. Using LEFT JOIN to list entire year's month


declare @table table
(
	item		varchar(10),
	start_date	datetime,
	end_date	datetime,
	amount		decimal(10,2)
)

insert into @table
select	'ITEM A', '2006-06-01', '2006-09-30', 200 union all
select	'ITEM B', '2006-07-01', '2006-07-31', 300

declare @min_date	datetime,
	@max_date	datetime

select	@min_date = dateadd(year, datediff(year, 0, min(start_date)), 0) from @table    -- begining of the year
select	@max_date = dateadd(year, datediff(year, 0, max(end_date)) + 1, -1) from @table -- end of the year

select	d.YEAR, d.MONTH, 
	total_monthly_expense = sum(isnull(t.amount, 0))
from
(
	select	YEAR, MONTH
	from	F_TABLE_DATE(@min_date, @max_date) d 
	group by YEAR, MONTH
) d
left join @table t
on	d.YEAR	>= year(t.start_date)	and	d.YEAR  <= year(t.end_date)
and	d.MONTH	>= month(t.start_date)	and	d.MONTH	<= month(t.end_date)
group by d.YEAR, d.MONTH
/*

YEAR   MONTH total_monthly_expense                    
------ ----- ---------------------------------------- 
2006   1     .00
2006   2     .00
2006   3     .00
2006   4     .00
2006   5     .00
2006   6     200.00
2006   7     500.00
2006   8     200.00
2006   9     200.00
2006   10    .00
2006   11    .00
2006   12    .00

(12 row(s) affected)
*/

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources