error in insert the record with all topups and fir

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 2012 Forums

Transact-SQL (2012)

error in insert the record with all topups and fir

Author

Topic

afri
Starting Member

8 Posts

Posted - 2013-07-02 : 06:58:26

hello all

im new be in sql..i have modified sp like bellow :

--update : Afridel
CREATE procedure osr_gen_nth_topup_rpt_by_date_afri
@rpt_dateFrom varchar(10) -- yyyy-MM-dd format
@rpt_dateTo varchar (10) -- yyyy-MM-dd format

as
begin

set nocount on

declare
@lk_srv varchar(50),
@lk_db varchar(50),
@sql_cmd varchar(2000)

select
@lk_srv = srv_name,
@lk_db = [db_name]
from osr_linked_servers(nolock)
where id = 'OFFLINE_ESP'

/* ******************
get top up data
****************** */

--- this temp table stores all topup data for the date
create table #tmp_nth_topup (
iccid varchar(20),
nth_topup int)

--- fill in the temp table with mvno topup data
set @sql_cmd =
'insert into #tmp_nth_topup (iccid, nth_topup)
select
m.iccid,
count(1) nth_topup
from
<@srv_name>.<@db_name>.dbo.topup_log t
join <@srv_name>.<@db_name>.dbo.mvno_account m
on t.mobileno = m.mobileno
where
t.createdate between ''<@rpt_dateFrom> 00:00:00'' and ''<@rpt_dateTo> 23:59:59'' and
t.transstatusid = 1
group by m.iccid'

set @sql_cmd = replace(@sql_cmd, '<@srv_name>', @lk_srv)
set @sql_cmd = replace(@sql_cmd, '<@db_name>', @lk_db)
set @sql_cmd = replace(@sql_cmd, '<@rpt_dateFrom>', @rpt_dateFrom)
set @sql_cmd = replace(@sql_cmd, '<@rpt_dateTo>', @rpt_dateTo)
exec(@sql_cmd)

/* ******************
get first login data
****************** */

--- this temp table stores all 1st login data for the date
create table #tmp_first_login (
iccid varchar(20),
first_login int)

--- fill in the temp table with first login data
set @sql_cmd =
'insert into #tmp_first_login (iccid, first_login)
select iccid, 1
from <@srv_name>.<@db_name>.dbo.sim
where firstupdate between ''<@rpt_dateFrom> 00:00:00'' and ''<@rpt_dateTo> 23:59:59'''

set @sql_cmd = replace(@sql_cmd, '<@srv_name>', @lk_srv)
set @sql_cmd = replace(@sql_cmd, '<@db_name>', @lk_db)
set @sql_cmd = replace(@sql_cmd, '<@rpt_dateFrom>', @rpt_dateFrom)
set @sql_cmd = replace(@sql_cmd, '<@rpt_dateTo>', @rpt_dateTo)

exec(@sql_cmd)

/* ******************
join topup and first login data
****************** */

create table #tmp_full_join (
iccid varchar(20),
nth_topup int,
first_login int)

insert into #tmp_full_join
select
isnull(t.iccid, f.iccid),
isnull(t.nth_topup, 0),
isnull(f.first_login, 0)
from
#tmp_nth_topup t
full join #tmp_first_login f
on t.iccid = f.iccid

--- clean up
drop table #tmp_nth_topup
drop table #tmp_first_login

/* ******************
map topup and first login data with freesim data
****************** */

--- this table maps the topup data with the freesim LP data
create table #tmp_lp_rpt (
ordersim_url varchar(500),
nth_topup int,
first_login int,
[contract] varchar(50),
[source] varchar(25),
country varchar(500))

--- based on the iccid, get freesim data where ordersim_url is not null (orders coming from LP have ordersim_url == not null)
insert into #tmp_lp_rpt
(ordersim_url, nth_topup, first_login)
select
f.ordersim_url, j.nth_topup, j.first_login
from
#tmp_full_join j
inner join freesim f(nolock)
on j.iccid = f.iccid
where
f.ordersim_url is not null

--- clean up
drop table #tmp_full_join

/* ******************
parse all ordersim_url into contract, source, country in #tmp_lp_rpt
****************** */

--- expected ordersim_url format: <unused string><source><contract><country><unused non-aplha><unused string>
update t
set
t.[contract] = isnull(t2.[contract], '#N/A#'),
t.[source] = isnull(t2.[source], '#N/A#'),
t.country =
isnull(
(select dbo.osr_parse_country(
t.ordersim_url,
t2.[source],
t2.[contract])),
'#N/A#')
from
#tmp_lp_rpt t
left join
(select s.[source] + c.[contract] s_c, [contract], [source]
from osr_source s (nolock) cross join osr_contract c (nolock)) t2
--- expected ordersim_url format: <unused string><source><contract><unused string>
on substring(t.ordersim_url, charindex(t2.s_c, t.ordersim_url), len(t2.s_c)) = t2.s_c

--- log parsing failures
insert into osr_contract_parsing_failure (create_date, ordersim_url)
select getdate(), t.ordersim_url
from (select distinct(ordersim_url) ordersim_url
from #tmp_lp_rpt
where [contract] = '#N/A#' or [source] = '#N/A#' or country = '#N/A#') t

/* ******************
list all unique contract-source-country combination for looping and grouping
****************** */

create table #tmp_unique_contract
(id int identity(1, 1),
[contract] varchar(50),
[source] varchar(25),
country varchar(500))

insert into #tmp_unique_contract ([contract], [source], country)
select distinct [contract], [source], country
from #tmp_lp_rpt

/* ******************
generate the final report
****************** */

--- delete existing record of the date
delete from osr_dly_topup_rpt where rpt_date between @rpt_dateFrom AND @rpt_dateTo

declare @curr_loop int, @max_loop int
set @curr_loop = 1
select @max_loop = max(id) from #tmp_unique_contract

while @curr_loop <= @max_loop
begin

declare
@curr_contract varchar(50),
@curr_source varchar(25),
@curr_country varchar(500)

select
@curr_contract = [contract],
@curr_source = [source],
@curr_country = country
from #tmp_unique_contract
where id = @curr_loop

--- insert the record with all topups and first login as 0
insert into osr_dly_topup_rpt (rpt_date, [contract], [source], country)
values (@rpt_dateFrom,@rpt_dateTo @curr_contract, @curr_source, @curr_country)

declare @curr_rpt_id bigint
select @curr_rpt_id = scope_identity()

--- update first login
set @sql_cmd =
'update osr_dly_topup_rpt
set first_login = (
select count(1)
from #tmp_lp_rpt
where
[contract] = ''<@curr_contract>'' and
[source] = ''<@curr_source>'' and
country = ''<@curr_country>'' and
first_login = 1)
where
id = <@curr_rpt_id>'

set @sql_cmd = replace(@sql_cmd, '<@curr_contract>', @curr_contract)
set @sql_cmd = replace(@sql_cmd, '<@curr_source>', @curr_source)
set @sql_cmd = replace(@sql_cmd, '<@curr_country>', @curr_country)
set @sql_cmd = replace(@sql_cmd, '<@curr_rpt_id>', cast(@curr_rpt_id as varchar))
exec(@sql_cmd)

declare
@curr_nth int,
@max_nth int

set @curr_nth = 1
set @max_nth = 6

--- transpose nth topup into columns
while @curr_nth <= @max_nth
begin

set @sql_cmd =
'update osr_dly_topup_rpt
set topup<@nth> = (
select count(1)
from #tmp_lp_rpt
where
[contract] = ''<@curr_contract>'' and
[source] = ''<@curr_source>'' and
country = ''<@curr_country>'' and
nth_topup = <@nth>)
where
id = <@curr_rpt_id>'

set @sql_cmd = replace(@sql_cmd, '<@curr_contract>', @curr_contract)
set @sql_cmd = replace(@sql_cmd, '<@curr_source>', @curr_source)
set @sql_cmd = replace(@sql_cmd, '<@curr_country>', @curr_country)
set @sql_cmd = replace(@sql_cmd, '<@nth>', cast(@curr_nth as varchar))
set @sql_cmd = replace(@sql_cmd, '<@curr_rpt_id>', cast(@curr_rpt_id as varchar))

exec(@sql_cmd)

set @curr_nth = @curr_nth + 1
end

set @curr_loop = @curr_loop + 1
end

--- clean up
drop table #tmp_unique_contract
drop table #tmp_lp_rpt

end

but im error in myquery(insert into osr_dly_topup_rpt (rpt_date, [contract], [source], country)
values (@rpt_dateFrom,@rpt_dateTo @curr_contract, @curr_source, @curr_country) )

thanks

regard
afri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:13:22

whats the error message?
Also there are few other issues i can see

1. using varchar fields for date values. You should always use datetime or date related datatypes for storing dates
2. using # tables inside dynamic sql. Why not keep INSERT part outside dynamic string and use sp_executesql to execute string alone?
3. I cant see a complelling reason for usage of loops here. I stringly you whould be able to implement the above requirement using a set based solution. We can suggest you alternatives if you mind explaining us yout actual requirement with some sample data and required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

afri
Starting Member

8 Posts

Posted - 2013-07-02 : 07:52:09

i still confiused

pls help me

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-02 : 12:19:14

So are we.

Again, what is the error message?

Here are some links on how to ask your question so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources