Code Snippets
Job Monster
Query your SQL Server Agent jobs to see what's running on your server.
SELECT
name
,sysjobs.job_id
,sysjobs.enabled
,CONVERT(VARCHAR(16), date_created, 120) date_created
,CONVERT(VARCHAR(16), date_modified,120) date_modified
,sysjobsteps.step_id
,sysjobsteps.step_name
,sysjobs.description
,LEFT(CAST(sysjobsteps.last_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),7,2) last_run_date
,
CASE
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 6
THEN SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
+':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
+':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),5,2)
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 5
THEN '0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
+':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
+':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),4,2)
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 4
THEN '00:'
+ SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
+':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 3
THEN '00:'
+'0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
+':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 2 THEN '00:00:' + CAST(sysjobsteps.last_run_time AS VARCHAR)
WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 1 THEN '00:00:' + '0'+ CAST(sysjobsteps.last_run_time AS VARCHAR)
END last_run_time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobsteps
ON sysjobs.job_id = sysjobsteps.job_id
ORDER BY sysjobs.enabled,sysjobs.name,sysjobsteps.step_id
Create Table: Warehouse Donations
Create a table script for a warehouse donations page.
USE [MinistryPlatform]
GO
/****** Object: Table [dbo].[PowerBI_Donations] Script Date: 8/28/2019 2:07:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Warehouse_Donations](
[PowerBI_Donation_ID] [int] IDENTITY(1,1) NOT NULL,
[Household_ID] [int] NULL,
[Date] [date] NULL,
[Amount] [money] NULL,
[Domain_ID] [int] NULL,
[Allow_Online_Giving] [bit] NULL,
[Donor_ID] [int] NULL,
CONSTRAINT [PK_PowerBI_Donations] PRIMARY KEY CLUSTERED
(
[PowerBI_Donation_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PowerBI_Donations] ADD CONSTRAINT [DF_PowerBI_Donations_Domain_ID] DEFAULT ((1)) FOR [Domain_ID]
GO
Stored Procedure: Warehouse Donations
Populate your Warehouse Donations table.
USE [MinistryPlatform]
GO
/****** Object: StoredProcedure [dbo].[service_Custom_PowerBI_Donations] Script Date: 8/28/2019 2:07:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[service_Custom_Warehouse_Donations]
AS
DELETE FROM Warehouse_Donations;
DBCC CHECKIDENT(Warehouse_Donations, RESEED,1)
insert into Warehouse_Donations
(Household_ID, Date, Amount, Allow_Online_Giving, donor_ID)
select c.Household_ID
,DO.Donation_Date
,dd.amount
,p.Allow_Online_Giving
,d.Donor_ID
from Donation_Distributions DD
inner join Donors D on D.Donor_ID = DD.Soft_Credit_Donor
inner join Contacts C on C.Contact_ID = D.Contact_ID
inner join Donations DO on DO.Donation_ID = DD.Donation_ID
inner join Programs P on DD.Program_ID = P.Program_ID
where dd.Soft_Credit_Donor is not null
AND do.Donation_Date >= DATEADD(year, -3, getdate())
insert into Warehouse_Donations
(Household_ID, Date, Amount, Allow_Online_Giving, donor_ID)
select c.Household_ID
,DO.Donation_Date
,dd.amount
,p.Allow_Online_Giving
,d.Donor_ID
from Donation_Distributions DD
inner join Donations DO on DO.Donation_ID = DD.Donation_ID
inner join Donors D on D.Donor_ID = DO.Donor_ID
inner join Contacts C on C.Contact_ID = D.Contact_ID
inner join Programs P on DD.Program_ID = P.Program_ID
where dd.Soft_Credit_Donor is null
AND do.Donation_Date >= DATEADD(year, -3, getdate())
Stored Procedure: People Page
This code snippet will help you get started writing a stored procedure that populates your People page.
USE [MinistryPlatform]
GO
/****** Object: StoredProcedure [dbo].[Service_Custom_MyChurch_People] Script Date: 6/15/2019 9:52:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[service_Custom_MyChurch_People]
AS
DELETE FROM People;
DBCC CHECKIDENT(People, RESEED,1)
INSERT INTO People
(Contact_ID, Household_ID, Participant_ID, Donor_ID, Address_ID, [User_ID], Domain_ID)
SELECT
C.Contact_ID, C.Household_ID, C.Participant_Record, C.Donor_Record, H.Address_ID, C.User_Account, 1
FROM Contacts C
left join Households H on H.Household_ID = C.Household_ID
WHERE C.Company = 0
UPDATE P
SET P.Display_Name = C.Display_Name
FROM People P
INNER JOIN Contacts C on C.Contact_ID = P.Contact_ID
​
UPDATE P
SET P.Spouse_Name = C2.Nickname
FROM People P
inner join Contacts C on C.Contact_ID = P.Contact_ID
inner join Households H on H.Household_ID = C.Household_ID
inner join Contacts C2 on C2.Household_ID = H.Household_ID
WHERE C.Household_Position_ID = 1 --head of household
AND c2.Household_Position_ID = 1 --head of household
AND C.Marital_Status_ID = 2 --married
AND P.Contact_ID <> C2.Contact_ID
Script: Create People Table
This script will create a People table in your database.
USE [MinistryPlatform]
GO
/****** Object: Table [dbo].[People] Script Date: 6/18/2019 6:51:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[People](
[People_ID] [int] IDENTITY(1,1) NOT NULL,
[Contact_ID] [int] NOT NULL,
[Household_ID] [int] NULL,
[Participant_ID] [int] NULL,
[Donor_ID] [int] NULL,
[Address_ID] [int] NULL,
[User_ID] [int] NULL,
[Display_Name] [nvarchar](200) NULL,
[Nickname] [nvarchar](200) NULL,
[Last_Name] [nvarchar](200) NULL,
[Gender] [nvarchar](200) NULL,
[Marital_Status] [nvarchar](200) NULL,
[Date_of_Birth] [date] NULL,
[Age] [int] NULL,
[Contact_Status] [nvarchar](200) NULL,
[Household_Position] [nvarchar](200) NULL,
[Email_Address] [nvarchar](200) NULL,
[Mobile_Phone] [nvarchar](200) NULL,
[Spouse_Name] [nvarchar](200) NULL,
[Address_Line_1] [nvarchar](200) NULL,
[Address_Line_2] [nvarchar](200) NULL,
[City] [nvarchar](200) NULL,
[State/Region] [nvarchar](200) NULL,
[Postal_Code] [nvarchar](200) NULL,
[Latitude] [nvarchar](200) NULL,
[Longitude] [nvarchar](200) NULL,
[Congregation] [nvarchar](200) NULL,
[Participant_Type] [nvarchar](200) NULL,
[Serving_Status] [nvarchar](200) NULL,
[Small_Group_Member_Status] [nvarchar](200) NULL,
[Small_Group_Leader_Status] [nvarchar](200) NULL,
[Ministry_Team_Leader_Status] [nvarchar](200) NULL,
[Giving_Last_30_Days] [money] NULL,
[Age_Category] [nvarchar](50) NULL,
[Generation] [nvarchar](50) NULL,
[Staff] [bit] NULL,
[Age_Group] [nvarchar](50) NULL,
[Domain_ID] [int] NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED
(
[People_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[People] ADD CONSTRAINT [DF_People_Domain_ID] DEFAULT ((1)) FOR [Domain_ID]
GO
​
Function: InitCap
This function formats character strings with first letter capitalized and remaining letters lower case. Example: Apple Banana Carrot
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
Function: GetNextDateByDayOfWeek
This function returns a date in the past or future relative to today and optionally append a time of day. Example: "Give me Sunday's date two weeks in the future from today."
CREATE FUNCTION [dbo].[fn_GetNextDateByDayOfWeek]
(@Day varchar(100) = 'Sunday'
,@LookType varchar(100) = 'ahead'
,@Time varchar(100) = '1:00 PM'
,@NumberOfWeeks int = 0)
returns datetime
as
begin
/*****************************************/
--Input variables (upper/lower case doesn't matter - the function takes care of that):
--Day of week you want to return (string): 'Sunday'
--If you want a day in the future or in the past (string): use 'ahead' or 'behind'
--Specify a time you want appended to the date (string): '8:56 AM'
--Number of weeks to look back or forward (int): 2
--Give me Sunday's date, two weeks from now, and set the time to 8:56 am:
--select dbo.fn_12Stone_GetNextDateByDayOfWeek('Sunday', 'ahead', '8:56 AM', 2)
/*****************************************/
--External variables
--declare @Day varchar(100) = 'thursday'
--declare @LookType varchar(100) = 'behind'
--declare @Time varchar(100) = '4:00 PM'
--declare @NumberOfWeeks int = 1
-- Internal variables
declare @Now datetime
declare @DayNumber int
declare @NowDayNumber int
declare @DayNumberDiff int
declare @FlatDate date
declare @ReturnDate datetime
-- Get now
select @Now = getdate()
-- Get now day day number
select @NowDayNumber = datepart(weekday, @Now)
-- Old school set up day
if (lower(@Day) = 'sunday')
set @DayNumber = 1
if (lower(@Day) = 'monday')
set @DayNumber = 2
if (lower(@Day) = 'tuesday')
set @DayNumber = 3
if (lower(@Day) = 'wednesday')
set @DayNumber = 4
if (lower(@Day) = 'thursday')
set @DayNumber = 5
if (lower(@Day) = 'friday')
set @DayNumber = 6
if (lower(@Day) = 'saturday')
set @DayNumber = 7
-- Setup a flat date
if (lower(@LookType) = 'ahead')
begin
if (@NowDayNumber = @DayNumber)
set @DayNumberDiff = 0
else
set @DayNumberDiff = (7 - @NowDayNumber) + @DayNumber
set @FlatDate = dateadd(day, @DayNumberDiff, convert(date, getdate()))
set @FlatDate = dateadd(week, @NumberOfWeeks, @FlatDate)
end
if (lower(@LookType) = 'behind')
begin
if (@NowDayNumber = @DayNumber)
set @DayNumberDiff = 0
if (@NowDayNumber <= @DayNumber)
set @DayNumberDiff = (@NowDayNumber - (@NowDayNumber - 1)) + (7 - @DayNumber) + 1
if (@NowDayNumber > @DayNumber)
set @DayNumberDiff = @NowDayNumber - @DayNumber
set @FlatDate = dateadd(day, -@DayNumberDiff, convert(date, getdate()))
set @FlatDate = dateadd(week, -@NumberOfWeeks, @FlatDate)
end
-- Set the return date
select @ReturnDate = convert(datetime, convert(varchar(100), @FlatDate) + ' ' + convert(varchar(100), @Time))
-- Return necessary
--select @ReturnDate
return @ReturnDate
end
JavaScript: Reload Page After 60 Seconds
This JavaScript snippet reloads the page after 60 seconds. Put it in the JS file for your portal skin.
if (window.location.href.indexOf("get_form.aspx?id=<guid>") > -1)
{
setTimeout(function () { window.location.href = "get_form.aspx?id=<guid>"; }, 60000);
}
SQL Query: Congregation Churn
This SQL query returns statistics on number of households in the front door and out the back door over the last year
select h.Household_ID
,(select count (do.donation_ID)
from Donations Do
inner join Donors D on D.Donor_ID = DO.Donor_ID
inner join Contacts C on C.Contact_ID = d.Contact_ID
where C.Household_ID = H.Household_ID
AND do.Donation_Date BETWEEN DATEADD(month, -24, GETDATE()) AND DATEADD(month, -12, getdate())) as Past_Donations
,(select count (do.donation_ID)
from Donations Do
inner join Donors D on D.Donor_ID = DO.Donor_ID
inner join Contacts C on C.Contact_ID = d.Contact_ID
where C.Household_ID = H.Household_ID
AND do.Donation_Date BETWEEN DATEADD(month, -12, GETDATE()) AND DATEADD(month, 0, getdate())) as Current_Donations
,(select count (ep.event_Participant_ID)
from Event_Participants EP
inner join Contacts C on C.Participant_Record = ep.Participant_ID
where C.Household_ID = H.Household_ID
AND EP._Setup_Date BETWEEN DATEADD(month, -24, GETDATE()) AND DATEADD(month, -12, getdate())) as Past_Events
,(select count (ep.event_Participant_ID)
from Event_Participants EP
inner join Contacts C on C.Participant_Record = ep.Participant_ID
where C.Household_ID = H.Household_ID
AND EP._Setup_Date BETWEEN DATEADD(month, -12, GETDATE()) AND DATEADD(month, 0, getdate())) as Current_Events
,(select count (gp.Group_Participant_ID)
from Group_Participants GP
inner join Contacts C on C.Participant_Record = gp.Participant_ID
where C.Household_ID = H.Household_ID
AND DATEADD(month, -12, getdate()) BETWEEN GP.Start_Date AND GP.End_Date) as Past_Groups
,(select count (gp.Group_Participant_ID)
from Group_Participants GP
inner join Contacts C on C.Participant_Record = gp.Participant_ID
where C.Household_ID = H.Household_ID
AND DATEADD(month, 0, getdate()) BETWEEN GP.Start_Date AND GP.End_Date) as Current_Groups
into #churn
from Households H
--select * from #churn
select count(c.household_ID)
from #churn c
where c.Past_Donations >= 3
AND C.Current_Donations = 0
--And c.Past_Events >= 1
--AND c.Current_Events = 0
--AND C.Past_Groups >= 1
--AND C.Current_Groups = 0
select count(c.household_ID)
from #churn c
where c.Past_Donations >= 3
select count(c.household_ID)
from #churn c
where c.Past_Donations = 0
AND C.Current_Donations >= 3
select count(c.household_ID)
from #churn c
where c.Current_Donations >= 3
drop table #churn
SQL Function: Fiscal Week
Plug in a two-digit month that is the start of your fiscal year and a date, and this function returns the fiscal week number.
Example: select dbo.FiscalWeek('07', getdate())
USE [MinistryPlatform]
GO
/****** Object: UserDefinedFunction [dbo].[FiscalWeek] Script Date: 2/4/2020 11:45:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[FiscalWeek] (@startMonth varchar(2), @DateToFind datetime) returns int
as
Begin
--@StartMonth = > The month number of the start start of fiscal period
--@DateToFind = > The date that you want to find the fiscal week number
declare @firstWeek datetime
declare @weekNum int
declare @year int
--Step 1
set @year = datepart(year, @DateToFind)+1
--Step 2: you are taking the 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
--Step 3: Retreat to beginning of the week for the date
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
--Step 4:
--This allows you to pass in any date with year to find the fiscal week number
while @DateToFind < @firstWeek
--Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = ((datediff(day, @firstweek, @DateToFind)/7)+1)
return @weekNum
END