LVS_CONT_RECENT_DT

 

I don’t quite know the purpose of this view, but this identifies the highest ref_no for a customer, which is likely but not necessarily the latest contribution.  Also it doesn’t return any interesting fields for an output set.  This might work better for you.

BWG

 

CREATE View [dbo].[LV_CONTRIBUTION_LAST]

 

AS

SELECT

  a.customer_no,

  dbo.LF_GET_midnight (max(a.cont_dt)) 'last_cont_dt',

  sum(a.cont_amt) 'last_cont_amt',

  max(b.description) 'last_fund_desc',

  MAX(b.fund_no) 'last_fund_no',

  MAX(a.channel) 'last_sales_channel_no',

  max(c.description) 'last_sales_channel'

from [dbo].T_CONTRIBUTION a

join t_fund b on a.fund_no = b.fund_no

join tr_sales_channel c on a.channel = c.id

where a.cont_dt = (select max(b.cont_dt)

                  from t_contribution b

                  where b.customer_no = a.customer_no)

 

GROUP BY a.customer_no

 

 

And if you need the function used above:

 

CREATE FUNCTION [dbo].[LF_GET_MIDNIGHT] (

      @InDate datetime

)

RETURNS datetime

AS

BEGIN

 

declare @OutDate datetime

-- add input date to base date, time set to 0/midnight

SELECT @OutDate = DATEADD

(     day,

      DATEDIFF

      ( day,0, @InDate

      ),

      0

);

return @OutDate

END

GO

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Echert
Sent: Friday, October 04, 2013 1:42 PM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] LVS_CONT_RECENT_DT

 

Hi John,

That's correct. You'll need your DBA to create the database view before you can reference it in your output set elements.

In general I think the naming convention is that any time you have a table or a view starting with "L" it refers to something Local, i.e. a customization rather than one of the out-of-the-box Tessitura tables, views, etc. Something prefixed with LT_ is a local table, LV_ is a local view, LVS_ is a local secure view, and so on.

I was trying to use LVS_CONT_RECENT_DT in an output set based on something in the updated cookbook for v.11, and come to find out that is not one of our three views.    I am in dev, so I've never set up a view, but I guess that's what this does (which follows after the output set bits I'm familiar with)?

 

800x600

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

USE [impresario]

GO

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

ALTER VIEW [dbo].[LVS_CONT_RECENT_DT]

AS

SELECT a.customer_no, MAX(a.ref_no) AS ref_no

FROM  dbo.VS_CONTRIBUTION AS a

       JOIN VS_FUND b on a.fund_no = b.fund_no             

WHERE (a.recd_amt > 0) AND (b.desig_code IN (##,##))-- ## your desig_codes

GROUP BY a.customer_no

GO

 

GRANT REFERENCES, SELECT on [LVS_CONT_RECENT_DT] to impusers

GO

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

 

I don’t quite know the purpose of this view, but this identifies the highest ref_no for a customer, which is likely but not necessarily the latest contribution.  Also it doesn’t return any interesting fields for an output set.  This might work better for you.

BWG

 

CREATE View [dbo].[LV_CONTRIBUTION_LAST]

 

AS

SELECT

  a.customer_no,

  dbo.LF_GET_midnight (max(a.cont_dt)) 'last_cont_dt',

  sum(a.cont_amt) 'last_cont_amt',

  max(b.description) 'last_fund_desc',

  MAX(b.fund_no) 'last_fund_no',

  MAX(a.channel) 'last_sales_channel_no',

  max(c.description) 'last_sales_channel'

from [dbo].T_CONTRIBUTION a

join t_fund b on a.fund_no = b.fund_no

join tr_sales_channel c on a.channel = c.id

where a.cont_dt = (select max(b.cont_dt)

                  from t_contribution b

                  where b.customer_no = a.customer_no)

 

GROUP BY a.customer_no

 

 

And if you need the function used above:

 

CREATE FUNCTION [dbo].[LF_GET_MIDNIGHT] (

      @InDate datetime

)

RETURNS datetime

AS

BEGIN

 

declare @OutDate datetime

-- add input date to base date, time set to 0/midnight

SELECT @OutDate = DATEADD

(     day,

      DATEDIFF

      ( day,0, @InDate

      ),

      0

);

return @OutDate

END

GO

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Echert
Sent: Friday, October 04, 2013 1:42 PM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] LVS_CONT_RECENT_DT

 

Hi John,

That's correct. You'll need your DBA to create the database view before you can reference it in your output set elements.

In general I think the naming convention is that any time you have a table or a view starting with "L" it refers to something Local, i.e. a customization rather than one of the out-of-the-box Tessitura tables, views, etc. Something prefixed with LT_ is a local table, LV_ is a local view, LVS_ is a local secure view, and so on.

I was trying to use LVS_CONT_RECENT_DT in an output set based on something in the updated cookbook for v.11, and come to find out that is not one of our three views.    I am in dev, so I've never set up a view, but I guess that's what this does (which follows after the output set bits I'm familiar with)?

 

800x600

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

USE [impresario]

GO

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

ALTER VIEW [dbo].[LVS_CONT_RECENT_DT]

AS

SELECT a.customer_no, MAX(a.ref_no) AS ref_no

FROM  dbo.VS_CONTRIBUTION AS a

       JOIN VS_FUND b on a.fund_no = b.fund_no             

WHERE (a.recd_amt > 0) AND (b.desig_code IN (##,##))-- ## your desig_codes

GROUP BY a.customer_no

GO

 

GRANT REFERENCES, SELECT on [LVS_CONT_RECENT_DT] to impusers

GO

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!