Query for Planninglist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query for Planninglist

Author

Topic

JNotenboom
Starting Member

27 Posts

Posted - 2003-07-15 : 05:51:14

Hello everybody,
I have the following issue.
I'm making a certain planningslist. I do this based on the following:
A device 'X' needs maintenance. This is done based on two parameters:
- A start month (for example 'februari 2003');
- A number of times per year, for example 3 times.
A device 'Y' needs also maintaince, but with startmonth of june and two times a year.
etc
I have put the parameters in a table with fields:
devicename - startmonth - number of times maintenance in a year.

So for device 'Y' maintenance is planned in february, june and oktober of 2003. I can do this with functions like mod() and DateAdd stuff. But...how should the query look like?? Please help.
If I have the correct query, I want to put this in a planningslist. It should look like this:
-- february --
device 'X' needs mainenance

-- june --
device 'X' needs mainenance

-- oktober --
device 'X' needs mainenance

Regards,
Johan

======
'Our solution will be part of your problem'

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 08:06:45

Something like this (with your tuning it up), where dn - device name,
sm - start month (just a number!), n - number of maintenances per year,
I use here this raw formula: (currmonth - sm) MOD ((12-sm)/n)=0

declare @i int, @s varchar(1000)
set @i=1 set @s=''
while @i<=12
begin
set @s=@s+
'select '+cast(@i as varchar(2))+' currmonth, dn, sm, n from t where ('+
cast(@i as varchar(2))+'-sm) % ((12-sm)/n)=0 and sm<='+cast(@i as varchar(2))
set @s=@s+' union '
set @i=@i+1
end
set @s=left(@s,len(@s)-len(' union '))
-- print @s
exec (@s)

- Vit

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-15 : 09:47:21

Yikes ....

use a tally or numbers table. the table is a generic table of numbers, from 1 to whatever (usually 1000). you create it once and just leave it there.

in this case, call it Numbers with an int column called Number.

Here's an example (self-contained) :

-------------

-- set up:

declare @t table (Device char(1),
StartMonth datetime,
TimesPerYear int)

declare @Numbers table (Number int)

-- fill in our numbers table:

insert into @numbers
select 1 union select 2
union select 3 union select 4
union select 5 union select 6
union select 7 union select 8
union select 9 union select 10
union select 11 union select 12

-- sample data:

insert into @t
select 'X','2/1/2003',3
union
select 'Y','6/1/2003',2
union
select 'Z','5/1/2003',6

-- and the result:

select a.device, dateadd(mm, (N.Number - 1) * (12 / TimesPerYear), StartMonth) as Date
from
@t a
inner join
@Numbers N
on
N.Number <= TimesPerYear
order by 1,2

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-15 : 12:23:46

I was absolutely confident that the 'start month' expression means
that all maintenances of a given device must be performed only from
its 'start' month till december including. With the same shedule on next
and following years... Great... Uhfff... Any comment?

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-15 : 12:29:30

Way Cool...

TALLY HO!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources