sql problem - 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

Author

Topic

KinYeung
Starting Member

14 Posts

Posted - 2006-06-06 : 04:55:49

Hi all,

I would like to create a view to list the sum of transaction record.
e.g.
in jan 2006, have 3 rec
in feb 2006, have 6 rec
in mar 2006, have 2 rec
in apr 2006, have 9 rec

the view i would like to create will show the result as follow
year, month, total
2006, 1, 3
2006, 2, 9
2006, 3, 11
2006, 4, 20

I tried to use "group by", but it shows wrong result.

pls help, thanks

Regards,

Kin

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 04:58:03

the date field (jan 2006), is it varchar or datetime?

post your group by query so we can help you troubleshoot it

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 05:30:40

Post your table structure, some sample data and the result that you want.

KinYeung
Starting Member

14 Posts

Posted - 2006-06-06 : 06:00:49

TRANS(TRANSID, TRANSDT, DESCR,...)

IN TABLE TRANS:, 3 record in jan and 6 rec in Feb. the datatype of transdt field is datetime

0601-01, 2006/01/16, XXX
0601-02, 2006/01/18, XXX
0601-03, 2006/01/19, XXX

0602-01, 2006/02/02, XXX
0602-02, 2006/02/05, XXX
0602-03, 2006/02/07, XXX
0602-04, 2006/02/10, XXX
0602-05, 2006/02/11, XXX
0602-06, 2006/02/16, XXX

the view is expected as follow:

TRANSSUM (TRANSYEAR, TRANSMONTH, TOTAL)

TRANSYEAR is the year of the transaction,
TRANSMONTH is the month of the transaction and
TOTAL is the total accumodate sum of the transaction

2006, 1, 3 <-- only 3 trans record in jan.
2006, 2, 9 <-- 6 trans record in feb + 3 trans rec. in feb.
2006, 3, 11
2006, 4, 20

Please help.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 06:17:49

Try this

declare @TRANS table
(
	TRANSID	varchar(10),
	TRANSDT	datetime
)
insert into @TRANS
select '0601-01', '2006/01/16' union all
select '0601-02', '2006/01/18' union all
select '0601-03', '2006/01/19' union all
select '0602-01', '2006/02/02' union all
select '0602-02', '2006/02/05' union all
select '0602-03', '2006/02/07' union all
select '0602-04', '2006/02/10' union all
select '0602-05', '2006/02/11' union all
select '0602-06', '2006/02/16' union all
select '0603-07', '2006/03/03' union all
select '0603-08', '2006/03/26'

select	year(TRANS_DT) as [Year], month(TRANS_DT) as [Month], 
	(select count(*) from @TRANS x where x.TRANSDT < dateadd(month, 1, a.TRANS_DT)) as [Total]
from
(
	select	dateadd(month, datediff(month, 0, t.TRANSDT), 0) as TRANS_DT
	from	@TRANS t
	group by dateadd(month, datediff(month, 0, t.TRANSDT), 0)
) a

/* RESULT :
Year        Month       Total       
----------- ----------- ----------- 
2006        1           3
2006        2           9
2006        3           11
*/

KinYeung
Starting Member

14 Posts

Posted - 2006-06-06 : 06:51:04

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources