SQL Development - Golden Jackpot Progressive:

This automated SQL SP progressed a drawing prize amount for every jackpot over a certain amount detected on a specific range of gaming machines (those in the high limit room). It was hand-coded using SQL Query Analyzer, and fed variables to a Flash animation displaying the progressive. This ran every 5 minutes, within scheduled jobs on a SQL Server, and used BCP as well as an XP_CMDSHELL call to initiate copying of text files containing generated variables to a remote computer to feed the associated Flash application.

Jackpot Progressive (SQL Stored Procedure), 2008:

if exists (select * from dbo.sysobjects where id = object_id('dbo.usp_GoldenJackpotProgressive') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure dbo.usp_GoldenJackpotProgressive
GO


/***************************************************************************
Date        Version     Name
09/27/2008  11.5.2      Dana Laratta
Automated SP to increment progressive based on jackpot $1,200+
located in the High Limit room. Exports to a local .txt file
and calls a .bat file to copy results to Flash animation local

***************************************************************************/

CREATE PROCEDURE dbo.usp_GoldenJackpotProgressive
	@increment_amount money
	, @increment_jpminimum money
	, @specifiedmeasurestart datetime = NULL
	, @specifiedmeasureend datetime = NULL
AS
BEGIN

--vars for testing
--DECLARE @increment_amount money
--DECLARE @increment_jpminimum money
--DECLARE @specifiedmeasurestart datetime
--DECLARE @specifiedmeasureend datetime
--set @increment_amount = 25
--set @increment_jpminimum = 1200





--Track Qualifying Jackpots in Subset of SlotNumbers and Icrement a Figure by $25 apieace

--Create Table to hold last five qualifying jackpots
if exists (select * from dbo.sysobjects where id = object_id(N'usr_GoldenJackpotProgDisplay_Last5') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5
	CREATE TABLE gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5 
	(DisplayDatetxt varchar(10)
	, SlotNumbertxt varchar(6)
	, Denominationtxt varchar(6)
	, Amounttxt money
	, JackpotFillDate datetime)

--Declare local variables
DECLARE @progressive money
	, @progressions int
	, @progressivetxt varchar(20)
	, @progressionstxt varchar(20)
	, @measurestart datetime
	, @measureend datetime
	, @lastdaytxt varchar(10)
	, @firstdaytxt varchar(10)
	, @Output varchar(3000)
	, @bcpCommand varchar(8000)

if @specifiedmeasurestart is not null
begin
	set @measurestart = @specifiedmeasurestart
end
else
begin
	set @measurestart = dateadd(hh,+3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
end
set @firstdaytxt = convert(varchar(10), @measurestart, 101)


if @specifiedmeasureend is not null
begin
	set @measureend = @specifiedmeasureend
end
else
begin
	set @measureend = dateadd(hh,-6,dateadd(mm,1+datediff(mm,0,getdate()),0))
end
set @lastdaytxt = convert(varchar(10), @measureend, 101)
--print @measurestart
--print @firstdaytxt
--print @measureend
--print @lastdaytxt


--Populate Table to hold last five qualifying jackpots
insert into gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5
	(DisplayDatetxt
	, SlotNumbertxt
	, Denominationtxt
	, Amounttxt
	, JackpotFillDate)
select top 5 
	isnull(convert(varchar(10),tx.MachineDateTime, 110),0),
        isnull(convert(varchar(6),tx.SlotID),0),
        isnull(convert(varchar(6),tx.Denomination),0),
        isnull(convert(money, tx.JackpotFillAmt),0),
        isnull(tx.MachineDateTime,0)
      from gmgoasissql.WinOasis.dbo.JF_JACKPOTFILL tx
	INNER JOIN usr_gmg_gjr_slotnumbers gjr
	ON tx.SlotID=gjr.SlotNumber
      Where tx.MachineDateTime > @measurestart
	and tx.MachineDateTime < @measureend
	and tx.JackpotFillAmt >= @increment_jpminimum
	and tx.TransType = 'J'
	and tx.IsCancelCredit = 'N'
	and tx.VoidedDateTime is null
      order by tx.MachineDateTime desc


--Populate Progressive Prize Variable
select @progressions = isnull(count(distinct tx.TicketNum), 0)
	,@progressive = isnull((count(distinct tx.TicketNum) * @increment_amount), 0)
      from gmgoasissql.WinOasis.dbo.JF_JACKPOTFILL tx
	INNER JOIN usr_gmg_gjr_slotnumbers gjr
	ON tx.SlotID=gjr.SlotNumber
      Where tx.MachineDateTime > @measurestart
	and tx.MachineDateTime < @measureend
	and tx.JackpotFillAmt >= @increment_jpminimum
	and tx.TransType = 'J'
	and tx.IsCancelCredit = 'N'
	and tx.VoidedDateTime is null

set @progressivetxt = REPLACE(   convert(varchar(20), @progressive, 1)  ,'.00', '')
set @progressionstxt = convert(varchar(20), @progressions)
--print @progressivetxt
--print @progressionstxt


--Variables for output of last 5
DECLARE @d1 varchar(10)
	, @n1 varchar(6)
	, @dn1 varchar(6)
	, @amt1 varchar(20)
	, @d2 varchar(10)
	, @n2 varchar(6)
	, @dn2 varchar(6)
	, @amt2 varchar(20)
	, @d3 varchar(10)
	, @n3 varchar(6)
	, @dn3 varchar(6)
	, @amt3 varchar(20)
	, @d4 varchar(10)
	, @n4 varchar(6)
	, @dn4 varchar(6)
	, @amt4 varchar(20)
	, @d5 varchar(10)
	, @n5 varchar(6)
	, @dn5 varchar(6)
	, @amt5 varchar(20)
SELECT @d1 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n1 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn1 = Denominationtxt  from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt1 = replace(  convert(varchar(20), Amounttxt, 1)  ,'.00', '')   from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)

DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d1
and SlotNumbertxt = @n1
and Denominationtxt = @dn1
and Amounttxt = convert(money, @amt1)

SELECT @d2 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n2 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn2 = Denominationtxt  from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt2 = replace(  convert(varchar(20), Amounttxt, 1)  ,'.00', '')   from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)

DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d2
and SlotNumbertxt = @n2
and Denominationtxt = @dn2
and Amounttxt = convert(money, @amt2)

SELECT @d3 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n3 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn3 = Denominationtxt  from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt3 = replace(  convert(varchar(20), Amounttxt, 1)  ,'.00', '')   from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)

DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d3
and SlotNumbertxt = @n3
and Denominationtxt = @dn3
and Amounttxt = convert(money, @amt3)

SELECT @d4 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n4 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn4 = Denominationtxt  from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt4 = replace(  convert(varchar(20), Amounttxt, 1)  ,'.00', '')   from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)

DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d4
and SlotNumbertxt = @n4
and Denominationtxt = @dn4
and Amounttxt = convert(money, @amt4)

SELECT @d5 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n5 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn5 = Denominationtxt  from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt5 = replace(  convert(varchar(20), Amounttxt, 1)  ,'.00', '')   from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)

DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d5
and SlotNumbertxt = @n5
and Denominationtxt = @dn5
and Amounttxt = convert(money, @amt5)



SET @Output = '&lastdaytxt=' + @lastdaytxt 
	+ '&firstdaytxt=' + @firstdaytxt 
	+ '&progressive=' + isnull(@progressivetxt,'0')
	+ '&progressions=' + isnull(@progressionstxt,'0')
	+ '&d1=' + isnull(@d1,'')
	+ '&n1=' + isnull(@n1,'')
	+ '&dn1=' + isnull(@dn1,'')
	+ '&amt1=' + isnull(@amt1,'')
	+ '&d2=' + isnull(@d2,'')
	+ '&n2=' + isnull(@n2,'')
	+ '&dn2=' + isnull(@dn2,'')
	+ '&amt2=' + isnull(@amt2,'')
	+ '&d3=' + isnull(@d3,'')
	+ '&n3=' + isnull(@n3,'')
	+ '&dn3=' + isnull(@dn3,'')
	+ '&amt3=' + isnull(@amt3,'')
	+ '&d4=' + isnull(@d4,'')
	+ '&n4=' + isnull(@n4,'')
	+ '&dn4=' + isnull(@dn4,'')
	+ '&amt4=' + isnull(@amt4,'')
	+ '&d5=' + isnull(@d5,'')
	+ '&n5=' + isnull(@n5,'')
	+ '&dn5=' + isnull(@dn5,'')
	+ '&amt5=' + isnull(@amt5,'')
--print @Output
--print @progressivetxt
--print @progressionstxt



--Create concentrated table of variables for display program
if exists (select * from dbo.sysobjects where id = object_id(N'usr_GoldenJackpotProgDisplay_Output') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output
CREATE TABLE gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output (DisplayVars VARCHAR(3000))
INSERT INTO gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output (DisplayVars) VALUES (@Output)

--run xp_cmdshell to bcp to export winning person info to text
SET @bcpCommand = 'bcp "SELECT * FROM gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output" queryout C:\sqlexporttemp\GoldenJackpotProg\output.txt -S gcgbhmktsql -U sa -P m@rk3t! -c'
SET NOCOUNT ON
EXEC master..xp_cmdshell @bcpCommand, NO_OUTPUT
EXEC master..xp_cmdshell 'cmd /C "C:\sqlexporttemp\GoldenJackpotProg\GJRPRogXsfer.bat"', NO_OUTPUT
SET NOCOUNT OFF

END
GO

Disclaimer:

Golden Casino Group properties ęGolden Gaming Inc. Affiliate and partner materials are property of the respective enterprise.