Query help

I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>

Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?