need help on this queery - 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 2008 Forums

Transact-SQL (2008)

need help on this queery

Author

Topic

amsad
Starting Member

17 Posts

Posted - 2011-01-12 : 23:09:43

Hi,
i already select this guery like this and the result show as blow,
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select count(convert(bigint,(inc.inc_id))) as countCDR,
inc.hour_of_day,
inc.recordtype_string,
sum(convert(bigint,inc.whole_dis_value))/(10000)as revenue,
sum(convert(bigint,inc.usage_duration))/(60) as Mou
from in_cdr03 inc
where (inc.duplicate_fl = 'N'
and inc.service_nature <> 'RouteMT'
and inc.service_nature not like 'ROAM%'
and inc.tariff_class not like 'RL%'
and inc.tariff_class not like 'RH%'
and inc.tariff_class not like 'ROAM%'
and inc.tariff_class not like 'USSD%'
and inc.other_party_region not like 'Free%'
and inc.tariff_class not like 'IDD_%'
and inc.tariff_class <> 'INT_CAN'
and inc.tariff_class <> 'INT_US'
and inc.tariff_class not like 'I_62%'
and inc.tariff_class not like 'I_65%'
and inc.tariff_class not like 'I_852%'
and inc.tariff_class not like 'I_86%'
and hour_of_day=0)
group by inc.hour_of_day,inc.recordtype_string

+++++++++++++++++++++++++++++++++++++++++++++++

countCDR | hour_of_day |recordtype_string | revenue | Mou |
15545 0 Content 140889 NULL
1167337 0 GPRS 487908 NULL
169077 0 GPRS Refund 379920 NULL
9769 0 MM Retrieval(MMS) NULL NULL
10963 0 MM Submission(MMS) 79843 NULL
332685 0 MO 164431 978074
2416 0 Undo MM Call(MMS) 6601 NULL

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

i need this table showing like this,just one row appear.For MMS we combine(MM Retrieval(MMS),MM Submission(MMS),Undo MM Call(MMS))and also for GPRS.

-------------------------------------------------------------------------------------------
|hour_of_day|countCDR|revenue|Mou |countCDR|revenue|countCDR|revenue|countCDR |revenue |
| | (MO) | (MO) |(MO)| (MMS) | (MMS) | (GPRS)| (GPRS)|(CONTENT)|(Content)|
---------------------------------------------------------------------
0 332685 164431 978074 23148 86444 1336414 867828 15545 140889

Need your assist and help...

thanks.

Can do.

amsad
Starting Member

17 Posts

Posted - 2011-01-13 : 02:35:16

all,

need your help on this.thank u.

Can do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 10:59:56

[code]select hour_of_day,
SUM(CASE WHEN recordtype_string = 'MO' THEN countCDR ELSE 0 END) AS [countCDR(MO)],
SUM(CASE WHEN recordtype_string = 'MO' THEN revenue ELSE 0 END) AS [revenue (MO)],
SUM(CASE WHEN recordtype_string = 'MO' THEN Mou ELSE 0 END) AS [Mou (MO)],
SUM(CASE WHEN recordtype_string = 'MM' THEN countCDR ELSE 0 END) AS [countCDR(MMS)],
SUM(CASE WHEN recordtype_string = 'MM' THEN revenue ELSE 0 END) AS [revenue (MMS)],
SUM(CASE WHEN recordtype_string = 'MM' THEN Mou ELSE 0 END) AS [Mou (MMS)],
SUM(CASE WHEN recordtype_string = 'GPRS' THEN countCDR ELSE 0 END) AS [countCDR (GPRS)],
SUM(CASE WHEN recordtype_string = 'GPRS' THEN revenue ELSE 0 END) AS [revenue (GPRS)],
SUM(CASE WHEN recordtype_string = 'GPRS' THEN Mou ELSE 0 END) AS [Mou (GPRS)],
... similary for content
from
(select count(convert(bigint,(inc.inc_id))) as countCDR,
inc.hour_of_day,
inc.recordtype_string,
sum(convert(bigint,inc.whole_dis_value))/(10000)as revenue,
sum(convert(bigint,inc.usage_duration))/(60) as Mou
from in_cdr03 inc
where (inc.duplicate_fl = 'N'
and inc.service_nature <> 'RouteMT'
and inc.service_nature not like 'ROAM%'
and inc.tariff_class not like 'RL%'
and inc.tariff_class not like 'RH%'
and inc.tariff_class not like 'ROAM%'
and inc.tariff_class not like 'USSD%'
and inc.other_party_region not like 'Free%'
and inc.tariff_class not like 'IDD_%'
and inc.tariff_class <> 'INT_CAN'
and inc.tariff_class <> 'INT_US'
and inc.tariff_class not like 'I_62%'
and inc.tariff_class not like 'I_65%'
and inc.tariff_class not like 'I_852%'
and inc.tariff_class not like 'I_86%'
and hour_of_day=0)
group by inc.hour_of_day,inc.recordtype_string
)t
group by hour_of_day
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources