USP_DATALIST_CONSTITUENT_SURVEYS
Displays a list of surveys for a given constituent record.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @SENTDATEFILTER | tinyint | IN | Send date |
| @SENTSTARTDATE | datetime | IN | Start date |
| @SENTENDDATE | datetime | IN | End date |
| @RESPONSEDATEFILTER | tinyint | IN | Response date |
| @RESPONSESTARTDATE | datetime | IN | Start date |
| @RESPONSEENDDATE | datetime | IN | End date |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_SURVEYS
(
@CONSTITUENTID uniqueidentifier,
@SENTDATEFILTER tinyint = 0,
@SENTSTARTDATE datetime = null,
@SENTENDDATE datetime = null,
@RESPONSEDATEFILTER tinyint = 0,
@RESPONSESTARTDATE datetime = null,
@RESPONSEENDDATE datetime = null
)
as
begin
set nocount on;
declare @DATE datetime;
set @DATE= getdate();
if @SENTDATEFILTER = 0 -- all
begin
set @SENTSTARTDATE = null;
set @SENTENDDATE = null;
end
if @SENTDATEFILTER = 1 -- this year
begin
set @SENTSTARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 2 -- this quarter
begin
set @SENTSTARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 3 -- this month
begin
set @SENTSTARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 4 -- this week
begin
set @SENTSTARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 5 -- next week
begin
set @SENTSTARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 6 -- next month
begin
set @SENTSTARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 7 -- next quarter
begin
set @SENTSTARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0);
end
if @SENTDATEFILTER = 8 -- next year
begin
set @SENTSTARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @SENTENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0);
end;
if @RESPONSEDATEFILTER = 0 -- all
begin
set @RESPONSESTARTDATE = null;
set @RESPONSEENDDATE = null;
end
if @RESPONSEDATEFILTER = 1 -- this year
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 2 -- this quarter
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 3 -- this month
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 4 -- this week
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 5 -- next week
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 6 -- next month
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 7 -- next quarter
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0);
end
if @RESPONSEDATEFILTER = 8 -- next year
begin
set @RESPONSESTARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @RESPONSEENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0);
end;
set @SENTSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@SENTSTARTDATE);
set @SENTENDDATE = dbo.UFN_DATE_GETLATESTTIME(@SENTENDDATE);
set @RESPONSESTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@RESPONSESTARTDATE);
set @RESPONSEENDDATE = dbo.UFN_DATE_GETLATESTTIME(@RESPONSEENDDATE);
select
SURVEY.ID,
SURVEY.DATESENT,
SURVEY.DATERESPONDED,
SURVEY.NAME,
dbo.UFN_SURVEYCATEGORYCODE_GETDESCRIPTION(SURVEY.SURVEYCATEGORYCODEID) as SURVEYCATEGORYCODE,
(select count(ID) from dbo.SURVEYRESPONSE where SURVEYRESPONSE.SURVEYID = SURVEY.ID) as QUESTIONCOUNT
from
dbo.SURVEY
where
SURVEY.CONSTITUENTID = @CONSTITUENTID
and
(
(SURVEY.DATESENT between @SENTSTARTDATE and @SENTENDDATE)
or
(@SENTSTARTDATE is null and @SENTENDDATE is null)
or
(SURVEY.DATESENT >= @SENTSTARTDATE and @SENTENDDATE is null)
or
(@SENTSTARTDATE is null and SURVEY.DATESENT <= @SENTENDDATE)
)
and
(
(SURVEY.DATERESPONDED between @RESPONSESTARTDATE and @RESPONSEENDDATE)
or
(@RESPONSESTARTDATE is null and @RESPONSEENDDATE is null)
or
(SURVEY.DATERESPONDED >= @RESPONSESTARTDATE and @RESPONSEENDDATE is null)
or
(@RESPONSESTARTDATE is null and SURVEY.DATERESPONDED <= @RESPONSEENDDATE)
)
order by
SURVEY.DATESENT, SURVEY.NAME
end