How would I select one record and not the another related record in one
Select statement?

I have regular prices that I want to display on most days and promotional
prices on particular days (christmas, summer etc) where I don't want to
display the regular price if I am displaying the promotional price.

I have a table that has 2 types of records in it.

One record is a normal ticket price on a ship.  The other record is a
promotional price for that ship.

If it is a Regular Price, there will be null values in the PromoPriceID,
StartDate and End Date (as these are only used for the promotional prices).

If it is a Promotional Price there will be a Null Value in ShipName (as I
would get that from the regular record).  The PromoPriceID would contain the
Regular records ShipPricingID (so that it could get the name of the ship and
so that we would know not to display that records regular price).   The
StartDate and EndDate would have the date range the promotional prices ran
for.

Here is a test file setup with the display of all the records.
**********************************************************************************************************
drop table shipPricing

create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)

insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
insert shipPricing(PromoPriceID,Price,StartDate,EndDate) values
(Scope_Identity(),1300,'12/01/05','12/31/05')
insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
insert shipPricing(PromoPriceID,Price,StartDate,EndDate) values
(Scope_Identity(),2050,'12/15/05','12/31/05')

select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID, Price
= Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) from shipPricing

ShipPricingID ShipName             PromoPriceID    Price
StartDate    EndDate
------------- -------------------- ------------       ------------   ------------
------------
1                    Sea Witch              NULL                1,500.00
NULL         NULL
2                    NULL                    1
1,300.00      12/01/05     12/31/05
3                    Southern Cross       NULL                2,200.00
NULL         NULL
4                    NULL                    3
2,050.00     12/15/05     12/31/05
**********************************************************************************************************************

What I want to do is have a Select statement that would give me back the
prices available for a particular date (Select ... where date = some date).
If it is a promotion I don't want to show the regular price and  I would
like it to say something like "Promo".

For example:
If I want the prices for 11/15/01 I should get something like:

ShipPricingID ShipName             PromoPriceID    Price
StartDate    EndDate
------------- -------------------- ------------       ------------   ------------
------------
1                    Sea Witch              NULL                1,500.00
NULL         NULL
3                    Southern Cross       NULL                2,200.00
NULL         NULL

If I want prices for 12/05/05, I should get something like:

ShipPricingID ShipName             PromoPriceID    Price
StartDate    EndDate
------------- -------------------- ------------       ------------   ------------
------------
2                    Sea Witch               1
1,300.00      12/01/05     12/31/05      Promo
3                    Southern Cross       NULL                2,200.00
NULL         NULL

If I want prices for 12/18/05, I should get something like:

ShipPricingID ShipName             PromoPriceID    Price
StartDate    EndDate
------------- -------------------- ------------       ------------   ------------
------------
2                    Sea Witch               1
1,300.00      12/01/05     12/31/05        Promo
4                    Southern Cross       3                         2,050.00
12/15/05     12/31/05        Promo

I can't figure out how to select one record and not the other in one Select
statement.

Thanks,

Tom

you could do it with a simple if exists statement, like so:

-- let's assume you've got some kind of variable with your Date value
in it
DECLARE @date datetime

IF EXISTS (SELECT * FROM shipPricing
  WHERE @date > StartDate AND @date < EndDate)
  SELECT * FROM shipPricing WHERE @date > StartDate AND @date < EndDate
ELSE
  SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL

if the exists is true, you won't likely be taxed for the second query,
because the rows will already be cached from the if condition test. so
i'm pretty sure performance-wise you'll only be running 1 query,
essentially. also, you can simply replace * with whatever you want to
return, and since the promo stuff is in its own query, you can throw in
a 'Promo' value in for kicks.

Show quote Hide quote This was the problem I had.

The problem is that you can either get the Promo prices (those with dates)
or regular prices (those without dates).  The problem is that I want to
either a regular price OR a promotional price for both ships.

Here are the results if you add in dates you can see the problem.  In the
first 2 it would either be on or the other for those dates, but the last
date should have the regular price for the Sea Witch and the promo price for
the other.  Also I need to grab the name of the ship from the regular price
record if a promo price.

*******************************************************************************************************************
declare @date smalldatetime

select @date = '11/01/05'

IF EXISTS (SELECT * FROM shipPricing
  WHERE @date > StartDate AND @date < EndDate)
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE @date >
StartDate AND @date < EndDate
ELSE
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL

ShipPricingID ShipName             PromoPriceID      Price        StartDate
EndDate
------------- -------------------- ------------ ------------ ------------ ------------
1                    Sea Witch               NULL              1,500.00
NULL         NULL
3                    Southern Cross       NULL              2,200.00
NULL         NULL

