USE FrenchDB;
PRINT GETDATE()
------------------------------------------------------------------------------------------
-- ******** MAKE SURE THE YEAR IS CORRECTLY SET IN THE HORSE TABLE UPDATE CODE ******** --
------------------------------------------------------------------------------------------
IF OBJECT_ID('t_Dir') IS NOT NULL DROP TABLE t_Dir
;
CREATE TABLE t_Dir
(
D1900 INT NOT NULL,
WDay CHAR(3) NOT NULL,
MDay TINYINT NOT NULL,
Mth CHAR(3) NOT NULL,
Yr SMALLINT NOT NULL,
CrsID INT NOT NULL,
Hippodrome VARCHAR(32) NOT NULL,
Reg VARCHAR(2) NOT NULL,
Cat TINYINT NOT NULL,
NbMeet TINYINT,
QUH CHAR(1) NOT NULL,
WX VARCHAR(64),
Temp SMALLINT,
GenGng VARCHAR(64),
NbRce TINYINT NOT NULL,
RceTime CHAR(5),
RceID INT NOT NULL,
RaceName VARCHAR(128) NOT NULL,
Going VARCHAR(64),
RceTyp VARCHAR(1) NOT NULL,
Dist SMALLINT NOT NULL,
Class VARCHAR(9),
RceSex CHAR(1),
Sang CHAR(3),
EBF CHAR(4),
MnAge TINYINT,
MxAge TINYINT,
HcRef VARCHAR(9),
Dir CHAR(1) NOT NULL,
Surf VARCHAR(12),
Trk VARCHAR(3),
Box CHAR(1),
Pot TINYINT,
Lice SMALLINT,
Rider VARCHAR(13),
Prize1 INT NOT NULL,
Prize2 INT,
Prize3 INT,
Prize4 INT,
Prize5 INT,
Prize6 INT,
Prize7 INT,
Prize8 INT,
TotPrize INT NOT NULL,
Ran TINYINT NOT NULL,
WinSec DECIMAL(5,2),
RKWSec DECIMAL(5,2),
TotOSP DECIMAL(4,1),
TotPMU DECIMAL(4,1),
TotLT DECIMAL(4,1),
RceCond VARCHAR(2047)
)
;
PRINT 'Inserting data into t_Dir'
BULK INSERT t_Dir
FROM 'C:\Users\Administrator\Desktop\PT Data\Directory.txt'
WITH
(
CODEPAGE = 'ACP',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
;
ALTER TABLE t_Dir ADD DNew CHAR(1) NOT NULL DEFAULT('D')
;
IF OBJECT_ID('t_CID') IS NOT NULL DROP TABLE t_CID
;
SET NOCOUNT ON
SELECT RceID, CrsID AS CourseID
INTO t_CID
FROM t_Dir
SET NOCOUNT OFF
;
---------------------------------------------------------------------------------------------------
IF OBJECT_ID('t_Run') IS NOT NULL DROP TABLE t_Run
;
CREATE TABLE t_Run
(
RceID INT NOT NULL,
R1900 INT NOT NULL,
Gain INT,
FinPos VARCHAR(15) NOT NULL,
Nb TINYINT NOT NULL,
EcuSup VARCHAR(3),
Horse VARCHAR(25) NOT NULL,
Brd VARCHAR(3) NOT NULL,
ReclPr VARCHAR(6),
Gear VARCHAR(3),
HSex CHAR(1) NOT NULL,
Age TINYINT,
Draw TINYINT,
WtK DECIMAL(3,1),
WonBy DECIMAL(4,2),
DTP DECIMAL(4,2),
DTW DECIMAL(5,2),
Trainer VARCHAR(40) NOT NULL,
Jockey VARCHAR(40) NOT NULL,
OpnSP DECIMAL(5,1),
FinSP DECIMAL(6,2),
LTFSP DECIMAL(6,2),
PlcSP DECIMAL(6,2),
LTPlc DECIMAL(6,2),
Comments VARCHAR(256),
WtRnk TINYINT,
[OR] DECIMAL(3,1),
ORRnk TINYINT,
OSPRnk TINYINT,
FSPRnk TINYINT,
LTRnk TINYINT,
Ownr VARCHAR(64) NOT NULL,
Sire VARCHAR(25),
SireBrd VARCHAR(3),
Dam VARCHAR(25),
DamBrd VARCHAR(3),
DamSire VARCHAR(25),
DSBrd VARCHAR(3),
Breeder VARCHAR(127),
HorseID INT NOT NULL,
TrainerID INT NOT NULL,
JockeyID INT NOT NULL,
OwnrID INT NOT NULL,
SireID INT,
DamID INT,
DSID INT,
JClm DECIMAL(2,1),
exJck INT,
LdE VARCHAR(32),
DoB INT,
DoC INT
)
;
PRINT '------------------------------------' + CHAR(13) + 'Inserting data into t_Run'
BULK INSERT t_Run
FROM 'C:\Users\Administrator\Desktop\PT Data\Runners.txt'
WITH
(
CODEPAGE = 'ACP',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
;
ALTER TABLE t_Run ADD RNew CHAR(1) NOT NULL DEFAULT('R')
ALTER TABLE t_Run ADD CourseID INT NOT NULL DEFAULT(1)
;
SET NOCOUNT ON
MERGE t_Run AS T
USING t_CID AS S
ON S.RceID = T.RceID
WHEN MATCHED THEN
UPDATE SET T.CourseID = S.CourseID
;
SET NOCOUNT OFF
;
DROP TABLE t_CID
;
---------------------------------------------------------------------------------------------------
IF OBJECT_ID('t_Lnk') IS NOT NULL DROP TABLE t_Lnk
;
CREATE TABLE t_Lnk
(
RceID INT NOT NULL,
Link VARCHAR(255) NOT NULL,
NbRce TINYINT NOT NULL,
[Description] VARCHAR(1023)
)
;
PRINT '------------------------------------' + CHAR(13) + 'Inserting data into t_Lnk'
BULK INSERT t_Lnk
FROM 'C:\Users\Administrator\Desktop\PT Data\Links.txt'
WITH
(
CODEPAGE = 'ACP',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
;
ALTER TABLE t_Lnk ADD LNew CHAR(1) NOT NULL DEFAULT('L')
;
---------------------------------------------------------------------------------------------------
-- MAKE SURE THE YEAR IS CORRECTLY SET HERE (IN 3 PLACES)
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Horses table'
SET NOCOUNT ON
UPDATE Horses SET HNew = NULL WHERE HNew = 'H'
SET NOCOUNT OFF
;
MERGE Horses AS T
USING (SELECT DISTINCT HorseID, Horse, Brd, HSex, Age, Breeder, Sire, SireBrd, SireID, Dam, DamBrd, DamID, DamSire, DSBrd, DSID, DoB, DoC FROM t_Run) AS S
ON S.HorseID = T.HorseID
-- HERE
WHEN MATCHED THEN UPDATE SET T.LastYear = 2016
WHEN NOT MATCHED BY TARGET THEN
INSERT (HorseID, Horse, Bred, Sex, YOB, Breeder, Sire, SireBrd, SireID, Dam, DamBrd, DamID, DamSire, DSBrd, DSID, HNew, LastYear, DoB, DoC)
-- HERE
VALUES (S.HorseID, S.Horse, S.Brd, CASE WHEN S.HSex = 'H' THEN 'M' ELSE S.HSex END, 2016-S.Age, S.Breeder, S.Sire, S.SireBrd, S.SireID, S.Dam, S.DamBrd, S.DamID, S.DamSire, S.DSBrd, HERE
S.DSID, 'H', 2016, S.DoB, S.DoC)
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Jockeys table'
SET NOCOUNT ON
UPDATE Jockeys SET JNew = NULL WHERE JNew = 'J'
SET NOCOUNT OFF
;
MERGE Jockeys AS T
USING (SELECT DISTINCT JockeyID, Jockey FROM t_Run) AS S
ON S.JockeyID = T.JockeyID
WHEN NOT MATCHED BY TARGET THEN
INSERT (JockeyID, Jockey, JNew)
VALUES (S.JockeyID, S.Jockey, 'J')
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Owners table'
SET NOCOUNT ON
UPDATE Owners SET ONew = NULL WHERE ONew = 'O'
SET NOCOUNT OFF
;
MERGE Owners AS T
USING (SELECT DISTINCT OwnrID, Ownr FROM t_Run) AS S
ON S.OwnrID = T.OwnrID
WHEN NOT MATCHED BY TARGET THEN
INSERT (OwnrID, Ownr, ONew)
VALUES (S.OwnrID, S.Ownr, 'O')
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Trainers table'
SET NOCOUNT ON
UPDATE Trainers SET TNew = NULL WHERE TNew = 'T'
SET NOCOUNT OFF
;
MERGE Trainers AS T
USING (SELECT DISTINCT TrainerID, Trainer FROM t_Run) AS S
ON S.TrainerID = T.TrainerID
WHEN NOT MATCHED BY TARGET THEN
INSERT (TrainerID, Trainer, TNew)
VALUES (S.TrainerID, S.Trainer, 'T')
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Directory table'
SET NOCOUNT ON
UPDATE Directory SET DNew = NULL WHERE DNew = 'D'
SET NOCOUNT OFF
;
--IF Directory AND t_Dir HAVE A DIFFERENT NUMBER OF COLS, THE COLUMNS TO BE IMPORTED MUST BE SPECIFIED IN THE INSERT COMMAND AS WELL
--AT THE MOMENT THEY HAVE THE SAME NUMBER, BUT THAT MAY NOT ALWAYS BE THE CASE, SO THEY ARE SPECIFIED HERE TO ALLOW FOR FUTURE CHANGES
INSERT INTO Directory (D1900, WDay, MDay, Mth, Yr, CrsID, NbMeet, QUH, WX, Temp, GenGng, NbRce, RceTime, RceID, RaceName, Going, RceTyp, Dist, Class,
RceSex, Sang, EBF, MnAge, MxAge, HcRef, Dir, Trk, Box, Pot, Lice, Rider, Prize1, TotPrize, Ran, WinSec, RKWSec, TotOSP, TotPMU, TotLT, RceCond, DNew)
--OUTPUT INSERTED.RceID
SELECT D1900, WDay, MDay, Mth, Yr, CrsID, NbMeet, QUH, WX, Temp, GenGng, NbRce, RceTime, RceID, RaceName, Going, RceTyp, Dist, Class,
RceSex, Sang, EBF, MnAge, MxAge, HcRef, Dir, Trk, Box, Pot, Lice, Rider, Prize1, TotPrize, Ran, WinSec, RKWSec, TotOSP, TotPMU, TotLT, RceCond, DNew
FROM t_Dir
;
DROP TABLE t_Dir
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Links table'
SET NOCOUNT ON
UPDATE Links SET LNew = NULL WHERE LNew = 'L'
SET NOCOUNT OFF
;
INSERT INTO Links (RceID, Link, NbRce, [Description], LNew)
SELECT RceID, Link, NbRce, [Description], LNew
FROM t_Lnk
;
DROP TABLE t_Lnk
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating the Runners table'
SET NOCOUNT ON
UPDATE Runners SET RNew = NULL WHERE RNew = 'R'
SET NOCOUNT OFF
;
--AS Runners AND t_Run HAVE A DIFFERENT NUMBER OF COLS (Runners CONTAINS DSLR, LTO, LTO3, NTO3, 'z' COUNTER COLUMNS AND JOCKEY ALLOWANCES), THE COLUMNS TO BE IMPORTED MUST BE SPECIFIED IN THE INSERT COMMAND
INSERT INTO Runners (RceID, R1900, Gain, FinPos, Nb, EcuSup, ReclPr, Gear, Age, Draw, WtK, WonBy, DTP, DTW, OpnSP, FinSP, LTFSP, PlcSP, LTPlc, Comments, WtRnk, [OR], ORRnk,
OSPRnk, FSPRnk, LTRnk, HorseID, TrainerID, JockeyID, OwnrID, RNew, CourseID, HSex, JClm)
SELECT RceID, R1900, Gain, FinPos, Nb, EcuSup, ReclPr, Gear, Age, Draw, WtK, WonBy, DTP, DTW, OpnSP, FinSP, LTFSP, PlcSP, LTPlc, Comments, WtRnk, [OR], ORRnk,
OSPRnk, FSPRnk, LTRnk, HorseID, TrainerID, JockeyID, OwnrID, RNew, CourseID, HSex, JClm
FROM t_Run
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Calculating DSLR, LTO, LTO3 and NTO3'
IF OBJECT_ID('z_DSLR') IS NOT NULL DROP TABLE z_DSLR
;
SET NOCOUNT ON
SELECT HorseID, RceID, R1900, FinPos,
DSLR = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
THEN R1900 - LAG(R1900) OVER (PARTITION BY HorseID ORDER BY R1900)
ELSE 0
END,
LTO = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
THEN dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
ELSE '<fto>'
END,
LTO3 = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 3
THEN dbo.rfn_OneCharFinPos(LAG(FinPos,3) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LAG(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 2
THEN dbo.rfn_OneCharFinPos(LAG(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
THEN dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
ELSE '<fto>'
END,
NTO3 = CASE WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 2
THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LEAD(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LEAD(FinPos,3) OVER (PARTITION BY HorseID ORDER BY R1900))
WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
+ dbo.rfn_OneCharFinPos(LEAD(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 0
THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
ELSE '<lto>'
END
INTO z_DSLR
FROM Runners
WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run)
GROUP BY HorseID, RceID, R1900, FinPos
SET NOCOUNT OFF
;
MERGE Runners AS T
USING z_DSLR AS S
ON S.RceID = T.RceID AND S.HorseID = T.HorseID
WHEN MATCHED THEN
UPDATE SET
T.DSLR = S.DSLR, T.LTO = S.LTO, T.LTO3 = S.LTO3, T.NTO3 = S.NTO3
;
DROP TABLE z_DSLR
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating pOA'
IF OBJECT_ID('z_pOA') IS NOT NULL DROP TABLE z_pOA
;
WITH CTE AS
(SELECT Gear, HorseID, R1900, RN = ROW_NUMBER() OVER(PARTITION BY HorseID ORDER BY R1900)
FROM Runners
WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run) AND RIGHT(Gear,2) = 'OA'
)
SELECT * INTO z_pOA
FROM CTE
WHERE RN = 1
;
MERGE Runners AS T
USING z_pOA AS S
ON S.HorseID = T.HorseID AND S.R1900 = T.R1900 AND NOT S.Gear = 'pOA'
WHEN MATCHED THEN
UPDATE SET T.Gear = 'pOA'
;
DROP TABLE z_pOA
;
---------------------------------------------------------------------------------------------------
PRINT '------------------------------------' + CHAR(13) + 'Updating pO'
IF OBJECT_ID('z_pO') IS NOT NULL DROP TABLE z_pO
;
WITH CTE AS
(SELECT Gear, HorseID, R1900, RN = ROW_NUMBER() OVER(PARTITION BY HorseID ORDER BY R1900)
FROM Runners
WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run) AND RIGHT(Gear,1) = 'O'
)
SELECT * INTO z_pO
FROM CTE
WHERE RN = 1
;
MERGE Runners AS T
USING z_pO AS S
ON S.HorseID = T.HorseID AND S.R1900 = T.R1900 AND NOT S.Gear = 'pO'
WHEN MATCHED THEN
UPDATE SET T.Gear = 'pO'
;
DROP TABLE z_pO