Need help converting a SP to MySQL
I have been tasked with converting a SQL Server DB to MySql.
I have a procedure that I cannot seem to figure out where to begin the transition from:
HERE is the SQL server version:
ALTER procedure ActivitySummary
@startdate datetime
,@enddate datetime
as
/*
declare @startdate datetime
,@enddate datetime
set @startdate = '11/8/05'
set @enddate = '11/11/05'
*/
set @startdate = convert(datetime,@startdate,101)
set @enddate = convert(datetime,@enddate,101)
Declare @summary table (NumRecords int
,List varchar(50)
,TransType varchar(50)
,Source varchar(50)
,Type varchar(20)
)
-- Get beginning counts of actives for each list
insert into @summary(NumRecords, List, Type)
select count (userid), List_Name, 'Beginning'
from dbo.Transactions
where transaction_type = 'opt-in' and
(InactiveDate is null
or
convert(datetime,InactiveDate,101) > @Startdate)
and Transaction_date <= @startdate
group by List_name
insert into @summary(NumRecords, List, Type)
select count (Distinct(userid)), 'Unique Records', 'Beginning'
from dbo.Transactions
where transaction_type = 'opt-in' and
(InactiveDate is null
or
convert(datetime,InactiveDate,101) > @Startdate)
and Transaction_date <= @startdate
--Get summarry counts for each list
insert into @summary(NumRecords,TransType, Source, List, type)
select
case when Transaction_Type = 'Opt-Out' then - count(a.userid)
else count(a.userid) end as NumRecords , Transaction_Type,OriginalSourceType , List_name,
case when Transaction_Type = 'Opt-Out' then 'Request Offs'
when Transaction_Type = 'Opt-In' then 'Ons'
else 'System Offs' end
from dbo.Transactions a
inner join users b on
a.userid = b.userid
where convert(datetime,Transaction_date,101)
between @startdate and @enddate
and list_name is not null
group by Transaction_Type, OriginalSourceType , List_name
insert into @summary(NumRecords,TransType, Source, List, type)
select
case when Transaction_Type = 'Opt-Out' then - count(Distinct(a.userid))
else count(Distinct(a.userid)) end as NumRecords , Transaction_Type,OriginalSourceType , 'Unique Records',
case when Transaction_Type = 'Opt-Out' then 'Request Offs'
when Transaction_Type = 'Opt-In' then 'Ons'
else 'System Offs' end
from dbo.Transactions a
inner join users b on
a.userid = b.userid
where convert(datetime,Transaction_date,101)
between @startdate and @enddate
and list_name is not null
group by Transaction_Type, OriginalSourceType
select * from @summary
How do I even start this one and will MySql be able to do this?