Some kind of join?

[Ebadin]

Hi guys,
Do you guys think this can be solved in one query?

I have 1 table:

| Name | Start Date | Value
----------------------------
| A | D1 | V1
| A | D2 |
| A | D3 |
| A | D5 | V3
| B | D9 | V4
...

I need to get something like this:

| Name | Start Date | Value
----------------------------
| A | D1 | V1
| A | D2 | V1
| A | D3 | V1
| A | D5 | V3
| B | D9 | V4
...

So what I need is to fill the empty cells with the last value from the last previous date that has value.

Am I making any sense?

 

[Rabbie]

You should be able to do this using sub-queries. See this link for more information

 

[Ebadin]

Thanks Rabbie,
think though my situation it's a little bit more complex...
I'm still struggling with it so if anyone has some more tips... I gladly listen.

 

[spaddhu]

Hi Ebadin,

I registered with this community for the sake of answering your query. Found it too interesting and tried it out, so that could provide you a solution. Here goes a detailed explanation of what i did.

1. Create a query that renames all the fields in your table.
Thereby you have two copies of same data, with proper aliases, so that it does
confuse you.
2. The Table1 contains 3 fields Name,SDate and Val
The Query2 contains 3 fields Name1,SDate1 and Val1
3. Write the new Query1 as follows

SELECT Query2.Name1, Query2.SDate1, Last(IIf([Val1] Is Null,[Val],[Val1])) AS Val2
FROM Table1 INNER JOIN Query2 ON Table1.Name = Query2.Name1
WHERE (((Table1.Val) Is Not Null) AND ((Query2.SDate1)>=[SDate]))
GROUP BY Query2.Name1, Query2.SDate1;

Please do get back if this works or doesn't work. Also post if you have already achieved this using some other technique.

 

[spaddhu]

forgot to add... this works only if D5>D3>D2>D1.

 

[Ebadin]

forgot to add... this works only if D5>D3>D2>D1.

Hehe... thanks. Yes, the values are chronological.
I will give it a try. I am also working on something. I will update this topic.

Think is that after this, the query will be moved to a SQL or PLSQL environment and there the fun part begins

 

[spaddhu]

i'm basically a SQL programmer only.. so this solution is very much in line with SQL.. the only difference being, here it is a query and there it is called a view..

 

[Ebadin]

Hey, hey, hey!.... it looks like it works... thanks mate!