help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with a query

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-01 : 10:11:46

I am looking to get a count of how many times a driver went from one state to another

table is setup like this
Start to stop1 is a move and stop1 to stop2 is a move
if stop1 is empty then count from start to stop2

the code I tried returns several results and too many query results (its below for reference) help

Driver# start stop1 stop2
001 LA FL LA
002 MS LA
003 SC FL LA


-- in-memory state table to hold distinct state_id 
--DECLARE @state_id int
DECLARE @i int
DECLARE @p int
DECLARE @numrows int
DECLARE @vState1 nvarchar(2)--State abreviated
DECLARE @vState2 nvarchar(2)
DECLARE @vState3 nvarchar(2)
DECLARE @state_table1 TABLE ( 
    idx1 smallint Primary Key IDENTITY(1,1)
    , state_start nvarchar(2)
)
-- populate tmp table 
INSERT @state_table1 
SELECT distinct Right(LoadUnload,2) as state_Table1 FROM dbo.BOA_CharlestonNormalized
INSERT INTO @state_table1  VALUES ('CA')

-- enumerate tmp table 
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @state_table1)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx1) FROM @state_table1))
    BEGIN
        -- get next primary
        SET @vState1 = (SELECT state_start FROM @state_table1 WHERE idx1 = @i)

-- enumerate tmp table 
SET @p = 1

    WHILE (@p <= (SELECT MAX(idx1) FROM @state_table1))
    BEGIN
        -- get next primary
        SET @vState2 = (SELECT state_start FROM @state_table1 WHERE idx1 = @p)

SELECT COUNT(*), @vState1, @vstate2
FROM dbo.BOA_CharlestonNormalized Where Right(PickAt,2) = @vState1 AND Right(LoadUnload,2) = @vState2
        SET @p = @p + 1

		END   

     -- increment counter for next state
        SET @i = @i + 1
    END

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-01 : 10:34:55

if I could return this in a single select instead of enumerating is the goal I am after... I got the enumeration to work.. but a single selct for results would be much more efficient..
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-01 : 11:20:21

This seems to work - but you didn't post your expected output based on your sample so I'm not sure.


declare @t table (Driver# int,  start varchar(2), stop1 varchar(2), stop2 varchar(2))
insert @t
select 001, 'LA', 'FL', 'LA' union all
select 002, 'MS', 'LA', null union all
select 003, 'SC', 'FL', 'LA'

select driver#
       ,case when start != stop1 then 1 else 0 end 
        + case when stop1 != stop2 then 1 else 0 end 
        as moves
from   @t


OUTPUT:
driver#     moves
----------- -----------
1           2
2           1
3           2

EDIT:
I just looked at your code that you said works and I'm confused because I don't even see any references to the columns you said are in your table? (start stop1, stop2)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources