Avatar billede dragnor Juniormester
01. februar 2019 - 07:23 Der er 1 kommentar

Master Data Services Custom Business Rule

Hej eksperter,

Er der nogen af jer som har styr på MDS?

jeg har prøvet at stille følgende spørgsmål på Technet men ingen reagere på spørgsmålet, så nu prøver jeg her :-)

https://social.technet.microsoft.com/Forums/en-US/5aaf98de-801e-4485-80f3-d912eec718ff/custom-business-rule-help?forum=sqlmds

Mit spørgsmål kan ses nedenfor:

Hi,

Currently i'm testing MDS, and are new to Master Data Services. (I have taken and online Microsoft Course)

Today we have our own home built tool for Master Data.

But we want to replace it with MDS.

I need to create a Custom Business Rule, but I'm really unsure how to built it and use it.

My challenge is that I have and ID field/ATTRIBUTE, let's call it TEAM_ID. That is a number, let's say that for one record that the TEAM_ID=100.

The same record also have two other fields/ATTRIBUTES, START_DATE + END_DATE.

Sample:

Code          TEAM_ID          START_DATE          END_DATE

1                100                2019-01-01          2019-12-31

2                100                2020-01-01          2020-06-01

3                100                2020-05-01          2100-12-31

I need a Business Rule that checks that:

START_DATE <= END_DATE
That the period between START_DATE and END_DATE are not overlapping any of the other periods where TEAM_ID=100 (I the sample above where Code=2 the END_DATE later that the START_DATE for Code=3, and that is not okay)
How would I built something like that?
Avatar billede dragnor Juniormester
01. februar 2019 - 08:47 #1
Fandt selv løsningen :-)

USE [MDS]
GO
/****** Object:  UserDefinedFunction [usr].[f_TeamValidate_Period]    Script Date: 01-02-2019 08:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [usr].[f_TeamValidate_Period]
(
    -- Add the parameters for the function here
    @value nvarchar(250)
)
RETURNS BIT
AS
BEGIN

declare @Code as nvarchar(250)
declare @TEAM_ID as decimal(38,0)
declare @Start_Date as datetime2(3)
declare @END_DATE as datetime2(3)
declare @Test_Result as int
declare @Test_Result_C as int

declare @Test_TEAM_ID as decimal(38,0)
declare @Test_Start_Date as datetime2(3)
declare @Test_END_DATE as datetime2(3)

Set @Test_TEAM_ID=0;
set @Test_Result_C=0;

SELECT @Test_TEAM_ID=[TEAM_ID], @Test_Start_Date=[Start_Date], @Test_END_DATE=[END_DATE]
FROM [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
WHERE [Code]=@value


if @Test_TEAM_ID>0
begin

    declare cur CURSOR LOCAL FAST_FORWARD
    for SELECT [Code]
          ,[TEAM_ID]
          ,[Start_Date]
          ,[END_DATE]
        FROM [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
        WHERE [TEAM_ID]=@Test_TEAM_ID and [Code] <> @value
    open cur

    fetch next from cur
    into @Code,@TEAM_ID,@Start_Date,@END_DATE

    while @@FETCH_STATUS = 0
    BEGIN
        SET @Test_Result=0;

        SELECT    @Test_Result=COUNT(*)
        FROM    [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
        WHERE    [TEAM_ID]=@Test_TEAM_ID and
                [Code] <> @value AND
                (
                    @Test_Start_Date BETWEEN [Start_Date] AND [END_DATE]
                    OR
                    @Test_END_DATE BETWEEN [Start_Date] AND [END_DATE]
                )

        if @Test_Result>0
        begin
            set @Test_Result_C = @Test_Result_C +1;
        end

        fetch next from cur
        into @Code,@TEAM_ID,@Start_Date,@END_DATE
    END
    close cur
    deallocate cur

end

if @Test_Result_C>0
begin
    set @Test_Result = 1;
end

RETURN @Test_Result

END
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester



IT-JOB