Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Why my SQL gives me wrong results
Message
From
27/07/2006 11:47:42
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 7 SP1
OS:
Windows XP SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01140687
Message ID:
01140733
Views:
17
Terry,
Didn't check throroughly, just some code to get 'overlapping' in an easier way:
where NET.Start_date < m.ldEndCurr and  NET.End_date > m.ldStartCurr
instead of multiple 'or's.
Cetin


>Really need some help here guys as I'm away on vacs this w/e and I've got to get this working, or everyone's screwed!
>
>a) Not only is the following code giving me wrong results, but
>b) it's also selecting data for the chosen operator (given by lnOpCode - numeric) PLUS all those recs after those for the chosen Operator (table sorted by Op Code)
>
>Essentially, there are recs with a start date and an end date. I have to select all thoise recs that fall within either of two date ranges (current and previous). If we take just the current period for an example, the start date and end date of each record needn't necessarilly fall all within the date range, but may start before, end after, or both, but at least part of their range must fall within the chosen subject date range, as in the following diagram.
>
>
>
> DATES:  Start Curr        End Curr
>             |               |
>    <------------------------------>
>    <--------------->        |
>             | <------------------->
>             | <-----------> |
>
>With Thisform
>  lnOpCode	= .nOpCode
>  ldStartCurr	= .dStartDate1
>  ldEndCurr	= .dEndDate1 + 1
>  ldStartPrev	= .dStartDate2
>  ldEndPrev	= .dEndDate2 + 1
>EndWith
>
>...
>
>Select NotETM
>Locate
>Wait WINDOW "Non-ETM Data: Finding Journeys partially or wholly within the Time periods ..." NOWAIT NOCLEAR
>SELECT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ;
>	IIF( NET.Route == NET.Mcl_Rte, "    ", NET.Mcl_Rte) as ContrNum, ;
>	0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, rf.ServType ;	
>  FROM NotETM NET ;
>    JOIN Operator op ;
>	On 	NET.Operator == op.Code ;
>    JOIN RteFam rf ;
>	On	NET.Operator == rf.Operator ;
>		and NET.Route    == rf.ERoute ;
>  WHERE NET.Start_date < ldStartCurr and NET.End_date > ldEndCurr ;
>  OR	(	NET.Start_date BETWEEN ldStartCurr and ldEndCurr ;
>  	  and	NET.End_date BETWEEN ldStartCurr and ldEndCurr ) ;
>  OR	(	NET.Start_date BETWEEN ldStartCurr and ldEndCurr ;
>  	  and	NET.End_date > ldEndCurr) ;
>  OR	(	NET.Start_date < ldStartCurr ;
>  	  and	NET.End_date BETWEEN ldStartCurr and ldEndCurr) ;
>  OR	NET.Start_date < ldStartPrev and NET.End_date > ldEndPrev ;
>  OR	(	NET.Start_date BETWEEN ldStartPrev and ldEndPrev ;
>  	  and	NET.End_date BETWEEN ldStartPrev and ldEndPrev ) ;
>  OR	(	NET.Start_date BETWEEN ldStartPrev and ldEndPrev ;
>  	  and	NET.End_date > ldEndPrev) ;
>  OR	(	NET.Start_date < ldStartPrev ;
>  	  and	NET.End_date BETWEEN ldStartPrev and ldEndPrev) ;
>  AND	lnOpCode	==  op.Code;
>  GROUP BY	NET.Mcl_Rte, NET.Operator, NET.Route ;
>  ORDER BY	NET.Operator, NET.Route ;
>  INTO CURSOR csrNotETMPassengers NOFILTER READWRITE
>
>
>
>Curiously I'm getting, for my selected operator, 3 recs in that range, as is correct, (and in this case all 3 recs fit the date range perfectly) but : the 1st rec selected has the correct value for "Passengers" in the selection, but the other 2 don't.
>
>This is seriously doing my head in
>
>REALLY appreciate it!
>
>Terry
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform