Question from an Ex-MSSQL admin/dev
Will this kind of cursor work in MySQL and if so, what the minimum version number it will? Bear in mind this is not part of a stored proc.
Declare @Start nvarchar(20)
Declare @End nvarchar(20)
Declare @Cash1 decimal(10,2)
Declare @Cash2 decimal(10,2)
Declare @Card1 decimal(10,2)
Declare @Card2 decimal(10,2)
Declare @CashTotal decimal(10,2)
Declare @CardTotal decimal(10,2)
Declare @RevTotal as Decimal(10,2)
Declare @MeterID as nvarchar(5)
Declare @Date1 as datetime
Declare @Date2 as datetime
Set @Start = '1/12/04'
Set @End = '5/12/04'
DECLARE REVENUE_CURSOR CURSOR FOR
select Distinct MeterID,
[Date],
CashTotal as Cash,
CardsTotal as Cards
FROM PA_MONEY
WHere datepart(hh,[Time]) <= 6
AND ([Date] = CONVERT(DATETIME, @Start, 3)
OR [Date] = CONVERT(DATETIME, @End, 3))
AND CashCol <> 1
Order By MeterID, [Date]
OPEN REVENUE_CURSOR
FETCH NEXT FROM REVENUE_CURSOR
INTO @MeterID, @Date1, @Cash1, @Card1
FETCH NEXT FROM REVENUE_CURSOR
INTO @MeterID, @Date2, @Cash2, @Card2
Set @CashTotal = (@Cash2-@Cash1)
Set @CardTotal = @Card2-@Card1
Select
@MeterID as MeterID,
@Date1 as StartDate,
@Date2 as EndDate,
@CashTotal as Cash,
@CardTotal as Card,
(@CashTotal + @CardTotal) as RevTotal
Set @Cash1 = 0
Set @Cash2 = 0
Set @Card1 = 0
Set @Card2 = 0
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM REVENUE_CURSOR
INTO @MeterID, @Date1, @Cash1, @Card1
FETCH NEXT FROM REVENUE_CURSOR
INTO @MeterID, @Date2, @Cash2, @Card2
Set @CashTotal = (@Cash2-@Cash1)
Set @CardTotal = @Card2-@Card1
Select
@MeterID as MeterID,
@Date1 as StartDate,
@Date2 as EndDate,
@CashTotal as Cash,
@CardTotal as Card,
(@CashTotal + @CardTotal) as RevTotal
Set @Cash1 = 0
Set @Cash2 = 0
Set @Card1 = 0
Set @Card2 = 0
END
CLOSE REVENUE_CURSOR
DEALLOCATE REVENUE_CURSOR
GO
Appreciate any info on this.
Cheers