Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SQL Query Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2014 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:26 PM
Points: 24, Visits: 76
Hi, is it possible to do the following?
I have this table:

CREATE TABLE test1(
[A] [int] NULL,
[B] [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.
Post #1602345
Posted Tuesday, August 12, 2014 11:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:50 PM
Points: 1,688, Visits: 2,271
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)

Internet ATM Machine
Post #1602394
Posted Tuesday, August 12, 2014 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:26 PM
Points: 24, Visits: 76
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!
Post #1602401
Posted Tuesday, August 12, 2014 12:20 PM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:50 PM
Points: 1,688, Visits: 2,271
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)

Internet ATM Machine
Post #1602405
Posted Tuesday, August 12, 2014 12:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:50 PM
Points: 1,688, Visits: 2,271
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)

Internet ATM Machine


  Post Attachments 
TEST1.sqlplan (0 views, 12.12 KB)
Post #1602418
Posted Tuesday, August 12, 2014 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:26 PM
Points: 24, Visits: 76
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!
Post #1602419
Posted Tuesday, August 12, 2014 12:52 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:50 PM
Points: 1,688, Visits: 2,271
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)

Internet ATM Machine
Post #1602421
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse