December 10, 2008 at 9:57 pm
Am working in roaming system, i have created four views, one for retrieving the Mobile Originated Calls, another for Mobile Terminated Calls, another for SMS Originated, and the last one for displaying the SMS Terminated (received).
However, i didn't make any group by because i will display the date for each event, each on which the date will be search criteria, summarizing all the events is intended to be done inside a crystal report.
what i want to display is sth like this.
TelecomOpertor MCCMNC MocCounter MocTotminutes MocTotChareg MtcCounter MtcTotMins MtcTotCharge SMSOCounter SMSOTotCharge SMSTCounter SMSTCharge.
Further more, i have designed a sub report for each event ( four reports), the problem is in the first two columns, because i want to dsiplay them when ever one of the events had occurred for that particular TelecomOperator, the question is how can i merge the four views with another fiveth view that will contain telecomoperator info.
December 12, 2008 at 8:44 am
You have not given enough information for an exact solution, but here is one approach you might consider:
WITH TeleData (TelecomOpertor, MocCounter, MocTotminutes, MocTotCharge
, MtcCounter, MtcTotMins, MtcTotCharge
, SMSOCounter, SMSOTotCharge
, SMSTCounter, SMSTCharge
)
AS
(
SELECT TelecomOperator, MocCounter, MocTotminutes, MocTotCharge
, 0 AS MtcCounter, 0 AS MtcTotminutes, 0 AS MtcTotCharge
, 0 AS SMSOCounter, 0 AS SMSOTotCharg
, 0 AS SMSTCounter, 0 AS SMSTTotCharg
FROM vwMobileOriginatedCalls
UNION ALL
SELECT TelecomOperator, 0,0,0, MtcCounter, MtcTotminutes, MtcTotCharge,0,0,0,0
FROM vwMobileTerminatedCalls
UNION ALL
SELECT TelecomOperator, 0,0,0,0,0,0, SMSOCounter, SMSOTotCharg,0,0
FROM vwSMSOriginated
UNION ALL
SELECT TelecomOperator, 0,0,0,0,0,0,0,0,SMSTCounter, SMSTTotCharg
FROM vwSMSTerminated
)
SELECT TelecomOpertor
, SUM(MocCounter) AS MocCounter
, SUM(MocTotminutes) AS MocTotminutes
, SUM(MocTotCharge) AS MocTotCharge
, SUM(MtcCounter) AS MtcCounter
, SUM(MtcTotMins) AS MtcTotMins
, SUM(MtcTotCharge) AS MtcTotCharge
, SUM(SMSOCounter) AS SMSOCounter
, SUM(SMSOTotCharge) AS SMSOTotCharge
, SUM(SMSTCounter) AS SMSTCounter
, SUM(SMSTCharge) AS SMSTCharge
FROM TeleData
GROUP BY TelecomOpertor
December 12, 2008 at 10:17 pm
Thanks for your replay, however, i temporarly created a table that will hold the final data for my report, and also another view for it.
what i did was, i populate the data of the table durring run time, i.e upon clicking on search button, so, from the four basic views i will rerieve the data i want with the date i want, and grouping by and other agregate functions are made at this time, once i got the data (.e.g. for the Mobile Originated Calls) i will call stored procedure that will decide whether to update or to insert depending on the existance of the MCCMNC (since it's unique).
this worked fine, but it is offending the perforamnce of the system, however, i will give a try of your sugesstion, and i 'll let u know.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply