Estimating by date and frequency

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

General SQL Server Forums

New to SQL Server Programming

Estimating by date and frequency

Author

Topic

bryanoates
Starting Member

2 Posts

Posted - 2008-10-28 : 13:13:10

I am a fairly new SQL user only having experience with some simple select statements. I need to estimate future entries into a table based on a frequency and date.

Ex.

My Table

due | freq | uom | pm_name

11/1/08 | 1 | week | pm_1
11/1/08 | 2 | week | pm_2

So what we have is a table with some preventive maintenance schedules. Column 1 is the next due date, column 2 is the frequency, column 3 is the Unit of Measure for the frequency, and column 4 is the name.

I do not have access directly to the database. The software I am using has a built in cognos engine so that I may write custom reports within our system. I want to select a date( I can create prompt pages with cognos) such as 11/30/08 and return a the list:

11/1/08 | pm_1
11/1/08 | pm_2
11/8/08 | pm_1
11/15/08 | pm_1
11/15/08 | pm_2
11/22/08 | pm_1
11/29/08 | pm_1
11/28/08 | pm_2

The result is a list of all forecasted PM Schedules up until the date selected.

Thanks,
Bryan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 13:17:13

[code]
DECLARE @Date datetime
SET @Date='11/30/08'
SELECT due,pm_name
FROM yourtable
WHERE due<=@Date
[/code]

bryanoates
Starting Member

2 Posts

Posted - 2008-10-28 : 13:28:20

Thanks, but this would only return the data that is within the table itself.

due | freq | uom | pm_name

11/1/08 | 1 | week | pm_1
11/1/08 | 2 | week | pm_2

I need the code to also estimate future entries that do not yet exist in the table.

11/1/08 | pm_1
11/1/08 | pm_2
11/8/08 | pm_1
11/15/08 | pm_1
11/15/08 | pm_2
11/22/08 | pm_1
11/29/08 | pm_1
11/28/08 | pm_2

Is there a way to do this.

Thanks, Bryan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-28 : 15:50:08

what are all the possible values of [UOM]?

Be One with the Optimizer
TG

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 16:06:55

Like TG said, all depends on the possible values of UOM, I have a query that ill work in SQL 2005. If you have other possible values for UOM make sure you update the query below:


;with mytemp (due, freq, uom, pm_name) as (
select cast(due as datetime), freq, uom, pm_name from @sample
union all
select case when uom = 'week' then dateadd(week,freq, cast(due as datetime)) 
when uom = 'day' then dateadd(dd, freq, cast(due as datetime))
when uom = 'month' then dateadd(mm,freq, cast(due as datetime))
end,freq, uom, pm_name
from mytemp
where cast(due as datetime) < '2008-11-30'
)
select * from mytemp
where due <= '2008-11-30'
order by due

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources