Conditional SQL syntax

  • Thread starter Thread starter Kevin Witty
  • Start date Start date
K

Kevin Witty

I have a table which contains (roughly) a Route# for Mon, Tue, Wed, etc. and
an override Route# and date. I'd like to run a query to which I supply a
date and which says:

In this column, if the date I supply matches the override date, show the
override route#, otherwise, if there is a Route# for the weekday of the date
I supply, show the Route# for that date.

I thought this would be fairly simple, but it ain't workin'.

iif(OverRideDate = SuppliedDate,OverrideRouteNum, iif(weekday(SuppliedDate)
= 2,MondayRouteNum,iif(... etc.

only gives me a value if the first condition is True, and nothing if False.
What am I doing wrong here???

Thanks,

Kevin
 
Kevin said:
I have a table which contains (roughly) a Route# for Mon, Tue, Wed, etc. and
an override Route# and date. I'd like to run a query to which I supply a
date and which says:

In this column, if the date I supply matches the override date, show the
override route#, otherwise, if there is a Route# for the weekday of the date
I supply, show the Route# for that date.

I thought this would be fairly simple, but it ain't workin'.

iif(OverRideDate = SuppliedDate,OverrideRouteNum, iif(weekday(SuppliedDate)
= 2,MondayRouteNum,iif(... etc.

only gives me a value if the first condition is True, and nothing if False.
What am I doing wrong here???
Click to expand...

I have no clear picture of your data yet. Especially the (roughly) keeps
me behind the fence ;-)

My intuition tells me you can use a LEFT JOIN here, however I cannot
tell you how.

Have you got default routes for every weekday? Or, equivalently, for
every weekday your parameter date may be? In that case you can make a
query that suggests a route, taking the weekday of the parameter date to
its route. Left join this query on the override; in the output, use an
expression close to

foundRoute: iif(isnull(overridedate),routenumfromquery,overrideroutenum)

You tell me what your tables look like, I provide more details. Deal? Or
you understand perfectly what I mean and figure the rest out yourself :-)
 
Sorry I was unclear. The (abbreviated) table looks like:

CustomerKey,MondayRouteNum,TuesdayRouteNum,...OverRideDate,OverrideRouteNum.

Some records have a routenum for only one day, some for more, some for none,
some have an override date. I want records which have an override date = the
one I supply, or have a routenum in the appropriate day field, and to return,
among other fields, a single field with the appropriate routenum.

The select statement is composed in VBA as

"select * from MyTable where OverrideDate = #" & SuppliedDate & "# or " &
format(SuplliedDate,"dddd") & "RouteNum is not null;"

What I'm trying to do is replace the * with a list of field names, including
a conditional column:

"iif(not isnull(OverrideRouteNum),OverrideRouteNum,iif(weekday(" &
SuppliedDate & ") = 2,MondayRouteNum,iif(weekday(" & SuppliedDate & ") =
3,TuesdayRouteNum,....))) as RouteNum"

I'm not sure how a join would come into this.

Kevin
 
Sorry I was unclear. The (abbreviated) table looks like:

CustomerKey,MondayRouteNum,TuesdayRouteNum,...OverRideDate,OverrideRouteNum.
Click to expand...

Then your table is incorrectly structured, Kevin. You're "committing
spreadsheet" by storing data (days) in fieldnames. You actually have a
one (customer) to many (RouteNum) relationship; it should be modeled
using two tables as a one to many relationship.
Some records have a routenum for only one day, some for more, some for none,
some have an override date. I want records which have an override date = the
one I supply, or have a routenum in the appropriate day field, and to return,
among other fields, a single field with the appropriate routenum.
Click to expand...

Then the many table needs fields for the basic route and the override
date.
The select statement is composed in VBA as

"select * from MyTable where OverrideDate = #" & SuppliedDate & "# or " &
format(SuplliedDate,"dddd") & "RouteNum is not null;"

What I'm trying to do is replace the * with a list of field names, including
a conditional column:

"iif(not isnull(OverrideRouteNum),OverrideRouteNum,iif(weekday(" &
SuppliedDate & ") = 2,MondayRouteNum,iif(weekday(" & SuppliedDate & ") =
3,TuesdayRouteNum,....))) as RouteNum"

I'm not sure how a join would come into this.
Click to expand...

It would... if your table were correctly normalized.

John W. Vinson[MVP]
 
Hi, John!

Remember that old saying, "Things should be as normal as possible, but not
too normal"? This is one of those, I think It's working very nicely for
presentation and for effficiency. The only problem I'm having is making that
SQL code work. This table is the "driver", i.e., scheduler, for when orders
are released, not for the orders themselves, i.e., a customer is scheduled to
get a pickup every Monday, Wednesday and Thursday, and this table drives the
creation of the actual orders, which are normalized. I originally started by
normalizing it and decided it was more trouble than it was worth, and there
were more benefits in denormalizing it. Am I forgiven?

And if so, what's wrong with my SQL code?

Best,

Kevin
 
Back
Top