Is there anu wahy to to that better? 1

[bborissov]

A fellow forum man, ask a question about SQL Query. Unfortunately I was not able to give a good him.

Here is the data and the query he uses:

[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=blue]Date[/color] [COLOR=#FF00FF]datetime[/color], Rate [COLOR=blue]float[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (1,[COLOR=red]'1/1/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (2,[COLOR=red]'1/2/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (3,[COLOR=red]'1/3/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (4,[COLOR=red]'1/4/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (5,[COLOR=red]'1/5/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (6,[COLOR=red]'1/6/2007'[/color],1.5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (7,[COLOR=red]'1/7/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (8,[COLOR=red]'1/8/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (9,[COLOR=red]'1/9/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (10,[COLOR=red]'1/10/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (11,[COLOR=red]'1/11/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (12,[COLOR=red]'1/12/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (13,[COLOR=red]'1/13/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (14,[COLOR=red]'1/14/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (15,[COLOR=red]'1/15/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (16,[COLOR=red]'1/16/2007'[/color],1)

[COLOR=blue]SELECT[/color] Tmp.*
[COLOR=blue]FROM[/color]  @Temp Tmp
[COLOR=blue]WHERE[/color] (([COLOR=blue]SELECT[/color] Id [COLOR=blue]FROM[/color] @Temp x
                [COLOR=blue]WHERE[/color] x.Id = Tmp.ID - 1) [COLOR=blue]IS[/color] NULL)
      OR (([COLOR=blue]SELECT[/color] Rate [COLOR=blue]FROM[/color] @Temp x [COLOR=blue]WHERE[/color] x.ID = Tmp.ID - 1) <> Tmp.Rate)

I am sure there is MUCH better query that this because all goes down If you have data like this:

[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=blue]Date[/color] [COLOR=#FF00FF]datetime[/color], Rate [COLOR=blue]float[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (1,[COLOR=red]'1/1/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (19,[COLOR=red]'1/2/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (3,[COLOR=red]'1/3/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (4,[COLOR=red]'1/4/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (5,[COLOR=red]'1/5/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (6,[COLOR=red]'1/6/2007'[/color],1.5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (7,[COLOR=red]'1/7/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (8,[COLOR=red]'1/8/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (9,[COLOR=red]'1/9/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (10,[COLOR=red]'1/10/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (11,[COLOR=red]'1/11/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (12,[COLOR=red]'1/12/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (13,[COLOR=red]'1/13/2007'[/color],1.25)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (14,[COLOR=red]'1/14/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (15,[COLOR=red]'1/15/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] (16,[COLOR=red]'1/16/2007'[/color],1)

TIA

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Hey Boris,

What about this?

[COLOR=blue]SELECT[/color] Tmp.*
[COLOR=blue]FROM[/color]  @Temp Tmp
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Temp [COLOR=blue]t[/color]
[COLOR=blue]on[/color] Tmp.Date = [COLOR=blue]t[/color].Date + 1
and Tmp.Rate = [COLOR=blue]t[/color].Rate
[COLOR=blue]where[/color] [COLOR=blue]t[/color].ID [COLOR=blue]is[/color] null

Works with the sample data, anyways...

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

Borislav,
I'm not sure what you are asking for here. Can you elaborate?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bborissov]

Sorry Denny,
It was 2 AM here when I posted the message, so maybe my brain was slipping.
The desired result from the example data must be like this:

ID |Date      |Rate
1  |1/1/2007  |1
4  |1/4/2007  |1.25
6  |1/6/2007  |1.5
7  |1/7/2007  |1
10 |1/10/2007 |1.25
14 |1/14/2007 |1

Alex, thank you.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrdenny]

The easiest way to process this will probably be to write a cursor to loop through the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

mrdenny,

Alex already showed how to do it with a set-based solution.

Why are you suggesting a cursor?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

Assuming Date is unique in the table,

SELECT T1.*   
FROM
   @Temp T1
WHERE
   T1.Rate <> IsNull((
      SELECT Rate
      FROM @Temp T2
      WHERE T2.Date = (
         SELECT TOP 1 Date
         FROM @Temp T3
         WHERE T3.Date < T1.Date
         ORDER BY T3.Date DESC
		)
   ), -1)

And there might even be a way to do it without a correlated subquery.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

And no it didn't take 40 minutes to write that. Alex mentioned his solution wasn't complete.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

If I could use the First() aggregate function from Access, it would be even simpler:

SELECT T1.*   
FROM
   @Temp T1
WHERE
   T1.Rate <> IsNull((
      SELECT First(T2.Rate)
      FROM @Temp T2
      WHERE T2.Date < T1.Date
      ORDER BY T2.Date DESC
   ), -1)

And here's a derived table version (probably doesn't perform well)

SELECT A.*
FROM
   @Temp A
   INNER JOIN (
      SELECT T1.Date, PrevDate = Max(T2.Date)
      FROM
         @Temp T1
         LEFT JOIN @Temp T2 ON T1.Date > T2.Date
      GROUP BY T1.Date
   ) X ON A.Date = X.Date
   LEFT JOIN @Temp B ON X.PrevDate = B.Date
WHERE
   A.Rate <> B.Rate OR B.Rate IS NULL

If the maximum number of skipped days could be known in advance, this might perform very well by adding a condition to the center LEFT JOIN, AND T2.Date > T1.Date - @MaxDatesSkipped

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

And hey... the task is also simplified and query performance is greatly increased if you can insert dummy records for missing days.

Just insert the most recent rate for all missing days (you could set a bit flag for "real" rates vs. fill-in ones if required) and then you can just join on the date - 1.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

sorry to keep posting... and Index on Date would greatly speed all queries.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[mrdenny]

Bad morning. Sorry folks.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com