גרי רשף

Select 'ט"ו בניסן' [פסח];

CREATE function dbo.Passover(@Yr int)

returns datetime

AS

BEGIN

   Declare @HYear int, @Matonic int, @LeapException int, @Leap int, @DOW int, @Century int

   Declare @fDay float(20), @fFracDay float(20)

   Declare @Mo int, @Day int

   Set @HYear=@Yr+3760

   Set @Matonic=(12*@HYear+17) % 19

   Set @Leap=@HYear % 4

   Set @fDay=32+4343/98496.+@Matonic+@Matonic*(272953/492480.)+@Leap/4.

   Set @fDay=@fDay-@HYear*(313/98496.)

   Set @fFracDay=@fDay-FLOOR(@fDay)

   Set @DOW=cast (3*@HYear+5*@Leap+FLOOR(@fDay)+5 as int) % 7

   IF @DOW=2 or @DOW=4 or @DOW=6

      set @fDay=@fDay+1

   IF @DOW=1 and @Matonic>6 and @fFracDay>=1367/2160.

      set @fDay=@fDay+2

   IF @DOW=0 and @Matonic>11 and @fFracDay>=23269/25920.

      set @fDay=@fDay+1

   Set @Century=FLOOR(@Yr/100.)

   Set @LeapException=FLOOR((3*@Century-5)/4.)

   IF @Yr>1582

      set @fDay=@fDay+@LeapException

   Set @Day=FLOOR(@fDay)

   Set @Mo=3

   IF @Day>153

      Begin

         set @Mo=8

         set @Day=@Day-153

      End

   IF @Day>122

      Begin

         set @Mo=7

         set @Day=@Day-122

      End

   IF @Day>92

      Begin

         set @Mo=6

         set @Day=@Day-92

      End

   IF @Day>61

      Begin

         set @Mo=5

         set @Day=@Day-61

      End

   IF @Day>31

      Begin

         set @Mo=4

         set @Day=@Day-31

      End

   return cast(str(@Mo)+'/'+str(@Day)+'/'+str(@Yr) as datetime)

/* Based on mathematical algorithms first devised by the German mathematician Carl Friedrich Gauss (1777-1855).  I have used the date of Passover to determine most of the other Jewish holidays.*/

END

GO

Declare    @MiShana Int,

        @AdShana Int;

Select    @MiShana=5661,

        @AdShana=6000;

With Shanim As

(Select    @MiShana ShanaI,

        @MiShana-3761 ShanaL,

        Cast(Null As DateTime) RoshHashanaHakodem,

        dbo.Passover(@MiShana-3761)+163 RoshHashanaHaba,

        Cast(Null As Int) Yamim

Union All

Select    ShanaI,

        ShanaL,

        RoshHashanaHakodem,

        RoshHashanaHaba,

        DateDiff(Day,RoshHashanaHakodem,RoshHashanaHaba) Yamim

From    (Select    ShanaI+1 ShanaI,

                ShanaL+1 ShanaL,

                RoshHashanaHaba RoshHashanaHakodem,

                dbo.Passover(ShanaI-3760)+163 RoshHashanaHaba

        From    Shanim

        Where    ShanaI<@AdShana) T)

Select    ShanaI-1 ShanaI,

        ShanaL-1 ShanaL,

        RoshHashanaHakodem,

        RoshHashanaHaba,

        Yamim

From    Shanim

Where    Yamim Is Not Null

option (MaxRecursion 0);

Go

Declare    @MiShana Int,

        @AdShana Int;

Select    @MiShana=5661,

        @AdShana=6000;

With Shanim As

(Select    @MiShana ShanaI,

        @MiShana-3761 ShanaL,

        Cast(Null As DateTime) RoshHashanaHakodem,

        dbo.Passover(@MiShana-3761)+163 RoshHashanaHaba,

        Cast(Null As Int) Yamim

Union All

Select    ShanaI,

        ShanaL,

        RoshHashanaHakodem,

        RoshHashanaHaba,

        DateDiff(Day,RoshHashanaHakodem,RoshHashanaHaba) Yamim

From    (Select    ShanaI+1 ShanaI,

                ShanaL+1 ShanaL,

                RoshHashanaHaba RoshHashanaHakodem,

                dbo.Passover(ShanaI-3760)+163 RoshHashanaHaba

        From    Shanim

        Where    ShanaI<@AdShana) T),

Hodashim As

(Select 1 ID, 'תשרי' Hodesh,30 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,29 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,30 Yamim, 'מלא' Sug Union All

Select 3 ID, 'כסלו' Hodesh,30 Yamim, Null Sug Union All

Select 3 ID, 'כסלו' Hodesh,29 Yamim, 'חסר' Sug Union All

Select 4 ID, 'טבת' Hodesh,29 Yamim, Null Sug Union All

Select 5 ID, 'שבט' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר' Hodesh,29 Yamim, Null Sug Union All

Select 6 ID, 'אדר א' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר ב' Hodesh,29 Yamim, Null Sug Union All

Select 7 ID, 'ניסן' Hodesh,30 Yamim, Null Sug Union All

Select 8 ID, 'אייר' Hodesh,29 Yamim, Null Sug Union All

Select 9 ID, 'סיוון' Hodesh,30 Yamim, Null Sug Union All

Select 10 ID, 'תמוז' Hodesh,29 Yamim, Null Sug Union All

Select 11 ID, 'אב' Hodesh,30 Yamim, Null Sug Union All

Select 12 ID, 'אלול' Hodesh,29 Yamim, Null Sug),

Yamim As

(Select 1 Yom

Union All

Select    Yom+1

From    Yamim

Where    Yom<30)

Select    S.ShanaI-1 ShanaI,

        H.Hodesh,

        H.Sug,

        Y.Yom,

        DateAdd(Day,Row_Number() Over(Partition By S.ShanaI Order By H.ID,H.Hodesh,Y.Yom)-1,S.RoshHashanaHakodem) TaarihL

From    Shanim S

Inner Join Hodashim H

        On ((S.Yamim<=355 And H.Hodesh Not In ('אדר א','אדר ב'))

            Or (S.Yamim>=383 And H.Hodesh Not In ('אדר')))

        And ((S.Yamim%10=3 

                And (H.Hodesh<>'חשוון' Or H.Sug Is Null)

                And (H.Hodesh<>'כסלו' Or H.Sug='חסר'))

            Or (S.Yamim%10=4 

                And H.Sug Is Null)

            Or (S.Yamim%10=5 

                And (H.Hodesh<>'חשוון' Or H.Sug='מלא')

                And (H.Hodesh<>'כסלו' Or H.Sug Is Null)))

Inner Join Yamim Y

        On H.Yamim>=Y.Yom

Order By S.ShanaI,

        H.ID,

        H.Hodesh,

        Y.Yom

option (MaxRecursion 0);

Go

Declare    @Molad DateTime,

        @Shana Int,

        @Shanim Int,

        @Hodesh Decimal(20,10);

Select    @Molad='19000924 05:00:30',

        @Shana=5661,

        @Shanim=200,

        @Hodesh=29+12./24+793./1080./24;

With Shanim As

(Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        Cast(Null As DateTime) RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    @Shana Shana,

                                Case When @Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                @Molad Molad,

                                DatePart(Weekday,@Molad) Yom,

                                Case When (@Shana-1)%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End MeuberetKodemet) T) T) T

Union All

Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,--לא אדו ראש

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    *,

                                Case When Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                DatePart(Weekday,Molad) Yom

                        From    (Select    Shana+1 Shana,

                                        Molad+Cast(12+Meuberet As Decimal(20,10))*@Hodesh Molad,

                                        Meuberet MeuberetKodemet,

                                        RoshHashana RoshHashanaHakodemet

                                From    Shanim) T

                        Where    Shana<@Shana+@Shanim) T) T) T)

Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        RoshHashanaHakodemet,

        Dhia1,--מולד זקן

        Dhia2,--ג' ט' ר"ד בשנה פשוטה

        Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת,

        Dhia4,--לא אדו ראש

        RoshHashana,

        DateDiff(Day,RoshHashanaHakodemet,RoshHashana) Yamim

From    Shanim

option (MaxRecursion 0);

Go

Declare    @Molad DateTime,

        @Shana Int,

        @Shanim Int,

        @Hodesh Decimal(20,10);

Select    @Molad='19000924 05:00:30',

        @Shana=5661,

        @Shanim=200,

        @Hodesh=29+12./24+793./1080./24;

With Hodashim As

(Select 1 ID, 'תשרי' Hodesh,30 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,29 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,30 Yamim, 'מלא' Sug Union All

Select 3 ID, 'כסלו' Hodesh,30 Yamim, Null Sug Union All

Select 3 ID, 'כסלו' Hodesh,29 Yamim, 'חסר' Sug Union All

Select 4 ID, 'טבת' Hodesh,29 Yamim, Null Sug Union All

Select 5 ID, 'שבט' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר' Hodesh,29 Yamim, Null Sug Union All

Select 6 ID, 'אדר א' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר ב' Hodesh,29 Yamim, Null Sug Union All

Select 7 ID, 'ניסן' Hodesh,30 Yamim, Null Sug Union All

Select 8 ID, 'אייר' Hodesh,29 Yamim, Null Sug Union All

Select 9 ID, 'סיוון' Hodesh,30 Yamim, Null Sug Union All

Select 10 ID, 'תמוז' Hodesh,29 Yamim, Null Sug Union All

Select 11 ID, 'אב' Hodesh,30 Yamim, Null Sug Union All

Select 12 ID, 'אלול' Hodesh,29 Yamim, Null Sug),

Shanim As

(Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        Cast(Null As DateTime) RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    @Shana Shana,

                                Case When @Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                @Molad Molad,

                                DatePart(Weekday,@Molad) Yom,

                                Case When (@Shana-1)%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End MeuberetKodemet) T) T) T

Union All

Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    *,

                                Case When Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                DatePart(Weekday,Molad) Yom

                        From    (Select    Shana+1 Shana,

                                        Molad+Cast(12+Meuberet As Decimal(20,10))*@Hodesh Molad,

                                        Meuberet MeuberetKodemet,

                                        RoshHashana RoshHashanaHakodemet

                                From    Shanim) T

                        Where    Shana<@Shana+@Shanim) T) T) T)

Select    S.Shana-1 Shana,

        *,

        DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana) Yamim

From    Shanim S

Inner Join Hodashim H

        On ((S.MeuberetKodemet=0 And H.Hodesh Not In ('אדר א','אדר ב'))

                Or (S.MeuberetKodemet=1 And H.Hodesh Not In ('אדר')))

            And ((DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=3 

                    And (H.Hodesh<>'חשוון' Or H.Sug Is Null)

                    And (H.Hodesh<>'כסלו' Or H.Sug='חסר'))

                Or (DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=4 

                    And H.Sug Is Null)

                Or (DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=5 

                    And (H.Hodesh<>'חשוון' Or H.Sug='מלא')

                    And (H.Hodesh<>'כסלו' Or H.Sug Is Null)))

Where    S.RoshHashanaHakodemet Is Not Null

option (MaxRecursion 0);

Go

Declare    @Molad DateTime,

        @Shana Int,

        @Shanim Int,

        @Hodesh Decimal(20,10);

Select    @Molad='19000924 05:00:30',

        @Shana=5661,

        @Shanim=200,

        @Hodesh=29+12./24+793./1080./24;

With Yamim As

(Select 1 Yom

Union All

Select    Yom+1

From    Yamim

Where    Yom<30),

Hodashim As

(Select 1 ID, 'תשרי' Hodesh,30 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,29 Yamim, Null Sug Union All

Select 2 ID, 'חשוון' Hodesh,30 Yamim, 'מלא' Sug Union All

Select 3 ID, 'כסלו' Hodesh,30 Yamim, Null Sug Union All

Select 3 ID, 'כסלו' Hodesh,29 Yamim, 'חסר' Sug Union All

Select 4 ID, 'טבת' Hodesh,29 Yamim, Null Sug Union All

Select 5 ID, 'שבט' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר' Hodesh,29 Yamim, Null Sug Union All

Select 6 ID, 'אדר א' Hodesh,30 Yamim, Null Sug Union All

Select 6 ID, 'אדר ב' Hodesh,29 Yamim, Null Sug Union All

Select 7 ID, 'ניסן' Hodesh,30 Yamim, Null Sug Union All

Select 8 ID, 'אייר' Hodesh,29 Yamim, Null Sug Union All

Select 9 ID, 'סיוון' Hodesh,30 Yamim, Null Sug Union All

Select 10 ID, 'תמוז' Hodesh,29 Yamim, Null Sug Union All

Select 11 ID, 'אב' Hodesh,30 Yamim, Null Sug Union All

Select 12 ID, 'אלול' Hodesh,29 Yamim, Null Sug),

Shanim As

(Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        Cast(Null As DateTime) RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    @Shana Shana,

                                Case When @Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                @Molad Molad,

                                DatePart(Weekday,@Molad) Yom,

                                Case When (@Shana-1)%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End MeuberetKodemet) T) T) T

Union All

Select    Shana,

        Meuberet,

        Molad,

        Yom,

        MeuberetKodemet,

        RoshHashanaHakodemet,

        Dhia1,

        Dhia2,

        Dhia3,

        Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End Dhia4,

        RoshHashana+Case When DatePart(WeekDay,RoshHashana) In (1,4,6) Then 1 Else 0 End RoshHashana

From    (Select    *,

                Cast(DateDiff(Day,0,Molad) As DateTime)+Case When 1 In (Dhia1,Dhia2,Dhia3) Then 1 Else 0 End RoshHashana

        From    (Select    *,

                        Case When Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(12./24) Then 1 Else 0 End Dhia1,--מולד זקן

                        Case When Yom=3 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(3.+204./1080)/24 And Meuberet=0 Then 1 Else 0 End Dhia2,--ג' ט' ר"ד בשנה פשוטה

                        Case When Yom=2 And Molad-Cast(DateDiff(Day,0,Molad) As DateTime)>(9.+589./1080)/24 And MeuberetKodemet=1 Then 1 Else 0 End Dhia3 --ב' ט"ו תקפ"ט בפשוטה שאחרי מעוברת

                From    (Select    *,

                                Case When Shana%19 In (3,6,8,11,14,17,0) Then 1

                                    Else 0

                                    End Meuberet,

                                DatePart(Weekday,Molad) Yom

                        From    (Select    Shana+1 Shana,

                                        Molad+Cast(12+Meuberet As Decimal(20,10))*@Hodesh Molad,

                                        Meuberet MeuberetKodemet,

                                        RoshHashana RoshHashanaHakodemet

                                From    Shanim) T

                        Where    Shana<@Shana+@Shanim) T) T) T)

Select    DateAdd(Day,Row_Number() Over(Partition By S.Shana Order By H.ID,H.Hodesh,Y.Yom)-1,S.RoshHashanaHakodemet) Taarih,

        S.Shana-1 Shana,

        S.RoshHashanaHakodemet RoshHashana,

        S.Meuberet,

        DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana) YamimBashana,

        H.ID MisparHodesh,

        H.Hodesh ShemHodesh,

        H.Sug SugHodesh,

        H.Yamim YamimBahodesh,

        Y.Yom

From    Shanim S

Inner Join Hodashim H

        On ((S.MeuberetKodemet=0 And H.Hodesh Not In ('אדר א','אדר ב'))

                Or (S.MeuberetKodemet=1 And H.Hodesh Not In ('אדר')))

            And ((DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=3 

                    And (H.Hodesh<>'חשוון' Or H.Sug Is Null)

                    And (H.Hodesh<>'כסלו' Or H.Sug='חסר'))

                Or (DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=4 

                    And H.Sug Is Null)

                Or (DateDiff(Day,S.RoshHashanaHakodemet,S.RoshHashana)%10=5 

                    And (H.Hodesh<>'חשוון' Or H.Sug='מלא')

                    And (H.Hodesh<>'כסלו' Or H.Sug Is Null)))

Inner Join Yamim Y

        On H.Yamim>=Y.Yom

Where    S.RoshHashanaHakodemet Is Not Null

Order By S.Shana,

        H.ID,

        H.Hodesh,

        Y.Yom

option (MaxRecursion 0);

Go

Create Proc P_TblDif @Tbl As Sysname,

                    @RmtServer As Sysname='MyProdServer' 

As 

Declare    @LclServer As Sysname, 

        @Cols As Varchar(Max), 

        @SQL As NVarchar(Max); 

Set        @LclServer=@@ServerName; 

Set        @SQL='Select    @Cols_=IsNull(@Cols_+'','','''')+''[''+L.name+'']''' 

            +Char(13)+'From    ['+@LclServer+'].'+DB_Name()+'.sys.columns L' 

            +Char(13)+'Inner Join ['+@RmtServer+'].'+DB_Name()+'.sys.columns R' 

            +Char(13)+'        On L.name=R.name' 

            +Char(13)+'Where    L.object_id=(Select object_id From ['+@LclServer+'].'+DB_Name()+'.sys.tables Where name='''+@Tbl+''')' 

            +Char(13)+'        And R.object_id=(Select object_id From ['+@RmtServer+'].'+DB_Name()+'.sys.tables Where name='''+@Tbl+''')' 

            +Char(13)+'Order By L.column_id;'; 

Exec    SP_ExecuteSQL @SQL, N'@Cols_ Varchar(Max) Out',@Cols_ = @Cols Out; 

Set        @SQL='With L As' 

            +Char(13)+'(Select    '+@Cols 

            +Char(13)+'From    ['+@LclServer+'].'+DB_Name()+'.dbo.'+@Tbl+'),' 

            +Char(13)+'R As' 

            +Char(13)+'(Select    '+@Cols 

            +Char(13)+'From    ['+@RmtServer+'].'+DB_Name()+'.dbo.'+@Tbl+')' 

            +Char(13)+'Select    '''+@LclServer+''' [Server],' 

            +Char(13)+'        *' 

            +Char(13)+'From    (Select * From R' 

            +Char(13)+'        Except' 

            +Char(13)+'        Select * From L) T' 

            +Char(13)+'Union All' 

            +Char(13)+'Select    '''+@RmtServer+''' [Server],' 

            +Char(13)+'        *' 

            +Char(13)+'From    (Select * From L' 

            +Char(13)+'        Except' 

            +Char(13)+'        Select * From R) T' 

            +Char(13)+'Order By 1;' 

Print    @SQL; 

Exec(@SQL);

With Tbl As

(Select    object_id,

        name,

        Schema_Name(schema_id) SchemaName,

        'Create Table '+Schema_Name(SCHEMA_ID)+'.'+name+'(' CreateTable

From    sys.tables),

Col As

(Select    object_id,

        column_id,

        Char(13)+Char(9)+Char(9)+'['+name+']'

        +Case When is_computed=0 Then

            ' '+Type_Name(user_type_id)+Case When Type_Name(user_type_id) In ('Char','Varchar') Then '('+Case When max_length=-1 Then 'Max' Else CAST(max_length AS Varchar) End+')'

                                            When Type_Name(user_type_id) In ('NChar','NVarchar') Then '('+Case When max_length=-1 Then 'Max' Else CAST(max_length/2 AS Varchar) End+')'

                                            When Type_Name(user_type_id)='Decimal' Then '('+CAST(precision As Varchar)+','+CAST(scale As Varchar)+')'

                                            Else '' End

            +IsNull(' '+'Collate '+collation_name,'') 

            +Case When is_identity=1 Then ' Identity('+(Select Cast(seed_value As Varchar)+','+Cast(increment_value As Varchar) From sys.identity_columns IC where IC.object_id=C.object_id And IC.column_id=C.column_id)+')' Else '' End

            +' '+Case When is_nullable=0 Then 'Not Null' Else 'Null' End

            +IsNull(' ='+OBJECT_DEFINITION(default_object_id),'')

        Else ' ='+(Select definition From sys.computed_columns CC Where CC.object_id=C.object_id And CC.column_id=C.column_id)

        End

        +',' ColDef

From    sys.columns C),

Cols As

(Select    object_id,

        (Select C1.ColDef As [text()]

            From    Col C1

            Where    C1.object_id=C.object_id

                    --And C1.column_id=C.column_id

            Order By column_id

            For XML Path('')) ColDef

From    Col C

Group By object_id),

PK As

(Select    ','+Char(13)+Char(9)+Char(9)+'Constraint ['+KC.name+'] Primary Key Clustered'

        +'('+IC.Cols+')' SQL,

        IC.object_id

From    (Select    object_id,

                Stuff((Select ','+Char(13)+Char(9)+Char(9)+Char(9)+'['+Col_Name(c1.object_id,c1.column_id)+'] '+Case When C1.is_descending_key=0 Then 'Asc' Else 'Desc' End As [text()]

                    From    sys.index_columns C1

                    Where    C1.object_id=C.object_id

                            --And C1.column_id=C.column_id

                    Order By C1.key_ordinal

                    For XML Path('')),1,1,'') Cols

        From    sys.index_columns C

        Group By C.object_id) IC

Inner Join sys.key_constraints KC 

        On IC.object_id = KC.parent_object_id  

Where    KC.type='PK'),

T As

(Select    T.object_id,

        T.SchemaName,

        T.name ObjectName,

        'User Table' TypeName,

        T.CreateTable

                +Left(C.ColDef,Len(C.ColDef)-1)

                +Cast(IsNull(PK.SQL,'') As Varchar(8000))+');' SQL

From    Tbl T

Inner Join Cols C

        On T.object_id=C.object_id

Left Join PK

        On T.object_id=PK.object_id

Union All

Select    object_id,

        Schema_Name(schema_id) SchemaName,

        Object_Name(object_id) ObjectName,

        type_desc TypeName,

        (Select Object_Definition(object_id) As [text()] From sys.all_objects S1 Where S1.object_id=S.object_id For XML Path('')) SQL

From    sys.all_objects S

Where    type Not In ('AF','C','D','FS','IT','PK','PC','S','SQ','U','X'))

Select    object_id,

        SchemaName,

        ObjectName,

        TypeName,

        Cast(SQL As XML) SQL

From    T

Where    T.SQL Like '%%'

Order By T.ObjectName;

If Object_ID('MyNewObject') Is Not Null Drop .. MyNewObject;

Go

If Object_ID('MyNewTable') Is Not Null

    Begin

    Create Table ..

    Grant Select On ..

    ..

    End;

If ObjectProperty(Object_ID('MyTbl'),'TableHasPrimaryKey')<>1

    Alter Table MyTbl ..

If Col_Length('MyTbl','MyCol') Is Null

    Alter Table MyTbl Add MyCol ..

Create Table #SP_Who(spid Int,

                    ecid Int,

                    status Varchar(20),

                    loginame Sysname Null,

                    hostname Sysname Null,

                    blk Int,

                    dbname Sysname Null,

                    cmd Varchar(20),

                    request_id Int);

Go

Insert

Into #SP_Who

Exec SP_Who;

Go

Select * From #SP_Who;

Go

Select *

Into #SP_Who

From OPENROWSET('SQLOLEDB','Server=MyServer;Trusted_Connection=yes;','Exec SP_Who;');

Go

Select * From #SP_Who;

Go

Select Schema_Name(Schema_ID) Schema_Name,

        Object_Name(parent_object_id) parent_Object_Name,

        *

From sys.objects;

Select Object_Name(object_id) Object_Name,

        Schema_Name(ObjectProperty(object_id,'SchemaId')) Schema_Name,

        Type_Name(system_type_id) Type_Name,

        Type_Name(user_type_id) User_Type_Name2,

        Object_Name(default_object_id) Default_Constraint_Name,

        Object_Name(rule_object_id) Rule_Constraint_Name,

        *

From sys.columns;

Select Object_Name(object_id) [Object_Name],

        ObjectPropertyEx (object_id,'BaseType') Object_Type,

        Schema_Name(Cast(ObjectPropertyEx (object_id,'SchemaId') As Int))

        Schema_Name,

        Col_Name(object_id,Column_id) Column_Name,

        *

From sys.index_columns

Order By Object_Name(object_id),

        Index_Id,

        key_ordinal;

Create Table #T(Txt Varchar(Max));

Go

Insert

Into    #T

Select    'ab[cd]ef';

Go

Select    *

From    #T

Where    Txt Like '%b[[]cd]e%';

Go

If Object_id('tempdb..#P') Is Not Null Drop Proc #P;

Go

Create Procedure #P As

Select  R.transaction_id,

        T.name 

From sys.dm_exec_requests R 

Inner Join sys.dm_tran_active_transactions T

        On R.transaction_id=T.transaction_id

Where R.session_id=@@SPID;

Begin Tran MyTran1

Select  R.transaction_id,

        T.name 

From sys.dm_exec_requests R 

Inner Join sys.dm_tran_active_transactions T

        On R.transaction_id=T.transaction_id

Where R.session_id=@@SPID;

Begin Tran MyTran2

Select  R.transaction_id,

        T.name 

From sys.dm_exec_requests R 

Inner Join sys.dm_tran_active_transactions T

        On R.transaction_id=T.transaction_id

Where R.session_id=@@SPID;

Commit Tran MyTran3;

Commit Tran MyTran3;

Go

Exec #P;

Go

Create Database MyDB Collate Hebrew_CI_AI;

Go

Select DatabasePropertyEx('master','Collation');

Select DatabasePropertyEx(DB_Name(),'Collation');

Select name,collation_name From sys.databases Where database_id=DB_ID();