Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Please help with this (simple?) query


Hello,

I have what is probably a very simple query to write, but I can't seem
to get my head around it.

I have a table that hold information about possible delivery methods
used by a company. In its simplest form, this table looks like...

create table DeliveryMethods (
DeliveryID int not null default 0 primary key,
DeliveryMethod varchar(50) not null default ''
)

where sample data might be...

insert DeliveryMethods (DeliveryID, DeliveryMethod)
values (1, 'Royal Mail 2nd Class Recorded')
insert DeliveryMethods (DeliveryID, DeliveryMethod)
values (2, 'Royal Mail Standard Parcels')

I then have another table that holds the delivery rates by weight for
these methods...

create table DeliveryRates (
DeliveryID int foreign key references DeliveryMethods(DeliveryID),
Weight int not null default 0,
Rate float not null default 0.0
)

where sample data might look like...

insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 100, 1.62)
insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 250, 1.90)
insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 500, 2.22)
insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 750, 2.62)
insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1000, 3.85)
insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1500, 4.95)
insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 2000, 5.68)
insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 4000, 8.24)

Obviously the real data would be a lot more than this, but this gives
you the idea.

I have a couple of tasks here...

1) to be able to pull out the delivery rate for each method, given a
specific weight. For example, if the weight were 300g, then I would want
to get the following data returned...

DeliveryID, Rate
1, 2.22
2, 3.85

Which would tell me that delivery by 2nd class recorded would be $2.22
and delivery by standard parcels would be $3.85. My current attempt at
this is the following...

select dm.deliveryid, dr.Rate
from DeliveryMethods as dm
inner join DeliveryRates as dr
On dr.DeliveryID=dm.DeliveryID
Where dr.Weight>0 and dr.Rate>0
And @weight<=dr.Weight
and Rate = (select top 1 Rate from DeliveryRates as dr2 where
@weight<=dr2.Weight and DeliveryID=dm.DeliveryID order by Rate asc)
order by dm.DeliveryID

This seems to work, but looks inelegant to me. Can anyone suggest a
neater and/or more efficient way of doing it?

2) the other task is similar, but requires me to return just the single
lowest delivery rate, instead of one rate for each method. I'm stuck on
this one.

Anyone able to help? Thanks a lot.

--
Alan Silver
(anything added below this line is nothing to do with me)

Solution to 1:
select
        a.deliveryID,
        a.rate
from
        deliveryrates a
        inner join
                (
                        select
                                deliveryID,
                                min(weight) w
                        from
                                deliveryrates
                        where
                                weight >= 1000
                        group by
                                deliveryID) b

                on a.deliveryid = b.deliveryid
                        and a.weight = b.w

On May 28, 12:04 pm, Alan Silver <alan-sil@nospam.thanx.invalid>
wrote:

Answer to #2:

select top 1
        deliveryID,
        rate
from
        deliveryrates
where
        weight >= 300
order by
        rate

On May 28, 12:32 pm, Jason Lepack <jlep@gmail.com> wrote:

1)      Do NOT use floating point numbers for currency; stick to
DECIMAL(s,p).  Lot less work AND no rounding errors.
2)      Instead of thinking in codes, think in data.  I changed your names
a bit to align them with ISO-11179
3)      While I applaud DEFAULTs, they are NOT a good idea in your CASE.
4)      You might consider using an abbreviation code for the delivery
methods so they would be easier for a human being to use.
5)      Minor points; you are using needlessly proprietary T-SQL; it is
INSERT INTO, you use ORDER BY in subqueries AND put your DEFAULTs in
the wrong order.

CREATE TABLE DeliveryMethods
(delivery_code INTEGER NOT NULL PRIMARY KEY,
 delivery_description VARCHAR(50) NOT NULL);

INSERT INTO DeliveryMethods VALUES (1, 'Royal Mail 2nd Class
Recorded');
INSERT INTO DeliveryMethods VALUES (2, 'Royal Mail Standard Parcels');

The real trick is to put in a weight range in your rates table:

CREATE TABLE DeliveryRates
(delivery_code INTEGER NOT NULL
 REFERENCES DeliveryMethods(delivery_code),
 low_parcel_wgt INTEGER NOT NULL,
 high_parcel_wgt INTEGER NOT NULL,
  CHECK (low_parcel_wgt < high_parcel_wgt),
delivery_rate DECIMAL(6,2) NOT NULL,
 PRIMARY KEY (delivery_code, low_parcel_wgt));
INSERT INTO DeliveryRates VALUES (1, 0, 100, 1.62);
INSERT INTO DeliveryRates VALUES (1, 101, 250, 1.90);
INSERT INTO DeliveryRates VALUES (1, 251, 500, 2.22);
INSERT INTO DeliveryRates VALUES (1, 501, 750, 2.62);
INSERT INTO DeliveryRates VALUES (2, 0, 1000, 3.85);
INSERT INTO DeliveryRates VALUES (2, 1001, 1500, 4.95);
INSERT INTO DeliveryRates VALUES (2, 1501, 2000, 5.68);
INSERT INTO DeliveryRates VALUES (2, 2001, 4000, 8.24);

Now the first problem is easy:

SELECT DM.delivery_code, DR.delivery_rate
  FROM Delivery_Methods AS DM,
       Delivery_Rates AS DR
 WHERE DR.delivery_code = DM.delivery_code
   AND @my_parcel_weight
       BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt;

-----------------------------------------------Reply-----------------------------------------------

Opps!  Second part, with Standard SQL:

SELECT delivery_code, delivery_rate AS best_delivery_rate
  FROM (SELECT DM.delivery_code, DR.delivery_rate,
               ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn
          FROM Delivery_Methods AS DM,
               Delivery_Rates AS DR
         WHERE DR.delivery_code = DM.delivery_code
           AND @my_parcel_weight
               BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt)
        AS X (delivery_code, delivery_rate, rn)
WHERE X.rn = 1;

-----------------------------------------------Reply-----------------------------------------------

In article <1180369929.281620.273@m36g2000hse.googlegroups.com>,
Jason Lepack <jlep@gmail.com> writes
>Solution to 1:

<snip>

Thanks, that's much better ;-)

--
Alan Silver
(anything added below this line is nothing to do with me)

-----------------------------------------------Reply-----------------------------------------------

In article <1180370270.054347.4@p77g2000hsh.googlegroups.com>, Jason
Lepack <jlep@gmail.com> writes
>Answer to #2:

<snip>

Duh! Obvious when you point it out!

Thanks again

--
Alan Silver
(anything added below this line is nothing to do with me)

-----------------------------------------------Reply-----------------------------------------------

In article <1180371281.769676.196@g4g2000hsf.googlegroups.com>,
--CELKO-- <jcelko@earthlink.net> writes

>1)     Do NOT use floating point numbers for currency; stick to
>DECIMAL(s,p).  Lot less work AND no rounding errors.

OK, didn't know that, thanks

>2)     Instead of thinking in codes, think in data.  I changed your names
>a bit to align them with ISO-11179

Don't understand what you mean here. Were you referring to my use of
"ID" in field names? If so, I don't see that "code" is so much better.
Please explain what you were getting at.

>3)     While I applaud DEFAULTs, they are NOT a good idea in your CASE.

They are there purely to allow me to use "not null" on the column. If
you look at the INSERT statements, you will find that I don't actually
use the default values.

I always set default values for columns, but I don't think I ever use
them. I just have a dislike of allowing nulls in a column unless it's
really necessary, so I specify a default value to get around that.

>4)     You might consider using an abbreviation code for the delivery
>methods so they would be easier for a human being to use.

The method name is for the human, the ID/code is for the computer. How
would using an abbreviation make it easier?

>5)     Minor points; you are using needlessly proprietary T-SQL; it is
>INSERT INTO,

Didn't know that, thanks.

> you use ORDER BY in subqueries

OK, my lack of expertise in SQL (which is why I came here!). Is this
invalid, or just not a good idea?

> AND put your DEFAULTs in
>the wrong order.

Please explain further as I'm not sure what you meant.

<snip>

>The real trick is to put in a weight range in your rates table:

Well, it's a trick, but is it the *real* one?

I'm not being sarcastic, I'm just wondering why it is so much better
than just having the maximum weight. The other solution suggested was
slightly more complex SQL, but not very much. Given the extra work of
maintaining a database with a range of data rather than a single value
(for example, requiring more validation between separate records for a
specific delivery method), it might not be as superior as you imply.
Then again, it might. Please explain further.

<snip>
Thanks very much for the reply. I'm genuinely interested in more
explanation here, I wasn't being ungrateful or sarcastic. I don't claim
to be an SQL expert (you'll be relieved to know) and am keenly
interested in understanding more.

Ta ra

--
Alan Silver
(anything added below this line is nothing to do with me)

-----------------------------------------------Reply-----------------------------------------------

But don't forget to add in your check constraints to ensure that there
is no overlapping of weights, or else this whole solution falls to
bits.

ie:
CREATE TABLE DeliveryMethods
(delivery_code INTEGER NOT NULL PRIMARY KEY,
 delivery_description VARCHAR(50) NOT NULL);

INSERT INTO DeliveryMethods VALUES (1, 'Royal Mail 2nd Class
Recorded');
INSERT INTO DeliveryMethods VALUES (2, 'Royal Mail Standard
Parcels');

CREATE TABLE DeliveryRates
(delivery_code INTEGER NOT NULL
 REFERENCES DeliveryMethods(delivery_code),
 low_parcel_wgt INTEGER NOT NULL,
 high_parcel_wgt INTEGER NOT NULL,
  CHECK (low_parcel_wgt < high_parcel_wgt),
delivery_rate DECIMAL(6,2) NOT NULL,
 PRIMARY KEY (delivery_code, low_parcel_wgt));
INSERT INTO DeliveryRates VALUES (1, 251, 500, 2.22);
INSERT INTO DeliveryRates VALUES (1, 252, 499, 3.00);
INSERT INTO DeliveryRates VALUES (2, 0, 1000, 3.85);

declare @my_parcel_weight int
set @my_parcel_weight = 300

SELECT DM.delivery_code, DR.delivery_rate
  FROM DeliveryMethods AS DM,
       DeliveryRates AS DR
 WHERE DR.delivery_code = DM.delivery_code
   AND @my_parcel_weight
       BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt;

Cheers,
Jason Lepack

On May 28, 12:54 pm, --CELKO-- <jcelko@earthlink.net> wrote:

Your coding style is all wrong, nobody uses the old cross join with
restriction in the where clause, rather - the old way we used to write
before ANSI 92.

>          FROM Delivery_Methods AS DM,
>               Delivery_Rates AS DR
>         WHERE DR.delivery_code = DM.delivery_code
>           AND @my_parcel_weight
>               BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt)

NO !

FROM <table> AS <alias>
    INNER JOIN <table> AS <alias> ON <join condition>

WHERE <filter condition>

Take a leaf out of your own book celko - use a style that is familiar to all
and not to just yourself.

Catch up with the rest of us - you are 7 years behind.

If you check books online and any books on SQL Server you will not find your
style of coding joins anywhere.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcelko@earthlink.net> wrote in message

news:1180371544.787886.92210@o5g2000hsb.googlegroups.com...

Celko, this is a SQL Server-specific forum.  PLEASE STOP giving examples
with the join syntax used below!!  It has been explicitly stated by
Microsoft that such syntax WILL NOT BE SUPPORTED in the future.

--
TheSQLGuru
President
Indicium Resources, Inc.

"--CELKO--" <jcelko@earthlink.net> wrote in message

news:1180371544.787886.92210@o5g2000hsb.googlegroups.com...

>> you use ORDER BY in subqueries

> OK, my lack of expertise in SQL (which is why I came here!). Is this
> invalid, or just not a good idea?

This is a bad idea from a performance standpoint.  The order by does nothing
for the actual query but carries server overhead.

--
TheSQLGuru
President
Indicium Resources, Inc.

-----------------------------------------------Reply-----------------------------------------------

I fully agree that old join syntax for inner joins is awkward, and we do have a nicer way to express
such.

I *do not* agree that these won't be supported (again, for inner joins). If you have some MS
documentation that states this, please share so we can communicate with MS to clear up that
documentation.

(ANSI SQL supports old style inner joins in all standard revisions, and last time I talked to MS
dev's there were no plans to deprecate this in SQL Server. Things might have changed since that, but
I'd be surprised...)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Hi Tibor,

Just to jump in here because this is an important topic that also shows
direct weakness, inconsistency and ambiquity in arguments celko puts out on
other techniques/methods of coding.

From join fundementals in 2005 latest bol, here is where it states that the
ANSI 92 INNER JOIN is the "preferred" method...

 Copy Code
SELECT pv.ProductID, v.VendorID, v.Name
FROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v
    ON (pv.VendorID = v.VendorID)
WHERE StandardPrice > $10
    AND Name LIKE N'F%'

The previous examples specified the join conditions in the FROM clause,
which is the preferred method. The following query contains the same join
condition specified in the WHERE clause:

 Copy Code
SELECT pv.ProductID, v.VendorID, v.Name
FROM Purchasing.ProductVendor pv, Purchasing.Vendor v
WHERE pv.VendorID = v.VendorID
    AND StandardPrice > $10
    AND Name LIKE N'F%'

---

From 'Using Joins'....

Join conditions can be specified in either the FROM or WHERE clauses;
specifying them in the FROM clause is recommended.

---

These are just two quick to find references in the product manual (bol), it
clearly states the INNER JOIN is the preferred and recommended method of
syntax rather than the old FROM x, y.

It doesn't state it's being deprecated though, but read into it what you
like - when the product vendor a) recommends a specific syntax and b) writes
all its examples in it then you'd be dam right daft and irresponsible to use
the old syntax impo.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in
message news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl...

In article <undYTzYoHHA.4@TK2MSFTNGP05.phx.gbl>, TheSQLGuru
<kgbo@earthlink.net> writes

>>> you use ORDER BY in subqueries

>> OK, my lack of expertise in SQL (which is why I came here!). Is this
>> invalid, or just not a good idea?

>This is a bad idea from a performance standpoint.  The order by does nothing
>for the actual query but carries server overhead.

Got it now, thanks.

I included the order by as I was using "top 1" to get the lowest rate.
Surely if I left it out, I might not get the lowest rate, which was the
point of the code.

Did I miss something? Thanks again

--
Alan Silver
(anything added below this line is nothing to do with me)

-----------------------------------------------Reply-----------------------------------------------

What about MIN?

ML

---
http://milambda.blogspot.com/

-----------------------------------------------Reply-----------------------------------------------

>> I *do not* agree that these won't be supported (again, for inner joins). If you have some MS documentation that states this, please share so we can communicate with MS to clear up that documentation.<<

Why would any vendor needlessly destroy existing code by violating
ANSI/ISO conformance?  They got rid of the old *=, (+) and OUTER
syntaxes for LEFT OUTER JOIN to get to ANSI/ISO conformance.

-----------------------------------------------Reply-----------------------------------------------
More to the point, why aren't you following vendor definied recommendations
and best practice?

It clearly states in books online the preferred method is FROM <table> INNER
JOIN <table> ON <join clause> and not FROM <table>, <table> WHERE <join
clause>.

Do you not see how irresponsible you are being in continuing your style
recommendation against the vendor docs?

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcelko@earthlink.net> wrote in message

news:1180445114.981804.63160@q69g2000hsb.googlegroups.com...

Hey Tony!

I'm not recommending the old join syntax, I hope my post suggested that. All I was saying was that
old join syntax isn't *deprecated* for inner joins. Personally, I haven't written an old style inner
join for ages.
:-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Tony Rogerson" <tonyroger@torver.net> wrote in message

news:egZ2cheoHHA.5052@TK2MSFTNGP04.phx.gbl...

>> Do you not see how irresponsible you are being in continuing your style recommendation against the vendor docs? <<

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

This vendor finally dropped the use of camelCase after realizing it is
a bitch to read (the eye moves to the uppercase letter, then jerks
back to the front of the word).

I think I have stated clearly why I prefer the WHERE clause syntax
over the infixed notation.  I have given the history of the actions
inside ANSI X3H2 and why the various infixed notations exist (even the
ones that nobody should use like CORRESPONDING and NATURAL clauses).
It is there for products without an optimizer, so that the order of
execution can be explicitly controlled.

I also explained the sequential mindset that has to have a left-to-
right, fully controlled ordering using only binary operators versus a
declarative, n-ary function mindset.  The first mindset lets old VB or
C# programmers who want to fake a file system in CLR feel good about
his code without having to learn a new way of thinking.

-----------------------------------------------Reply-----------------------------------------------

Hi Tibor,

Sorry, for some reason I read your post as if you where.

Sounds like another beer I owe you - are you going to IT Forum / Teched in
Nov aka Barcelona??

Tony

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in
message news:OOquNCgoHHA.4132@TK2MSFTNGP02.phx.gbl...

Who gives a hoot WHY!?!?  This is a MICROSOFT SQL SERVER FORUM, and
Microsoft has explicitly stated DO NOT DO THIS.  Feel free to tell OTHER
forums you post on to use whatever you like for join syntax.  But on THIS
forum, it is quite bad practice to use the *=, = and =* syntax, IMNSHO.

--
TheSQLGuru
President
Indicium Resources, Inc.

"--CELKO--" <jcelko@earthlink.net> wrote in message

news:1180445114.981804.63160@q69g2000hsb.googlegroups.com...

select *
from sys.objects o, sys.columns c
where o.objectid *= c.objectid

RESULT:

Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*").
To run this query without modification, please set the compatibility
level for current database to 80 or lower, using stored procedure
sp_dbcmptlevel. It is strongly recommended to rewrite the query using
ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN).
In the future versions of SQL Server, non-ANSI join operators will
not be supported even in backward-compatibility modes.

I took the last sentence of that to mean ALL "non-ANSI join operators", not
just LEFT (*=) and RIGHT (=*) ones since it is a new, full sentence.  Of
course I could be wrong in that interpretation, but a proper wording if it
means just outer would be to state "...non-ANSI outer join operators...".

--
TheSQLGuru
President
Indicium Resources, Inc.

"Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in
message news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl...

My bad!  I missed the top1/orderby!  Sorry.

As ML suggested, MIN operator is more appropriate since that is what you
truly seek here.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Alan Silver" <alan-sil@nospam.thanx.invalid> wrote in message

news:htJRkb6MDCXGFwcH@nospamthankyou.spam...

In article <#19d17ioHHA.3@TK2MSFTNGP04.phx.gbl>, TheSQLGuru
<kgbo@earthlink.net> writes

>My bad!  I missed the top1/orderby!  Sorry.

>As ML suggested, MIN operator is more appropriate since that is what
>you truly seek here.

Thanks for the clarification

--
Alan Silver
(anything added below this line is nothing to do with me)

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc