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
 General SQL Server Forums
 New to SQL Server Programming
 Very new to SQL, Need to expand this table.

Author  Topic 

rylan020
Starting Member

3 Posts

Posted - 2014-01-09 : 10:11:28
Hello Everybody! First time poster here.

I'm very new to SQL..The only familiarity I've had with it is a few lines of code I managed to successfully write over several hours of trial and error. Rather than go through that pain again I figured I'd give you geniuses a try. Here's my quandry:

I've been charged with creating a dimensional model for the small bus company I work for. The first step was to create a table including every "stop" for every "schedule" on our run. That alone is over 700 rows of data but I need to now break it down further to show every departure/arrival combination grouped by runs. I'll expand the first two runs to give you a better idea:

Product Line Table Orig/Dest Schedule Run Stop SCHEDSTOP# RUNSTOP#
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1
SS 753 TEX/KC 112 112 MENA, AR 2 2
SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3
SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4
SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5
SS 753 TEX/KC 112 112 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 KANSAS CITY, MO 7 7
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4
SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5
SS 753 KC/TEX 113 113 MENA, AR 6 6
SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 TEXARKANA, AR 9 9


Now becomes:


Product Line Table Orig/Dest Schedule Run Departure Departure# DepartureRun Arrival Arrival# ArrivalRun
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 MENA, AR 2 2
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 FORT SMITH, AR 3 3
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 FAYETTEVILLE, AR 4 4
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 ROGERS BENTO, AR 5 5
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 KANSAS CITY, MO 7 7
SS 753 TEX/KC 112 112 MENA, AR 2 2 FORT SMITH, AR 3 3
SS 753 TEX/KC 112 112 MENA, AR 2 2 FAYETTEVILLE, AR 4 4
SS 753 TEX/KC 112 112 MENA, AR 2 2 ROGERS BENTO, AR 5 5
SS 753 TEX/KC 112 112 MENA, AR 2 2 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 MENA, AR 2 2 KANSAS CITY, MO 7 7
SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 FAYETTEVILLE, AR 4 4
SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 ROGERS BENTO, AR 5 5
SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 KANSAS CITY, MO 7 7
SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 ROGERS BENTO, AR 5 5
SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 KANSAS CITY, MO 7 7
SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5 JOPLIN, MO 6 6
SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5 KANSAS CITY, MO 7 7
SS 753 TEX/KC 112 112 JOPLIN, MO 6 6 KANSAS CITY, MO 7 7
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 JOPLIN, MO 2 2
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 ROGERS BENTO, AR 3 3
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 FAYETTEVILLE, AR 4 4
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 FORT SMITH, AR 5 5
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 MENA, AR 6 6
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 ROGERS BENTO, AR 3 3
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 FAYETTEVILLE, AR 4 4
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 FORT SMITH, AR 5 5
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 MENA, AR 6 6
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 FAYETTEVILLE, AR 4 4
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 FORT SMITH, AR 5 5
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 MENA, AR 6 6
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 FORT SMITH, AR 5 5
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 MENA, AR 6 6
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 MENA, AR 6 6
SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 MENA, AR 6 6 DE QUEEN, AR 7 7
SS 753 KC/TEX 113 113 MENA, AR 6 6 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 MENA, AR 6 6 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7 LOCKESBURG, AR 8 8
SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7 TEXARKANA, AR 9 9
SS 753 KC/TEX 113 113 LOCKESBURG, AR 8 8 TEXARKANA, AR 9 9


Again..First time posting so I may haven't formatted this question correctly, but can anybody help me out with the language to execute this? Note I only need to show arrivals which have a "SchedStop#"higher than the departure#.

If you need any more clarification, please ask :)

Glad to become a part of this forum, I see this being an incredibly helpful tool as I develop my SQL skills.

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 05:05:55
[code]
SELECT t1.Product,
t1.Line,
t1.[Table],
t1.[Orig/Dest],
t1.Schedule,
t1.Run,
t1.Stop AS Departure,
t1.[SCHEDSTOP#] AS [Departure#] ,
t1.[RUNSTOP#] AS [DepartureRun],
t2.Stop AS Arrival,
t2.[SCHEDSTOP#] AS [Arrival#] ,
t2.[RUNSTOP#] AS [ArrivalRun]
FROM Table t1
INNER JOIN Table t2
ON t2.Product = t1.Product
AND t2.Line = t1.Line
AND t2.[Table] = t1.[Table]
AND t2.[Orig/Dest] = t1.[Orig/Dest]
AND t2.Schedule = t1.Schedule
AND t2.Run = t1.Run
AND t2.[SCHEDSTOP#] > t1.[SCHEDSTOP#]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -