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.
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 1SS 753 TEX/KC 112 112 MENA, AR 2 2SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5SS 753 TEX/KC 112 112 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 KANSAS CITY, MO 7 7SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1SS 753 KC/TEX 113 113 JOPLIN, MO 2 2SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5SS 753 KC/TEX 113 113 MENA, AR 6 6SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 TEXARKANA, AR 9 9Now becomes:Product Line Table Orig/Dest Schedule Run Departure Departure# DepartureRun Arrival Arrival# ArrivalRunSS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 MENA, AR 2 2SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 FORT SMITH, AR 3 3SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 FAYETTEVILLE, AR 4 4SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 ROGERS BENTO, AR 5 5SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 TEXARKANA, AR 1 1 KANSAS CITY, MO 7 7SS 753 TEX/KC 112 112 MENA, AR 2 2 FORT SMITH, AR 3 3SS 753 TEX/KC 112 112 MENA, AR 2 2 FAYETTEVILLE, AR 4 4SS 753 TEX/KC 112 112 MENA, AR 2 2 ROGERS BENTO, AR 5 5SS 753 TEX/KC 112 112 MENA, AR 2 2 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 MENA, AR 2 2 KANSAS CITY, MO 7 7SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 FAYETTEVILLE, AR 4 4SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 ROGERS BENTO, AR 5 5SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 FORT SMITH, AR 3 3 KANSAS CITY, MO 7 7SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 ROGERS BENTO, AR 5 5SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 FAYETTEVILLE, AR 4 4 KANSAS CITY, MO 7 7SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5 JOPLIN, MO 6 6SS 753 TEX/KC 112 112 ROGERS BENTO, AR 5 5 KANSAS CITY, MO 7 7SS 753 TEX/KC 112 112 JOPLIN, MO 6 6 KANSAS CITY, MO 7 7SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 JOPLIN, MO 2 2SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 ROGERS BENTO, AR 3 3SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 FAYETTEVILLE, AR 4 4SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 FORT SMITH, AR 5 5SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 MENA, AR 6 6SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 KANSAS CITY, MO 1 1 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 ROGERS BENTO, AR 3 3SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 FAYETTEVILLE, AR 4 4SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 FORT SMITH, AR 5 5SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 MENA, AR 6 6SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 JOPLIN, MO 2 2 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 FAYETTEVILLE, AR 4 4SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 FORT SMITH, AR 5 5SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 MENA, AR 6 6SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 ROGERS BENTO, AR 3 3 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 FORT SMITH, AR 5 5SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 MENA, AR 6 6SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 FAYETTEVILLE, AR 4 4 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 MENA, AR 6 6SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 FORT SMITH, AR 5 5 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 MENA, AR 6 6 DE QUEEN, AR 7 7SS 753 KC/TEX 113 113 MENA, AR 6 6 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 MENA, AR 6 6 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7 LOCKESBURG, AR 8 8SS 753 KC/TEX 113 113 DE QUEEN, AR 7 7 TEXARKANA, AR 9 9SS 753 KC/TEX 113 113 LOCKESBURG, AR 8 8 TEXARKANA, AR 9 9Again..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 t1INNER JOIN Table t2ON 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.RunAND t2.[SCHEDSTOP#] > t1.[SCHEDSTOP#][/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|