**********************************************************************************************************************

*****************************************************************************************************************
declare @date smalldatetime

select @date = '12/16/05'

IF EXISTS (SELECT * FROM shipPricing
  WHERE @date > StartDate AND @date < EndDate)
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE @date >
StartDate AND @date < EndDate
ELSE
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL

ShipPricingID ShipName             PromoPriceID     Price
StartDate    EndDate
------------- -------------------- ------------       ------------ ------------
------------
2                     NULL                 1
1,300.00     12/01/05     12/31/05
4                     NULL                 3
2,050.00     12/15/05     12/31/05

************************************************************************************************************************
************************************************************************************************************************
declare @date smalldatetime

select @date = '12/03/05'

IF EXISTS (SELECT * FROM shipPricing
  WHERE @date > StartDate AND @date < EndDate)
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE @date >
StartDate AND @date < EndDate
ELSE
  SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12),StartDate =
Substring(Convert(varChar,StartDate,1),1,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,12) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL


ShipPricingID ShipName             PromoPriceID     Price
StartDate    EndDate
------------- -------------------- ------------       ------------ ------------
------------
2                     NULL                 1
1,300.00     12/01/05     12/31/05
*************************************************************************************************************************

Thanks,

Tom
Show quoteHide quote

also, having the condition of one entity versus another being
determined by the presence or absense of values in a field seems a
little odd to me. if it's not a huge conversion, i would consider
migrating this data into two tables: shipPricingStandard, and
shipPricingPromo. that way your queries would look more like:

IF EXISTS (SELECT * FROM shipPricingPromo
  WHERE @date > StartDate AND @date < EndDate)
  SELECT * FROM shipPricingPromo WHERE @date > StartDate AND @date <
EndDate
ELSE
  SELECT * FROM shipPricingStandard

not sure why, but it seems like a clearer division of what seem like
two similar, but separate entities. but that's probably open to debate,
and more cosmetic than anything.

hope this helps,

jason

The reason I didn't pick 2 tables is the data is essentially the same except
for type of record as well as the dates.  I could also add a type code
field, but I can do that by testing for the shipPricingID (if nothing there
it is a regular price) as well as the dates (again if nothing there it is a
regular record).

Tom
Show quoteHide quote

tsad - try this:

declare @dt datetime
set @dt = cast('12/18/05' as datetime)

select
case
when @dt between s2.startdate and s2.enddate then s2.ShipPricingID
else s.ShipPricingID
end as ShipPricingID,
ShipName = substring(s.ShipName,1,20),
case
when @dt between s2.startdate and s2.enddate then s2.PromoPriceID
else s.PromoPriceID
end as PromoPriceID,
case
when @dt between s2.startdate and s2.enddate then s2.Price
else s.Price
end as Price,
case
when @dt between s2.startdate and s2.enddate then s2.StartDate
else s.StartDate
end as StartDate,
case
when @dt between s2.startdate and s2.enddate then s2.EndDate
else s.EndDate
end as EndDate,
case
when @dt between s2.startdate and s2.enddate then 'PROMO'
else NULL
end as Promo
from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
s2.PromoPriceID


Show quoteHide quote2,050.00
Show quoteHide quote

Show quote Hide quote Seems to do the job, except it shows Promotional records when it should show
Regular records and vice versa.  I am sure it is just that one of the tests
is in reverse.  Just need to look at it to see exactly what it does.

Just what I need, though.

Thanks a lot,

Tom
Show quoteHide quote

This is not a table.  It has no key, uses proprietary data types, etc.
This one table has more not null-able columns than the payroll for a
major auto manufacture.

Are you aware that "camelCase" adds 8-12% more time to reading code?
The eye jumps to the uppercase letter, then back to the front of the
word.

Is there really a ship with a CHAR(50) name or were you just too lazy
to pick a proper size?  With the complete lack of data integrity in
this schema, you will get one.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.  IDENTITY cannot ever be a key
**by definition**, which I would hope you have learned by now.

CREATE TABLE VoyagePricing -- were you pricing the ships or the
trips?
(ship_name VARCHAR(30) NOT NULL -- I would use a ship code
  CHECK (ship_name IN (..)),
promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
trip_price DECIMAL(7,2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (ship_name, start_date)
);

Notice the use of duration ranges.  Promos are nested inside the
regular fare ranges.

That is really bad business!  You can show them what they are saving
with this query.

SELECT ship_name, promo_name, trip_price
  FROM VoyagePricing AS P
WHERE @my_date BETWEEN start_date AND end_date;

This will give you both the promo and regular prices.  I assume that we
give the customer the lower price.

SELECT ship_name, MIN(trip_price)
  FROM VoyagePricing
WHERE @my_date BETWEEN start_date AND end_date
GROUP BY ship_name;

You keep posting the worst code of anyone in this newsgroup.  Can you
get your boss to pay for a basic RDBMS course for you and the other
programmers?

A little over a year ago, I got to watch incompetent RDBMS programmers
like you kill children in Africa by messing up a medical supply system.

You are such an arrogant ass.  You are blaming this guy for killing
children?  Good god, man...that is just over the freaking line.

I'm sure your monthly check to Pat Robertson is in the mail.  You sound just
like him.

WhY dOnT yOu JuSt StOp ReAdInG tHiS nEwSgRoUp.


Show quoteHide quoteparticular date (SELECT ... WHERE date = some date). If it is a promotion I
don't want to show the regular price and I would like it to say something
like "Promo".  <<
Show quoteHide quote

That is really bad business!  You can show them what they are saving
with this query
<<

now that's funny, you seem to think you know their business better than
they?

;)

BTW, I spent today's lunch break in the nearest Border's.
They used to carry SQL Puzzles, but they don't any more

Nice Rant.

First of all, and you seem to miss the point, this is not my actual table.
If you look closely (and I know this is difficult), there are NO ship
details.  That is because "I MADE THIS TABLE UP JUST TO IRRITATE YOU".

The table has exactly what I felt was necessary to illustrate my problem and
allow others to quickly run it (if they want) without wasting anyones time.
I am not worrying about keys here, I am not worried about indexes, I am not
worried about the size of the Ship name (anywhy would you think a 50 was not
a proper size for a ship name - too big, too little??????)

Everyone here has been great and I appreciate the time that people take to
help others.  I would not presume on their time by not putting only the
necessary elements to illustrate the problem.  I'm not sure where
nullable/non nullable, camelCase, Pascal, Uppercase, lower case, variable
size etc. has anything to do with the question I was asking.

BTW, you weren't incorrect in your assessment as to what I was looking for.
VC got it.  What happened to you?  This was the reason I made the table as
spartan as possible as well as multiple examples to show what I was looking
for.  You obviously missed it.

It is a table.

A table must have keys?

And you even call it a table (one that has more not nullable columns ...).
It has 2 BTW, one being identity column, which by definition would not be
nullable anyway.

create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)

What propriety data types?

Never seen that statistic.  Where did you get that from?

Actually, I use camelCase for variable names (as do many).  I also don't do
ship_names, I do ShipNames or shipNames.  I never liked the underscore.  But
that's just me.

You use ShipNames and ship_names style - but that's just you.

YUP.

schema?  What schema?  This is just a table (wait a minute, this isn't a
table) !  :)
Cannot ever be a key?????

By what definition ???

Here is one I have read:

