גרי רשף

With Nm As

(Select 0 N

Union All

Select    N+1

From    Nm

Where    N<5)

Select    --Nm1.N+Nm2.N+Nm3.N+Nm4.N+Nm5.N Nm,

        *

From    Nm Nm1, Nm Nm2, Nm Nm3, Nm Nm4, Nm Nm5

Where    Nm1.N<=5/1 And Nm2.N<=5/2 And Nm3.N<=5/3 And Nm4.N<=5/4 And Nm5.N<=5/5

        And Nm1.N+Nm2.N+Nm3.N+Nm4.N+Nm5.N=5

        And Nm1.N>=Nm2.N And Nm2.N>=Nm3.N And Nm3.N>=Nm4.N And Nm4.N>=Nm5.N

Order By Nm1.N,Nm2.N,Nm3.N,Nm4.N,Nm5.N

Declare    @N Int,

        @SQL Varchar(Max),

        @SQLs Varchar(Max),

        @SQLf Varchar(Max),

        @SQLw0 Varchar(Max),

        @SQLw1 Varchar(Max),

        @SQLw2 Varchar(Max),

        @SQLo Varchar(Max);

Set        @N=5;

Set        @SQLs='';

Set        @SQLf='';

Set        @SQLw0='';

Set        @SQLw1='';

Set        @SQLw2='';

Set        @SQLo='';

With Nm As

(Select 0 N

Union All

Select    N+1

From    Nm

Where    N<@N)

Select    @SQLs=@SQLs+'Nm'+Cast(N As Varchar)+'.N+',

        @SQLf=@SQLf+'Nm Nm'+Cast(N As Varchar)+', ',

        @SQLw0=@SQLw0+'Nm'+Cast(N As Varchar)+'.N<='+Cast(@N As Varchar)+'/'+Cast(N As Varchar)+' And ',

        @SQLw1=@SQLw1+'Nm'+Cast(N As Varchar)+'.N+',

        @SQLw2=@SQLw2+Case When N=@N Then '' Else 'And Nm'+Cast(N As Varchar)+'.N>=Nm'+Cast(N+1 As Varchar)+'.N ' End,

        @SQLo=@SQLo+'Nm'+Cast(N As Varchar)+'.N,'

From    Nm

Where    N>=1;

Set        @SQL='With Nm As'+CHAR(13)+

            '(Select 0 N'+CHAR(13)+

            'Union All'+CHAR(13)+

            'Select    N+1'+CHAR(13)+

            'From    Nm'+CHAR(13)+

            'Where    N<'+Cast(@N As Varchar)+')'+CHAR(13)+

            'Select    --' + Left(@SQLs,Len(@SQLs)-1)+' Nm,'+CHAR(13)+

            '        *'+CHAR(13)+

            'From    '+Left(@SQLf,LEN(@SQLf)-1)+CHAR(13)+

            'Where    '+Left(@SQLw0,LEN(@SQLw0)-4)+CHAR(13)+

            '        And '+Left(@SQLw1,Len(@SQLw1)-1)+'='+CAST(@N As Varchar)+CHAR(13)+

            '        '+@SQLw2+CHAR(13)+

            'Order By '+Left(@SQLo,Len(@SQLo)-1);

Print    @SQL;

Exec(@SQL);

Declare    @N Int;

Set        @N=5;

With Nm As

(Select 1 N

Union All

Select    N+1

From    Nm

Where    N<@N),

Haluka As

(Select    CAST(N As Varchar(Max)) Haluka,

        N As Ahron,

        N As Total

From    Nm

Union All

Select    H.Haluka+','+CAST(Nm.N As Varchar(Max)),

        Nm.N,

        H.Total+Nm.N

From    Haluka H

Inner Join Nm

        On H.Ahron>=Nm.N

        And H.Total+Nm.N<=@N)

Select    Haluka

From    Haluka

Where    Total=@N;

Create Table T_Employees(TZ Varchar(10) Not Null Primary Key,Name Varchar(20));

Go

Insert Into T_Employees Values('330123123','Avi');

Insert Into T_Employees Values('5659824','Batya');

Insert Into T_Employees Values('65936224','Galit');

Insert Into T_Employees Values('45205957','Dan');

Insert Into T_Employees Values('571425','Hila');

Go

Select    TZ,

        Right(10-Right(Cast(Right(SUBSTRING(Reverse(TZ),1,1)*2,1) As TinyInt)+Case When Cast(SUBSTRING(Reverse(TZ),1,1) As TinyInt)>=5 Then 1 Else 0 End+

            Cast(SUBSTRING(Reverse(TZ),2,1) As TinyInt)+

            Cast(Right(SUBSTRING(Reverse(TZ),3,1)*2,1) As TinyInt)+Case When Cast(SUBSTRING(Reverse(TZ),3,1) As TinyInt)>=5 Then 1 Else 0 End+

            Cast(SUBSTRING(Reverse(TZ),4,1) As TinyInt)+

            Cast(Right(SUBSTRING(Reverse(TZ),5,1)*2,1) As TinyInt)+Case When Cast(SUBSTRING(Reverse(TZ),5,1) As TinyInt)>=5 Then 1 Else 0 End+

            Cast(SUBSTRING(Reverse(TZ),6,1) As TinyInt)+

            Cast(Right(SUBSTRING(Reverse(TZ),7,1)*2,1) As TinyInt)+Case When Cast(SUBSTRING(Reverse(TZ),7,1) As TinyInt)>=5 Then 1 Else 0 End+

            Cast(SUBSTRING(Reverse(TZ),8,1) As TinyInt)+

            Cast(Right(SUBSTRING(Reverse(TZ),9,1)*2,1) As TinyInt)+Case When Cast(SUBSTRING(Reverse(TZ),9,1) As TinyInt)>=5 Then 1 Else 0 End+

            Cast(SUBSTRING(Reverse(TZ),10,1) As TinyInt),1),1) SB,

        Name

From    T_Employees;

Select    TZ,

        Right(10-Right(Cast(Right(TZ1*2,1) As TinyInt)+Case When TZ1>=5 Then 1 Else 0 End+

            TZ2+

            Cast(Right(TZ3*2,1) As TinyInt)+Case When TZ3>=5 Then 1 Else 0 End+

            TZ4+

            Cast(Right(TZ5*2,1) As TinyInt)+Case When TZ5>=5 Then 1 Else 0 End+

            TZ6+

            Cast(Right(TZ7*2,1) As TinyInt)+Case When TZ7>=5 Then 1 Else 0 End+

            TZ8+

            Cast(Right(TZ9*2,1) As TinyInt)+Case When TZ9>=5 Then 1 Else 0 End+

            TZ0,1),1) SB,

        Name

From    (Select    *,

                Cast(SUBSTRING(ZT,1,1) As TinyInt) TZ1,

                Cast(SUBSTRING(ZT,2,1) As TinyInt) TZ2,

                Cast(SUBSTRING(ZT,3,1) As TinyInt) TZ3,

                Cast(SUBSTRING(ZT,4,1) As TinyInt) TZ4,

                Cast(SUBSTRING(ZT,5,1) As TinyInt) TZ5,

                Cast(SUBSTRING(ZT,6,1) As TinyInt) TZ6,

                Cast(SUBSTRING(ZT,7,1) As TinyInt) TZ7,

                Cast(SUBSTRING(ZT,8,1) As TinyInt) TZ8,

                Cast(SUBSTRING(ZT,9,1) As TinyInt) TZ9,

                Cast(SUBSTRING(ZT,10,1) As TinyInt) TZ0

        From    (Select    *,

                        REVERSE(TZ) ZT

                From    T_Employees) T) T;

Create Proc SP_BulPgia_Begin As

If (Select Count(*) From sys.tables Where name='T_BulPgia')=1 Drop Table T_BulPgia;

Create Table T_BulPgia(A Char(1),B Char(1),C Char(1),D Char(1),YesNo Bit);

Create Unique Clustered Index T_BulPgia_Idx On T_BulPgia(A,B,C,D);

With Nm As

(Select 0 N

Union All

Select    N+1

From    Nm

Where    N<9)

Insert Into T_BulPgia

Select    Cast(N1.N As Char) A,

        Cast(N2.N As Char) B,

        Cast(N3.N As Char) C,

        Cast(N4.N As Char) D,

        1 YesNo

From    Nm N1

Inner Join Nm N2

        On N2.N<>N1.N

Inner Join Nm N3

        On N3.N<>N1.N

        And N3.N<>N2.N

Inner Join Nm N4

        On N4.N<>N1.N

        And N4.N<>N2.N

        And N4.N<>N3.N;

Select    Top 1 'My guess: '+A+B+C+D

From    T_BulPgia

Order By NewID();

Create Proc SP_BulPgia_Guess(@Guess Char(4), @Bul Int, @Pgia Int) As

With T As

(Select    *,

        Case When SUBSTRING(@Guess,1,1)=A Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,2,1)=B Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,3,1)=C Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,4,1)=D Then 1 Else 0 End Bul,

        Case When SUBSTRING(@Guess,2,3) Like '%'+A+'%' Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,1,1)+SUBSTRING(@Guess,3,2) Like '%'+B+'%' Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,1,2)+SUBSTRING(@Guess,4,1) Like '%'+C+'%' Then 1 Else 0 End +

        Case When SUBSTRING(@Guess,1,3) Like '%'+D+'%' Then 1 Else 0 End Pgia

From    T_BulPgia

Where    YesNo=1)

Update    T

Set        YesNo=0

Where    Not (Bul=@Bul And Pgia=@Pgia);

Select    Top 1 Case (Select COUNT(*) From T_BulPgia Where YesNo=1) When 1 Then 'The answer is: ' Else 'My guess: ' End +A+B+C+D

From    T_BulPgia

Where    YesNo=1

Order By NewID();

SP_BulPgia_Begin;            --My guess: 6342

SP_BulPgia_Guess '6342',0,1;--My guess: 2015

SP_BulPgia_Guess '2015',0,3;--My guess: 7201

SP_BulPgia_Guess '7201',0,2;--My guess: 1530

SP_BulPgia_Guess '1530',2,0;--My guess: 1528

SP_BulPgia_Guess '1528',4,0;--The answer is: 1528