SQL Problem (Group by Month) - SQL Server Forums

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)

SQL Problem (Group by Month)

Author

Topic

KinYeung
Starting Member

14 Posts

Posted - 2007-02-07 : 01:00:50

Hi all,

I have got a problem wish someone can help me.

I have a table (structure as follow:)
pymnt(start_dt, end_dt, mth_pymnt)

i need to group the amount between start_dt and end_dt by month.
such as:

Assume the data in pymnt table as follow:
Peter, 1/12/2005, 31/3/2006, 300
John, 1/2/2006, 30/4/2006, 100

the result table should look like:

2005, 12, 300
2006, 1, 300
2006, 2, 400
2006, 3, 400
2006, 4, 100

pls help
thanks a lot.

Regards,

Kin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 02:43:50

[code]set dateformat dmy

-- prepare sample data
declare @pymnt table (name varchar(20), start_dt datetime, end_dt datetime, mth_pymnt int)

insert @pymnt
select 'Peter', '1/12/2005', '31/3/2006', 300 union all
select 'John', '1/2/2006', '30/4/2006', 100

declare @mindt datetime,
@maxdt datetime

select @mindt = dateadd(month, datediff(month, 0, min(dt)), 0),
@maxdt = dateadd(month, datediff(month, 0, max(dt)), 0)
from (
select min(start_dt) as dt from @pymnt union all
select max(start_dt) from @pymnt union all
select min(end_dt) from @pymnt union all
select max(end_dt) from @pymnt
) as d

-- prepare staging table
declare @stage table (year smallint, month tinyint, pymnt int)

while @mindt <= @maxdt
begin
insert @stage
select datepart(year, @mindt),
datepart(month, @mindt),
mth_pymnt
from @pymnt
where start_dt <= @mindt
and end_dt >= @mindt

select @mindt = dateadd(month, 1, @mindt)
end

-- show the output
select year,
month,
sum(pymnt) as mth_pymnt
from @stage
group by year,
month
order by 1,
2[/code]

Peter Larsson
Helsingborg, Sweden

KinYeung
Starting Member

14 Posts

Posted - 2007-02-07 : 03:33:38

It's great. thank you so much

KinYeung
Starting Member

14 Posts

Posted - 2007-02-08 : 03:19:20

Hi,

one more question:
is it possible to create a view for this purpose instead of the above code?

thanks

Regards,

Kin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 04:22:29

You can easily make this a function, which works the same way as a view when selecting.

create function dbo.fnMyFunction
(
)
returns @out table ([year] smallint, [month] tinyint, mth_pymnt int)
as
begin
	declare	@mindt datetime,
		@maxdt datetime

	select	@mindt = dateadd(month, datediff(month, 0, min(dt)), 0),
		@maxdt = dateadd(month, datediff(month, 0, max(dt)), 0)
	from	(
		    select min(start_dt) as dt from pymnt union all
		    select max(start_dt) from pymnt union all
		    select min(end_dt) from pymnt union all
		    select max(end_dt) from pymnt
		) as d

	-- prepare staging table
	declare	@stage table (year smallint, month tinyint, pymnt int)

	while @mindt <= @maxdt
		begin
			insert	@stage
			select	datepart(year, @mindt),
				datepart(month, @mindt),
				mth_pymnt
			from	pymnt
			where	start_dt <= @mindt
				and end_dt >= @mindt

			select	@mindt = dateadd(month, 1, @mindt)
		end

	insert		@out
	select		[year],
			[month],
			sum(pymnt)
	from		@stage
	group by	[year],
			[month]

	return
end

And write/type somethin like this

SELECT e.* from employees as e inner join dbo.fnMyFunction() as x on x.empid = e.id

Peter Larsson
Helsingborg, Sweden

KinYeung
Starting Member

14 Posts

Posted - 2007-02-13 : 01:09:12

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources