"Eckhart" <n.kopal***@gmail.com> wrote in message
news:1154066248.554676.193580@75g2000cwc.googlegroups.com...
> Dear All, I am facing a problem with the following query,taking lot of
> time to fetch the data,
> calling the data using multi selects.
>
> CREATE proc Rolexi36Sync
> as
> DECLARE @date varchar(50),@ydate varchar(50)
> print CONVERT(char(11),(GETDATE()-1),100)
> SET @date =
> substring(CONVERT(char(11),(GETDATE()),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4)
> SET @ydate =
> substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)
> Print @date
> Print @ydate
> insert into
> biiod.dbo.data_trans_currentday_test(MobileNo,UA,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,CatID,SubCatID,SpecialPackage,Royalties,
> Operator,Circle,OPGPName)
> (select mobileno,
> (SELECT CASE ua
> when 'unknown' then null
> else ua
> end) as ua,
> (select case remarks
> when 'unknown' then null
> else remarks
> end) as remarks,
> contentid,
> (select case description
> when 'unknown' then null
> else description
> end) as description,
> (select musiclabel from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as musiclable,
> (select cpid from datalogs.dbo.contentprovider where cpname =
> datalogs.dbo.translogs.cpname) as cpid,
> cpname,
> contenttype,
> (select catname from datalogs.dbo.cont_Catg where catid in (select
> catid from cont_master where contentid =
> datalogs.dbo.translogs.contentid)) as category,
> (select subcatname from datalogs.dbo.cont_subCatg where subcatid in
> (select subcatid from cont_master where contentid =
> datalogs.dbo.translogs.contentid)) as subcategory,
> transactiondate,1 as Units, price,
> (select case servicename
> when 'AIRTELIVE' then remarks
> when 'ALCOMBOPACKREG' then remarks
> when 'HINDI' then remarks
> when 'NOKIAGAL' then remarks
> when 'SUDOKU' then remarks
> when 'SUDOKU_APP' then remarks
> else NULL
> end) as SHORTCODE,
> servicename,
> (select case servicename
> when 'TSTTNEWS' THEN 600
> when 'TSTTWAP' THEN 600
> when 'TSTT_MMS' THEN 600
> when 'AKTEL' THEN 300
> when 'TELEMOVIL' THEN 700
> when 'COMCEL' THEN 701
> when 'QATAR2900' THEN 1
> ELSE
> (select operatorid from datalogs.dbo.operator where phoneseries =
> substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
> end) as operatorid,
> (select catid from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as catid,
> (select subcatid from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as subcatid,
> (select specialpackage from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as specialpackage,
> (select Royalties from datalogs.dbo.cont_master where contentid =
> datalogs.dbo.translogs.contentid) as Royalties,
> (select case servicename
> when 'AKTEL' then 'Aktel'
> when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
> when 'TELEMOVIL' then 'TeleMovil'
> when 'COMCEL' THEN 'COMCEL'
> when 'TSTTNEWS' then 'TSTT'
> when 'TSTTWAP' then 'TSTT'
> when 'TSTT_MMS' then 'TSTT'
> when 'ALCLICKWIN6464' then 'Airtel'
> when 'ALMMSPORTAL' then 'Airtel'
> when 'ALMMSSMSDWN' then 'Airtel'
> when 'ALMYALBUM646' then 'Airtel'
> when 'HINDU6397' then
> substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
> else
> (select OPname from datalogs.dbo.operator where phoneseries =
> substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
> end) as Operator,
> (select case servicename
> when 'AKTEL' then 'Bangladesh'
> when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
> when 'TELEMOVIL' then 'El Salvador'
> when 'COMCEL' THEN 'Gautemala'
> when 'TSTTNEWS' then 'Trinidad'
> when 'TSTTWAP' then 'Trinidad'
> when 'TSTT_MMS' then 'Trinidad'
> when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
> 6,len(remarks)-PATINDEX('%-%',remarks))
> else
> (select Circlename from datalogs.dbo.operator where phoneseries =
> substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
> end) as Circle,
> (select case servicename
> when 'AKTEL' then 'Aktel'
> when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
> when 'TELEMOVIL' then 'TeleMovil'
> when 'COMCEL' THEN 'COMCEL'
> when 'TSTTNEWS' then 'TSTT'
> when 'TSTTWAP' then 'TSTT'
> when 'TSTT_MMS' then 'TSTT MMS'
> when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
> when 'ALMMSPORTAL' then 'Airtel MMS'
> when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
> when 'ALMYALBUM646' then 'Airtel My Album'
> when 'HINDU6397' then 'Hindu 6397'
> else
> (select OPname from datalogs.dbo.operator where phoneseries =
> substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
> end) as OPGPName
> from datalogs.dbo.translogs where transactiondate >= @ydate and
> transactiondate < @date and servicename in
> ('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))
> go
>