Good day
Please help.
I have a column 'CarHirePrem' that I've hardcoded in the script. The script works but when I copy the results into an excel sheet, I cannot sum the column as it's not in the number format.
How do I format this column in a script? please see the script below:
SELECT dbo.TrnPolicy.ClientID + ''*'' + dbo.TrnPolicy.FileNo AS PolicyNo, dbo.TrnPolicy.Status AS PolicyStatus,
dbo.TrnPolicy.LinkID + '' '' + ISNULL(dbo.MstLink.LinkName, ''Unknown'') AS LinkIDName, dbo.TrnPolicy.InceptionDate,
dbo.TrnPolicy.RenewalDate,
dbo.TrnPolicy.EndDate, dbo.TrnClient.Title, dbo.TrnClient.Initials,
dbo.TrnClient.ClientName AS Surname,
dbo.TrnPolicy.ResAddr1 AS StreetAddress, dbo.TrnPolicy.ResAddr2 AS StreetArea, dbo.TrnPolicy.ResAddr3 AS StreetCity,
dbo.TrnPolicy.ResPostalID AS StreetCode, dbo.TrnClient.ClientAddr1 AS PostalAddress,
dbo.TrnClient.ClientAddr2 AS PostalArea,
dbo.TrnClient.ClientAddr3 AS PostalCity, dbo.TrnClient.ClientPostalID AS PostalCode,
dbo.TrnClient.TelHome, dbo.TrnClient.TelWork,
dbo.TrnClient.TelCell, dbo.TrnClient.Email,
dbo.TrnRisk.VehMake,
dbo.TrnRisk.VehModel, TrnRisk.VehYear,
dbo.TrnRisk.Registration, dbo.TrnRisk.VinNumber,
TrnRisk.CoverID, MstCover.CoverDescr, ''70'' as CarHirePrem
FROM dbo.TrnPolicy INNER JOIN
dbo.TrnClient ON dbo.TrnPolicy.System = dbo.TrnClient.System AND dbo.TrnPolicy.ClientID = dbo.TrnClient.ClientID
INNER JOIN TrnRisk ON dbo.TrnPolicy.System = dbo.TrnRisk.System AND dbo.TrnPolicy.ClientID = dbo.TrnRisk.ClientID AND
dbo.TrnPolicy.FileNo = dbo.TrnRisk.FileNo
LEFT JOIN MstCover ON dbo.MstCover.System = dbo.TrnRisk.System AND dbo.MstCover.CoverID = dbo.TrnRisk.CoverID AND
dbo.MstCover.SectionID = dbo.TrnRisk.SectionID
LEFT JOIN MstLink ON dbo.MstLink.System = dbo.TrnPolicy.System AND dbo.MstLink.LinkID = dbo.TrnPolicy.LinkID
WHERE (dbo.TrnPolicy.Status IN (''A'', ''R'')) AND TrnRisk.RiskEndDate IS NULL
AND TrnRisk.RiskStartDate IS NOT NULL
AND TrnRisk.SectionID = ''05''
AND TrnRisk.CoverID = ''01''
AND '+ @SelectClause + @WhereClause + @OrderByClause+'
-- Use Select clause as per parameters supplied
Group By dbo.TrnPolicy.ClientID + ''*'' + dbo.TrnPolicy.FileNo, dbo.TrnPolicy.Status,
dbo.TrnPolicy.LinkID + '' '' + ISNULL(dbo.MstLink.LinkName, ''Unknown''), dbo.TrnPolicy.InceptionDate,
dbo.TrnPolicy.RenewalDate,
dbo.TrnPolicy.EndDate, dbo.TrnClient.Title, dbo.TrnClient.Initials,
dbo.TrnClient.ClientName,
dbo.TrnPolicy.ResAddr1, dbo.TrnPolicy.ResAddr2, dbo.TrnPolicy.ResAddr3,
dbo.TrnPolicy.ResPostalID , dbo.TrnClient.ClientAddr1,
dbo.TrnClient.ClientAddr2,
dbo.TrnClient.ClientAddr3, dbo.TrnClient.ClientPostalID,
dbo.TrnClient.TelHome, dbo.TrnClient.TelWork,
dbo.TrnClient.TelCell, dbo.TrnClient.Email,
dbo.TrnRisk.VehMake,
dbo.TrnRisk.VehModel, TrnRisk.VehYear,
dbo.TrnRisk.Registration, dbo.TrnRisk.VinNumber,
TrnRisk.CoverID, MstCover.CoverDescr
Thanks
Matlotlo