August 12, 2014 at 9:27 am
Hi, is it possible to do the following?
I have this table:
CREATE TABLE test1(
[A] [int] NULL,
[varchar](10) NULL
)
It has this data in it:
insert into test1 (a, b)
values (1, 'A')
insert into test1 (a, b)
values (2, null)
insert into test1 (a, b)
values (3, null)
insert into test1 (a, b)
values (4, null)
insert into test1 (a, b)
values (5, 'B')
insert into test1 (a, b)
values (6, null)
insert into test1 (a, b)
values (7, null)
insert into test1 (a, b)
values (8, 'C')
insert into test1 (a, b)
values (9, null)
insert into test1 (a, b)
values (10, null)
insert into test1 (a, b)
values (11, null)
insert into test1 (a, b)
values (12, null)
insert into test1 (a, b)
values (13, null)
insert into test1 (a, b)
values (14, 'D')
insert into test1 (a, b)
values (15, null)
insert into test1 (a, b)
values (16, null)
insert into test1 (a, b)
values (17, null)
insert into test1 (a, b)
values (18, null)
insert into test1 (a, b)
values (19, 'E')
I would like to write a query that can produce these results:
insert into test1 (a, b)
values (1, 'A')
insert into test1 (a, b)
values (2, 'A')
insert into test1 (a, b)
values (3, 'A')
insert into test1 (a, b)
values (4, 'A')
insert into test1 (a, b)
values (5, 'B')
insert into test1 (a, b)
values (6, 'B')
insert into test1 (a, b)
values (7, 'B')
insert into test1 (a, b)
values (8, 'C')
insert into test1 (a, b)
values (9, 'C')
insert into test1 (a, b)
values (10, 'C')
insert into test1 (a, b)
values (11, 'C')
insert into test1 (a, b)
values (12, 'C')
insert into test1 (a, b)
values (13, 'C')
insert into test1 (a, b)
values (14, 'D')
insert into test1 (a, b)
values (15, 'D')
insert into test1 (a, b)
values (16, 'D')
insert into test1 (a, b)
values (17, 'D')
insert into test1 (a, b)
values (18, 'D')
insert into test1 (a, b)
values (19, 'E')
If this can be done, any help is appreciated!
Thanks.
August 12, 2014 at 11:46 am
I have a working solution, but before I present it, you need to know that what you're asking for looks like a homework assignment rather than a real-world business problem. We don't do your homework for you. Yes, a query can be written to do this. What have you tried so far? Are you thinking in terms of what you can do to a column (set-based thinking), as opposed to using procedural thinking?
What can you tell us that you've noticed about the data itself? Might there be a useful subset of this data that could help be a part of the solution? I don't mind helping lead you down the path toward a solution, but YOU have to contribute to the process.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2014 at 12:09 pm
Hi Steve, thanks for your response.
This is totally a work problem, the example I provided is a very simplified version of our actual data. To give you the background: I work for a very large transit agency. In our data, our bus stops are numbered sequentially 1 to n (column 'a'). Every ten or so bus stops, we have a TimePoint, the Timepoint field is null everywhere else (column 'b'). One of our managers wants to know, from one timepoint to another, what is the maximum passenger load, and what the preceding timepoint was. In the current state of our data, I can only find the passenger load AT THE STOP that is a timepoint.
So, if I was able to fill in the nulls with what timepoint those sequence numbers actually belong to, I can then group by that field and take the max(passengerload). I couldn't think of another way of doing this.
Thanks again!
August 12, 2014 at 12:20 pm
Okay... Now we have something better to work with. The query below includes your original example code, but modifies the table to have a clustered index based on "A". It might well suffer performance-wise because of the nature of the JOIN, which uses a sub-query. This can be dangerous when the number of records is large, so you'll want to possibly create a SPARSE index on "B", WHERE B IS NOT NULL.
Here's the SQL:
DECLARE @test1 TABLE (
A int PRIMARY KEY CLUSTERED,
B varchar(10) NULL
)
insert into @test1 (A, B) values (1, 'A')
insert into @test1 (A, B) values (2, null)
insert into @test1 (A, B) values (3, null)
insert into @test1 (A, B) values (4, null)
insert into @test1 (A, B) values (5, 'B')
insert into @test1 (A, B) values (6, null)
insert into @test1 (A, B) values (7, null)
insert into @test1 (A, B) values (8, 'C')
insert into @test1 (A, B) values (9, null)
insert into @test1 (A, B) values (10, null)
insert into @test1 (A, B) values (11, null)
insert into @test1 (A, B) values (12, null)
insert into @test1 (A, B) values (13, null)
insert into @test1 (A, B) values (14, 'D')
insert into @test1 (A, B) values (15, null)
insert into @test1 (A, B) values (16, null)
insert into @test1 (A, B) values (17, null)
insert into @test1 (A, B) values (18, null)
insert into @test1 (A, B) values (19, 'E')
;WITH NOT_NULL AS (
SELECT A, B
FROM @test1
WHERE B IS NOT NULL
)
SELECT T.A, T2.B
FROM @test1 AS T
INNER JOIN NOT_NULL AS T2
ON T2.A = (SELECT MAX(A) FROM NOT_NULL WHERE A <= T.A)
ORDER BY T.A;
Let me know what you discover with this.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2014 at 12:46 pm
Just uploading the execution plan so you can see what you might be in for. The estimates aren't very good on number of executions, so the potential for ugly is present.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2014 at 12:48 pm
Thank you very much for your efforts, I learned a new thing today.
I'm still trying to wrap my head around the join that you did. Very clever...
Now it will take some time to adapt this into the assignment, that should be the fun part!
August 12, 2014 at 12:52 pm
Don't get too attached to the idea. It's more of a quick fix, and could be problematic in terms of applying it to a larger set of data, although there is some potential for it doing well if any given route is small in terms of total number of stops. You'll want to test this to be sure you aren't creating a peformance nightmare.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply