Any better sql can do the following:

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)

Any better sql can do the following:

Author

Topic

KinYeung
Starting Member

14 Posts

Posted - 2006-06-16 : 06:46:15

Hi all,

I have a table, table structure as follow:

MYTABLE(ID,EFFDT,CATEGORY,TYPE)
value:
1,'01/01/2005','PERSONAL','A'
2,'12/01/2005','PERSONAL','B'
3,'08/03/2005','BUSINESS','B'
4,'22/04/2005','PERSONAL','A'
5,'26/04/2005','PERSONAL','A'
6,'01/04/2005','BUSINESS','A'

I want to generate a report like the following

MONTH TOTAL_PER_A TOTAL_PER_B TOTAL_BUS_A TOTAL_BUS_B
1/2005 1 1 0 0
2/2005 0 0 0 0
3/2005 0 0 0 1
4/2005 2 0 1 0

I've created 4 views for TOTAL_PER_A, TOTAL_PER_B TOTAL_BUS_A AND TOTAL_BUS_B (I use group by to get the count of id for each of them) then join together and get the result table.

I think it's stupid but i cannot think any other better way can get the same result. Is it has a better way to do the same things

pls advice, thanks

Regards,

Kin

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-16 : 07:23:30

Something like this...

--data
declare @MYTABLE table (ID int, EFFDT datetime, CATEGORY varchar(10), TYPE char(1))
insert @MYTABLE
          select 1,'01/01/2005','PERSONAL','A'
union all select 2,'12/01/2005','PERSONAL','B'
union all select 3,'08/03/2005','BUSINESS','B'
union all select 4,'22/04/2005','PERSONAL','A'
union all select 5,'26/04/2005','PERSONAL','A'
union all select 6,'01/04/2005','BUSINESS','A'

--calculation
declare @months table (month int)
insert @months
          select '200501'
union all select '200502'
union all select '200503'
union all select '200504'

select month,
    sum(case when CATEGORY = 'PERSONAL' and TYPE = 'A' then 1 else 0 end) as TOTAL_PER_A,
    sum(case when CATEGORY = 'PERSONAL' and TYPE = 'B' then 1 else 0 end) as TOTAL_PER_B,
    sum(case when CATEGORY = 'BUSINESS' and TYPE = 'A' then 1 else 0 end) as TOTAL_BUS_A,
    sum(case when CATEGORY = 'BUSINESS' and TYPE = 'B' then 1 else 0 end) as TOTAL_BUS_B
from @months a left outer join @MYTABLE b on a.month = year(effdt) * 100 + month(effdt)
group by month

/*results
month       TOTAL_PER_A TOTAL_PER_B TOTAL_BUS_A TOTAL_BUS_B 
----------- ----------- ----------- ----------- ----------- 
200501      1           1           0           0
200502      0           0           0           0
200503      0           0           0           1
200504      2           0           1           0
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-16 : 07:28:55

By the way, you might find it useful to use a function to get the '@months' table (instead of hard-coding it). If so, I'd recommend using this one...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

And if you need a 'dynamic pivot', these will be useful...
http://www.sqlteam.com/item.asp?ItemID=2955
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 07:42:25

Also read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

KinYeung
Starting Member

14 Posts

Posted - 2006-06-16 : 22:05:28

Thanks a lot.
It's useful.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-16 : 23:38:59

Another way, using the date function that Ryan mentioned.


declare @MYTABLE table 
(ID int, EFFDT datetime, CATEGORY varchar(10), TYPE char(1))

insert @MYTABLE
select 1,'20050101','PERSONAL','A'
union all select 2,'20050112','PERSONAL','B'
union all select 3,'20050308','BUSINESS','B'
union all select 4,'20050422','PERSONAL','A'
union all select 5,'20050426','PERSONAL','A'
union all select 6,'20050401','BUSINESS','A'



select
	MONTH = max(YEAR_MONTH_NAME_LONG),
	sum(case x when 11 then 1 else 0 end) as PER_A,
	sum(case x when 12 then 1 else 0 end) as PER_B,
	sum(case x when 21 then 1 else 0 end) as BUS_A,
	sum(case x when 22 then 1 else 0 end) as BUS_B
from 
	-- Date function from script library
	dbo.F_TABLE_DATE('20050101','20050430') a
	left outer join
	( select *, x = 
		case CATEGORY
		when 'PERSONAL' then 10 
		when 'BUSINESS' then 20 end+ 
		case TYPE 
		when  'A' then 1
		when  'B' then 2 end
	from @MYTABLE ) b
	on a.date = b.EFFDT
group by
	a.MONTH_SEQ_NO
order by
	a.MONTH_SEQ_NO

Results:



MONTH          PER_A       PER_B       BUS_A       BUS_B       
-------------- ----------- ----------- ----------- ----------- 
2005 January   1           1           0           0
2005 February  0           0           0           0
2005 March     0           0           0           1
2005 April     2           0           1           0

(4 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources