USP_DATALIST_PROSPECT_WPSECURITYTRANSACTIONS

Displays a list of transactions for a WealthPoint Securities record.
Parameters

Parameter	Parameter Type	Mode	Description
@WPSECURITIESID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@NUMBERTOSHOWCODE	smallint	IN	Show
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_PROSPECT_WPSECURITYTRANSACTIONS(
                    @WPSECURITIESID uniqueidentifier,
                    @NUMBERTOSHOWCODE smallint = 1
                )
                as begin
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    if @NUMBERTOSHOWCODE in (2,3)
                        begin
                            declare @STARTDATE datetime;

                            if @NUMBERTOSHOWCODE = 2 -- Last Year

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)));
                            else if @NUMBERTOSHOWCODE = 3 -- Las 5 Years

                                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-5,@CURRENTDATE)));


                            declare @ENDDATE datetime;
                            set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);


                            select 
                                ID,
                                AMOUNT,
                                DATEFROM,
                                DATETO,
                                BB_PRICE,
                                AMOUNTTYPE,
                                TRANSDESC,
                                TRANSVALUE,
                                BB_TRANSVALUE,
                                TRANSTYPE
                            from
                                dbo.WPSECURITIESTRANSACTION
                            where
                                WPSECURITIESID = @WPSECURITIESID and
                                (DATEFROM >= @STARTDATE and DATEFROM <= @ENDDATE)
                            order by
                                DATEFROM desc
                        end
                    else
                        begin
                            declare @NUMBERTOSHOW int;

                            if @NUMBERTOSHOWCODE = 1  
                                set @NUMBERTOSHOW = 20
                            else
                                set @NUMBERTOSHOW = (select count(ID) from dbo.WPSECURITIESTRANSACTION where WPSECURITIESID = @WPSECURITIESID)

                            select top(@NUMBERTOSHOW)
                                ID,
                                AMOUNT,
                                DATEFROM,
                                DATETO,
                                BB_PRICE,
                                AMOUNTTYPE,
                                TRANSDESC,
                                TRANSVALUE,
                                BB_TRANSVALUE,
                                TRANSTYPE
                            from
                                dbo.WPSECURITIESTRANSACTION
                            where
                                WPSECURITIESID = @WPSECURITIESID
                            order by
                                DATEFROM desc

                        end

                end