Hello.
I have got an app which has a SQL Select query to select fields and populate a table for a report.
For the front page of the NPrinting report, I need to create a variable for the max date for a specific field. However, I do not know how to create a variable using SQL script.
Load*;
SQL select dp.Number
,dp.InceptionDate
,dp.EffectiveDate
,dp.ExpiryDate
,dp.CancellationDate
,datediff(year,dp.InceptionDate,dp.EffectiveDate) as TermNumber
,dlob.LobCd
,dlob.LobDesc
,SUM(fp.GWP) AS GrossWrittenPremium
,SUM(fp.NWP) AS NetWrittenPremium
,SUM(fp.Commission) AS Commission
from [GBQPS1SDA01DMA].[cdm].[FactPrm] fp
join [GBQPS1SDA01DMA].[cdm].[DLOB] dlob on fp.FKLineOfBusinessOID = dlob.LineOfBusinessOID
join [GBQPS1SDA01DMA].[cdm].[Dim] dp on fp.FKOID = dp.OID
and dp.CurrentRecordIndicator = '1'
join [GBQPS1SDA01DMA].[cdm].[DIMPrT] dpt on fp.FKTrOID = dpt.PrmTOID
and dpt.CurrentRecordIndicator = '1'
where 1=1
and dlob.LobCd IN ('B0100', 'B0098', 'B0099')
AND dp.EffectiveDate = dp.ExpiryDate
GROUP BY dp.Number
,dp.InceptionDate
,dp.EffectiveDate
,dp.ExpiryDate
,dp.CancellationDate
,datediff(year,dp.InceptionDate,dp.EffectiveDate)
,dlob.LobCd
,dlob.LobDesc
HAVING SUM(fp.GWP) <> 0;
exit script;
I wanted to create a variable to pass through to NPrinting which gives me the Max value of EffectiveDate, so essentially;
Let vDateTO = Date(Max([dp.EffectiveDate],'DD-MM-YYYY')); - however this seems to not work,
Thanks
