Code Snippets

IMG_0716.PNG
FreeCode.jpeg

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