Definition: The primary key of a relational table uniquely identifies each
record in the table. It can either be a normal attribute that is guaranteed
to be unique (such as Social Security Number in a table with no more than
one record per person) or it can be generated by the DBMS (such as a
globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys
may consist of a single attribute or multiple attributes in combination.

Does an Identity uniquely identify each record in the table?

Is it guaranteed to be unique?   ( by definition)

Is it generated by the DBMS?????

Single attribute?

Why is that even a question and what does it have to do with the question?
Maybe I'm pricing the popcorn on the ship.  What difference does it make?

So would I, but I wanted to see the best way to get the name both from the
record that had the name and the Promo that didn't, but refered to the
record that had the name.

Not really quite sure what you are doing here.  There are 2 prices one
regular and one or more Promotional record that would display (instead of)
the regular price between the start and end dates (such as Chrismas in my
examples).

Not what I am trying to do.  How does this handle multiple Promos?????
where is the Date Range for the Promos??????

Bad Business???  How about incorrect assessment of the question? or the
problem?

Won't give them what I was trying to give them.

Also, won't give them what I was trying to give them.  You have what I
needed above - how did you miss it????  "If it is a promotion I don't want
to show the regular price and I would like it to say something like "Promo".

Where is that done with either of your examples?????

Ok, let me change the statement slightly.

"If the date that I am asking for is between any of the dates in the
Promotion records (row) for each Ship, display price for that row as well as
the ships name which you can get from the regular price record of that ship
and also pass back the word "Promo".  If the date requested is not between
any of the dates in any of the Promotion records (row) for each ship, then
show the regular price and blank (null) instead of the word 'Promo'".  In
any case, I should get 1 and only 1 record (row) back for each ship (either
a regular price or a promotional price)."

Could be the case.  If I was an expert, I wouldn't be asking questions.

<Can you
Already been there done that (many years ago).  But I am also not an RDBMS
expert (as you may have gathered).  And I am sure there are many ways to
skin a cat, as can be seen in this group.  They can be done multiple ways
and still be right.

Never been to Africa.

Tom

I am going to make an assumption that prices belong to each ship and
each date range.  Top of the line ships cost more than a dingy, Summer
season costs more than Winter, etc.

Let's make a table for promotional data

CREATE TABLE PromoTrips
(promo_code INTEGER NOT NULL PRIMARY KEY
   CHECK (promo_code >= 0), -- zero is always regular price
promo_name CHAR(15) DEFAULT 'Regular Price' NOT NULL,
..);
INSERT INTO PromoTrips VALUES (0, 'Regular Price');
INSERT INTO PromoTrips VALUES (1, 'Mutiny Special');

Always code the regular price as zero.

CREATE TABLE VoyagePricing
(ship_name VARCHAR(30) NOT NULL,
promo_code INTEGER DEFAULT 0 NOT NULL
  REFERENCES PromoTrips(promo_code),
trip_price DECIMAL(7,2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (ship_name, start_date));

Put the regular price in a huge date range.  The promos are nested
inside these ranges

INSERT INTO VoyagePricing
VALUES ('HMS Bounty', 0, 1000.00, '2000-01-01', '2010-12-31');
INSERT INTO VoyagePricing
VALUES ('HMS Bounty', 1, 750.00, '2005-08-01', '2005-08-30');

I will look for minimum price for a given date. If you drop the WHERE
clause, you get complete data instead of hidding information.

DECLARE @my_date DATETIME;
SET @my_date = '2005-08-25';

SELECT ship_name, promo_name, trip_date, trip_price
  FROM (SELECT V1.ship_name, P.promo_name, @my_date, V1.trip_price
          FROM VoyagePricing AS V1, PromoTrips AS P
         WHERE @my_date BETWEEN V1.start_date AND V1.end_date
           AND V1.promo_code = P.promo_code
        ) AS X (ship_name, promo_name, trip_date, trip_price)
WHERE X.trip_price
       = (SELECT MIN (trip_price)
            FROM VoyagePricing AS V2
           WHERE @my_date BETWEEN V2.start_date AND V2.end_date
             AND V2.ship_name = X.ship_name);

Another version will pick the highest promo code from multiple
promotions:

SELECT X.ship_name, X.promo_name, X.trip_date, X.trip_price
  FROM (SELECT V1.ship_name, P.promo_name, P.promo_code, @my_date,
V1.trip_price
          FROM VoyagePricing AS V1, PromoTrips AS P
         WHERE @my_date BETWEEN V1.start_date AND V1.end_date
           AND V1.promo_code = P.promo_code
        ) AS X (ship_name, promo_name, promo_code, trip_date,
trip_price)
WHERE X.promo_code
       = (SELECT MAX (V2.promo_code)
            FROM VoyagePricing AS V2
           WHERE @my_date BETWEEN V2.start_date AND V2.end_date
             AND V2.ship_name = X.ship_name);

tshad wrote:
Can I ask why you don't have two tables - one with just the ship
pricing (for the regular price - surely every ship must have a regular
price), and then a seperate table, foreign keying to the first,
containing the promotions? It seems like you're actually trying to
squeeze two tables into one, even though they don't share any
attributes?

Damien

Show quote Hide quote Actually, I was back and forth on whether to have 2 tables.  But actually
they will share many fields.  Price is one.  But also Duration, there is a
Quantities field (which on the regular price is an actually quantity and on
a Promotion field it will be a delta value added to the regular quantity -
same with Duration).  Type of price is the same.  So really the only
difference is that the Promotional price has a date range when it is good
far and ID of the Regular price it is replacing.

In an old system I had over 15 years ago, this was the way we did it and
found trying to coordinate the tables for pricing and invoicing was a bit of
a headache.  Of course, that was an Isam and not an RDBMS.

I am not sure having 2 tables would make it any easier (and I could be wrong
here).  We do have multiple tables for different types of pricing (air
table, ship pricing table, car pricing table etc) - and these don't share
any similarities, except that they also would have promotions.

Tom
Show quoteHide quote

can't return zero when no rows exist using aggregate function sum
Trigger not executing stored proc ...
UPDATE statement behavior with derived table
Table name as a variable
Easy Convert Question