"Bharathi Kumar" wrote:

> Hi,
>               I have a requirement in my project:
>
> ex: User is running a scenario for 350 days.  within this period some
> events may occur. Each event has some duration. for ex: within the 350
> days an event may occur for 5 times and the duration of the event is
> for 10 days.
>
> Each time when I run the project I have to get 5 different numbers
> whose value must be less than 350.
>
> This I got by the query
>
> select top 5 DayNum from tblNumbers where DayNum <= 350 order by
> newid()
> ( I have 10000 numbers in tblNumbers table already)
>
> But the difference between any 2 numbers in the output should never be
> less than 10.
> (I mean when an event is running with in that duration that event
> should not repeat again).
>
> Please help how can I write the query for this.
>
> Thanks in advance,
> Regards
> Bharti Kumar.
>
>

> Hi Bharathi
>
> Try this :
> This is how I creted the environment so the code you will need is in the
> second part but I hope this helps. All the best
>
> -- Create Control Table
> Create Table #TempNumber (Daynum int null)
> -- Insert 10000 rows
>
> Begin
>     Set nocount on
>
>     Declare @X int
>     Set @X = 1
>
>     While (@X < 5000)
>         BEGIN
>                Insert #TempNumber Values (@X)
>             Set @X = @X + 1
>             CONTINUE
>         END
>
>     Set nocount off
> -- Now Begin the Test
>
>     Create Table #FiveNumbers (DayNum int)
>
>     Declare @Interval int
>     Declare @Counter int -- Number of Values to Return
>     Declare @Seed int
>
>     Set @COunter = 5
>     Set @Interval = 17
>     Set @Seed = 350
>
>     -- Insert First Row
>     Insert #FiveNumbers Select Top 1 DayNum  From #TempNumber where Daynum >=
> @Seed
>
>     -- Now Get The Other Rows
>     While @Counter > 0
>
>     BEgin
>         Insert #FiveNumbers
>         Select Top 1 T.DayNum
>         From #TempNumber  T --join #FiveNumbers F On T.DayNum <> F.Daynum
>         where T.DayNum >= @Seed + @Interval
>         AND T.Daynum not in (Select Daynum From #FiveNumbers)
>
>         Set @Seed = (Select max(Daynum) From #FiveNumbers)
>
>         Set @Counter = @Counter - 1
>
>         CONTINUE
>
>     End
>
>
>
>     Select Top 5 Daynum, NewID() 'UID' from #TempNumber
>     Select Top 5 Daynum, NewID() 'UID' from #FiveNumbers
>
>     Drop Table #TempNumber
>     Drop Table #FiveNumbers
> end
>
> All the best
>
> "Bharathi Kumar" wrote:
>
> > Hi,
> >               I have a requirement in my project:
> >
> > ex: User is running a scenario for 350 days.  within this period some
> > events may occur. Each event has some duration. for ex: within the 350
> > days an event may occur for 5 times and the duration of the event is
> > for 10 days.
> >
> > Each time when I run the project I have to get 5 different numbers
> > whose value must be less than 350.
> >
> > This I got by the query
> >
> > select top 5 DayNum from tblNumbers where DayNum <= 350 order by
> > newid()
> > ( I have 10000 numbers in tblNumbers table already)
> >
> > But the difference between any 2 numbers in the output should never be
> > less than 10.
> > (I mean when an event is running with in that duration that event
> > should not repeat again).
> >
> > Please help how can I write the query for this.
> >
> > Thanks in advance,
> > Regards
> > Bharti Kumar.
> >
> >