Calculation using row values - 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

General SQL Server Forums

New to SQL Server Programming

Calculation using row values

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-10-03 : 17:04:29

I have two tables as follows:
CREATE TABLE [dbo].[SAMPLE_DATA](
[SAMPLE_NUM] [numeric](12, 0) IDENTITY(1,1) NOT NULL, (PK)
[SAMPLE_TYPE] [char](1) NOT NULL,
[SAMPLE_STATION] [int] NOT NULL,
[SAMPLE_INTERVAL] [char](1) NOT NULL,
[SAMPLE_START_DATE] [datetime] NOT NULL,
[SAMPLE_START_TIME] [int] NOT NULL,
[SAMPLE_END_DATE] [datetime] NOT NULL,
[SAMPLE_END_TIME] [int] NOT NULL
)
CREATE TABLE [dbo].[STATION_DATA](
[DATA_ID] [int] IDENTITY(1,1) NOT NULL, (PK)
[DATA_SAMPLE_NUM] [numeric](12, 0) NOT NULL, (FK)
[DATA_PARAMETER] [char](8) NOT NULL,
[DATA_METHOD] [char](3) NOT NULL,
[DATA_UNIT] [char](2) NOT NULL,
[DATA_DECIMAL] [numeric](1, 0) NULL,
[DATA_VALUE] [numeric](8, 4) NULL
)
First table (SAMPLE_DATA) is header table, the second one (STATION_DATA) is a details table.
Data are stored for each hour of the day. For each hour, there are many parameter values that are stored in the STATION_DATA table.

Here are some data sample:
SAMPLE_DATA
------------
S_NUM,S_TYPE,S_STATION,S_START_DATE,S_START_TIME,S_END_DATE,S_END_TIME
123456, 1, 1023, 1-Jan-2011, 0,1-Jan-2011, 59

STATION_DATA
------------
D_SAMPLE_NUM,D_PARAMETER,D_METHOD,D_UNIT,D_VALUE
123456, 81104, 093, 11, 12.345
123456, 41102, 020, 07, 0.012
123456, 42402, 021, 07, 1.234
123456, 42602, 089, 08, 3.456

I need to go through each hours of data and perform calculations on some parameter values and do an INSERTION in another table.
DO I have to use cursors or I can use a loop to go through each row and do the calculations?

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:50:16

you dont want both!
You can go for a set based solution where you process all rows doing required calculations in a batch and insert it to new table.
Didnt get how you identify rows for a hour though as I cant find any time related details in STATION_DATA and hence dont understand how you identify which of records came at which time for a D_SAMPLE_NUM value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources