GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_RptNewAccVelocity]
@SubscriberId int,
@ReportLength int = null,
@Order char(30) = null
AS
BEGIN
-- SET
NOCOUNT ON added to prevent extra result sets from
--
interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@SQL_STATEMENT nvarchar(3000);
SET
@SQL_STATEMENT = 'SELECT
c.CreatedDate AS ''AccCreatedDate'', b.AccountId, c.AccountCode, a.DeviceId
FROM dbo.tblTransaction a WITH(NOLOCK) INNER JOIN ( ';
SET
@SQL_STATEMENT += 'SELECT
MIN(CreatedDate) AS CreatedDate, AccountId FROM dbo. tblTransaction WITH(NOLOCK)';
SET
@SQL_STATEMENT += 'WHERE
SubscriberId = '+ CAST(@SubscriberId
as varchar(20)) + ' ';
IF (@ReportLength IS
NOT NULL)
SET
@SQL_STATEMENT += 'AND
CreatedDate >= DATEADD(day, - '+ CAST(@ReportLength as varchar(20)) +', GETUTCDATE()) ';
SET
@SQL_STATEMENT += 'GROUP
BY AccountId ) b ON a.AccountId = b.AccountId AND a.CreatedDate = b.createdDate
';
SET
@SQL_STATEMENT += 'INNER
JOIN dbo.tblAccount c WITH(NOLOCK) ON c.AccountId=b.AccountId ';
SET
@SQL_STATEMENT += 'WHERE
a.SubscriberId = '+ CAST(@SubscriberId
as varchar(20)) + ' ';
IF (@ReportLength IS
NOT NULL)
SET
@SQL_STATEMENT += 'AND
c.CreatedDate >= DATEADD(day, - '+ CAST(@ReportLength as varchar(20)) +', GETUTCDATE()) ';
IF (@Order IS NOT NULL)
SET
@SQL_STATEMENT += 'ORDER
BY ' + CAST(@Order as varchar(30)) + ' ';
EXEC sp_executesql @SQL_STATEMENT; // print @SQL_Statement
END
