您的位置: 首页  >  IT文章  >  西方一些特殊节日的计算,该如何解决

西方一些特殊节日的计算,该如何解决

分类: IT文章 • 2023-11-03 10:56:06
西方一些特殊节日的计算
刚刚写的，提取了一部分。比如复活节等。
关于性能， 我也知道分开每个写一个function好的多，呵呵。
旨在重用、动态加载、程序结构上的一个设计思想。


SQL code

/*
fcuandy
2011.3.23
this can be stored in file system or data table.
*/
DECLARE @x_SPEC XML
SET @x_SPEC =
    '
<root>
    <item>
        <id>1</id>
        <key>Passover</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@hy INT
            ,@mat INT
            ,@leap INT
            ,@fday NUMERIC(10,2)
            ,@ffday NUMERIC(10,2)
            ,@dow INT
            ,@cent INT
            ,@leapExp INT
            ,@day INT
            ,@mo INT
            ,@PassoverDay DATETIME
        SET @y=@year
        SET @hy=@y + 3760
        SET @mat = (12 * @hy + 17) %19
        SET @leap = @hy %4
        SET @fday = 32 + 4343 / 98496.0 + @mat + @mat * (272953 / 492480.0) + @leap / 4.0
        SET @fday = @fday - @hy * (313 / 98496.0)
        SET @ffday = @fday - CAST(@fday AS INT)
        SET @dow = (3 * @hy + 5*@leap + CAST(@fday AS INT) + 5) % 7

        IF @dow IN (2,4,6)
            SET @fday = @fday + 1
        ELSE IF @dow=1 AND @mat>6 AND @ffday>= 1367 / 2160.0
            SET @fday = @fday + 2
        ELSE IF @dow=0 AND @mat>11 AND @ffday>=23269 / 25920.0
            SET @fday = @fday + 1

        SET @cent = CAST(@y/100.0 AS INT)
        SET @leapExp = CAST((3 * @cent - 5)/4.0 AS INT)
        IF @y > 1582
            SET @fday = @fday + @leapExp


        SET @day = CAST(@fday AS INT)
        SET @mo = 3

        IF @day>153
        BEGIN
            SET @mo = 8
            SET @day = @day - 153
        END
        ELSE IF @day>122
        BEGIN
            SET @mo = 7
            SET @day = @day -122
        END
        ELSE IF @day > 92
        BEGIN
            SET @mo = 6
            SET @day = @day - 92
        END
        ELSE IF @day > 61
        BEGIN
            SET @mo = 5
            SET @day = @day - 61
        END
        ELSE IF @day > 31
        BEGIN
            SET @mo = 4
            SET @day = @day - 31
        END

        SET @PassoverDay = RTRIM(@y) + ''-''+RTRIM(@mo) + ''-'' + RTRIM(@Day)
        SET @outDay = @PassoverDay
        </script>
    </item>
    <item>
        <id>2</id>
        <key>Easter</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@cent INT
            ,@i INT
            ,@j INT
            ,@k INT
            ,@met INT
            ,@emo INT
            ,@eday INT
            ,@Easter DATETIME
        SET @y=@year
        SET @cent = @y /100
        SET @met = @y % 19
        SET @k = (@cent - 17) / 25
        SET @i = (@cent - @cent / 4 - (@cent - @k)/3 + 19 * @met + 15)%30
        SET @i = @i - (@i/28) * (1 - (@i/28) * (29/(@i+1)) * ((21-@met)/11))
        SET @j = (@y + @y/4 + @i + 2 - @cent + @cent/4 ) % 7
        SET @emo = 3 + (@i-@j + 40) / 44
        SET @eday = @i - @j + 28 - 31 * (@emo / 4)

        SET @Easter = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday)
        SET @outDay = @Easter
        </script>
    </item>
    <item>
        <id>3</id>
        <key>OEaster</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@i INT
            ,@j INT
            ,@met INT
            ,@emo INT
            ,@eday INT
            ,@leap INT
            ,@OEaster DATETIME
            
        SET @y = @year
        SET @leap = @y /100 - @y / 400 -2
        SET @met = @y % 19
        SET @i = (19 * @met + 15) % 30
        SET @j = (@y + @y /4 + @i) % 7
        SET @emo = 3 + (@i - @j + 40) / 44
        SET @eday = @i - @j + 28 - 31 * (@emo /4)

        SET @OEaster = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday)
        SET @outDay = @OEaster
        </script>
    </item>
    <item>
        <id>4</id>
        <key>Chanukah</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@days INT
            ,@PassoverD1 DATETIME
            ,@PassoverD2 DATETIME
            ,@Chanukah DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
            
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key
        
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
        
        SET @y = @y + 1
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT
        
        SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2)
        
        IF @days IN (355, 385)
            SET @outDay = DATEADD(dd,246,@PassoverD1)
        ELSE
            SET @outDay = DATEADD(dd,245,@PassoverD1)
        
        </script>
    </item>
    <item>
        <id>5</id>
        <key>TishaBAv</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@dw INT
            ,@Passover DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                    
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key
                
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@Passover OUT

        SET @dw = DATEPART(DW,@Passover)
        IF @dw = 7
            SET @outDay = DATEADD(dd,113, @Passover)
        ELSE
            SET @outDay = DATEADD(dd,112, @Passover)
        </script>
    </item>
    <item>
        <id>6</id>
        <key>TuBishvat</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@days INT
            ,@PassoverD1 DATETIME
            ,@PassoverD2 DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                    
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key

        SET @y = @y - 1
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
        SET @y = @year
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT

        SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2)
        IF @days > 355
            SET @outDay = DATEADD(dd,-89, @PassoverD2)
        ELSE
            SET @outDay = DATEADD(dd,-59, @PassoverD2)
        </script>
    </item>
    <item>
        <id>7</id>
        <key>YomHaAtzmaut</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@dw INT
            ,@Passover DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                    
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key

        IF @y = 2004
            SET @outDay = CONVERT(DATETIME,''2004-04-27'',120)
        ELSE
        BEGIN
            SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
            EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@Passover OUT
            SET @dw = DATEPART(DW,@Passover)
            IF @dw = 1
                SET @outDay = DATEADD(dd,18,@Passover)
            ELSE IF @dw = 7
                SET @outDay = DATEADD(dd,19,@Passover)
            ELSE
                SET @outDay = DATEADD(dd,20,@Passover)
        END
        </script>
    </item>
</root>    
    '

DECLARE @t_res TABLE(
    hType VARCHAR, --F,M,S,O
    yy INT,
    hKey VARCHAR(40),
    hDate DATETIME,
    wDay AS DATEPART(DW,hDate) 
)

DECLARE @key VARCHAR(40)
    ,@refKey VARCHAR(40)
    ,@year INT
    ,@sql NVARCHAR(2000)
    ,@hDay DATETIME

SET @year= 2010  --param from outside

/*
INSERT holidays SPEC
*/
DECLARE @i INT, @cnt INT
SELECT @i = 1, @cnt = @x_SPEC.value('count(//key)','INT')

WHILE @i<= @cnt
BEGIN
    SELECT    @sql=T.x.value('script[1]','NVARCHAR(2000)') , 
            @key = T.x.value('key[1]','VARCHAR(40)'),
            @refKey = T.x.value('refKey[1]','VARCHAR(40)')
        /*FROM @x_SPEC.nodes('root/item[position()=sql:variable("@i")]') AS T(x)*/
        FROM @x_SPEC.nodes('root/item[id=sql:variable("@i")]') AS T(x)

    IF @refKey = ''
        EXEC sp_executeSQL @sql,N'@year INT,@outDay DATETIME OUT',@year,@hDay OUT
    ELSE
        EXEC sp_executeSQL @sql,N'@year INT,@x XML,@key VARCHAR(40),@outDay DATETIME OUT',@year,@x_SPEC,@refKey,@hDay OUT

    INSERT @t_res(hType,yy,hKey,hDate) SELECT 'S',@year,@key,@hDay

    SET @i=@i+1
END

SELECT * FROM @t_res

相关推荐