Tricky calculation in SQLServer 2000

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)

Tricky calculation in SQLServer 2000

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-02-25 : 05:59:34

I have a following table in sql server with almost 30k entries
Device , date, output_frequency
amplif, 24Feb , 50Hz
amplif, 25Feb , 50hz
amplif, 26Feb, 60hz
amplif, 27Feb , 50Hz
amplif, 28Feb , 60hz
amplif, 29Feb, 60hz

I have another table where I have sample
DB_sample
Device, date , sample_rate
amplif,24Feb, 120

what i need to calculate number of cycle for each entry
the final table where i store all my results is
Device, date, output_frequency,sample_rate, cycles(Frequency*sample_rate)

As long as there is just one entry in sample rate its a simple
calculation. But i need a way to calculate cycles for each date in db_sample. Like if i add another entry in db_sample

Device, date , sample_rate
amplif,24Feb, 120
amplif,26Feb, 240
amplif,28Feb, 110

I need to calculate no of cycles from 24Feb to 25Feb on 120 sample_rate as the new sample rate was introduced on 26Feb, then number of cycles from 26 to 27 on 240 and from 28 onwards on new sample rate value of 110 , untill any new value of samplerate comes.

I am sorry if i am not able to explain it clearly. But basically i need to calculate No_of_Cycles where sample_rate value will depend on which date is effective from untill any new rate is given.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:03:28

I think you have to post proper sample data illustrating your problem.

E 12°55'05.63"
N 56°04'39.26"

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-02-25 : 06:34:19

table A
Device , date, output_frequency
amplif, 24Feb , 50Hz
amplif, 25Feb , 50hz
amplif, 26Feb, 60hz
amplif, 27Feb , 50Hz
amplif, 28Feb , 60hz
amplif, 29Feb, 60hz

table B
Device, date , sample_rate
amplif,24Feb, 120
amplif,26Feb, 240
amplif,28Feb, 110

table results (required)
A.Device , A.date, A.output_frequency,B.sample_rate
amplif, 24Feb , 50Hz ,120 (note in my table B for 24Feb i have a sample rate of 120)
amplif, 25Feb , 50hz ,120 (Since new sample_rate was effective from 26Feb, therefore I should use 120 on 25Feb)
amplif, 26Feb, 60hz ,240 (the new value of sample_rate from table B)
amplif, 27Feb , 50Hz ,240 (since new eff_date was 28 it should use previous value which is 240)
amplif, 28Feb , 60hz ,110 ( new sample_rate was effective from 28Feb)
amplif, 29Feb, 60hz ,110 ( no new sample rate is defined, this value would be used unless any new value defined)

This is where i am having difficulty in checking which sample_rate value to choose for the range of dates.I can not simple inner join as on date as it will only display following records where it matches a match. But will ommit the rest of the entries as shown in above table table (table_results required)

table results
A.Device , A.date, A.output_frequency,B.sample_rate
amplif, 24Feb , 50Hz ,120
amplif, 26Feb, 60hz ,240
amplif, 28Feb , 60hz ,110

I hope i am making sense in explaining.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 06:44:41

Do you really store date like that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:49:54

[code]declare @tablea table
(
device varchar(20),
date datetime,
output_frequency int
)

insert @tablea
select 'amplif', '24 feb 2008', 50 union all
select 'amplif', '25 feb 2008', 50 union all
select 'amplif', '26 feb 2008', 60 union all
select 'amplif', '27 feb 2008', 50 union all
select 'amplif', '28 feb 2008', 60 union all
select 'amplif', '29 feb 2008', 60

declare @tableb table
(
device varchar(20),
date datetime,
sample_rate int
)

insert @tableb
select 'amplif', '24 feb 2008', 120 union all
select 'amplif', '26 feb 2008', 240 union all
select 'amplif', '28 feb 2008', 110

select a.device,
a.date,
a.output_frequency,
(select top 1 sample_rate from @tableb as b where b.device = a.device and b.date <= a.date order by b.date desc) AS sample_rate
from @tablea as a[/code]But you should read this topic VERY CAREFULLY to improve your application!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119974

It is almost the same problem you are facing.

E 12°55'05.63"
N 56°04'39.26"

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-02-25 : 07:43:34

Thanks for your help.
It looks quite complex , i will take some time in reading and understanding it.
I will post you if i do not understand any thing.
Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources