query question

I have a table where one of the fields is named SHIPPER
in the field, it will have the names of various shippers. ShipperA,
ShipperB ShipperC and ShipperD as an example
I need to make a query where it will return all the records where the
SHIPPER is ShipperA, ShipperB, ShipperD
All help would be great!
ThanksSELECT *
FROM YourTable
WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
?
If this doesn't work, please post DDL and sample data (
http://www.aspfaq.com/etiquette.asp?id=5006 )
"johnfli" <john@.here.com> wrote in message
news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
> I have a table where one of the fields is named SHIPPER
> in the field, it will have the names of various shippers. ShipperA,
> ShipperB ShipperC and ShipperD as an example
> I need to make a query where it will return all the records where the
> SHIPPER is ShipperA, ShipperB, ShipperD
> All help would be great!
> Thanks
>|||Thank you for the quick reply.
I wasn't able to get it to work, I am sure it has something to do with the
wildcards.
I modified your line to read:
WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
as for some reason, the names were inputted with partial address
thanks again if you're able to fix this.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23i6goi8rEHA.3848@.TK2MSFTNGP14.phx.gbl...
> SELECT *
> FROM YourTable
> WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
> ?
> If this doesn't work, please post DDL and sample data (
> http://www.aspfaq.com/etiquette.asp?id=5006 )
>
> "johnfli" <john@.here.com> wrote in message
> news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
>|||Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
WHERE Shipper LIKE '%ShipperA%'
OR Shipper LIKE '%ShipperB%'
OR Shipper LIKE '%ShipperD%'
"johnfli" <john@.here.com> wrote in message
news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
> Thank you for the quick reply.
> I wasn't able to get it to work, I am sure it has something to do with the
> wildcards.
> I modified your line to read:
> WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
> as for some reason, the names were inputted with partial address
> thanks again if you're able to fix this.
>|||perfect!
thank you
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OWbQUr8rEHA.556@.tk2msftngp13.phx.gbl...
> Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
> WHERE Shipper LIKE '%ShipperA%'
> OR Shipper LIKE '%ShipperB%'
> OR Shipper LIKE '%ShipperD%'
>
> "johnfli" <john@.here.com> wrote in message
> news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>

Query problem

Hi,

I've got the following problem:

In a table named "Commission" are all commissions of sales people per month listed.
Now I have to calculate and update the following:
I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

Has any brain a solution for me available ?

Thx a lot

dajmSummarizing Data Using COMPUTE and COMPUTE BY

In books online

Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.|||Originally posted by HanafiH
Summarizing Data Using COMPUTE and COMPUTE BY

In books online

Basically you can follow pretty much any select with a COMPUTE directive and pull up any aggregate you want.

Sorry, but COMPUTE is not helping me as I have to compare the result with `0`and to update the same fact table . Any ideas ?

Thx.|||use a cursor

Originally posted by dajm
Hi,

I've got the following problem:

In a table named "Commission" are all commissions of sales people per month listed.
Now I have to calculate and update the following:
I have to sum up the commissions (data type money) by month (smallint) and salesmanID(int). If the monthly sum is getting negative (yes, can happen!), I have to set it back to "0" for any sales record in the table "Commission".

I tried with views and subsets of select - statements, but I did not find a solution and I don't want to use MDX-statements instead in a cube later.

Has any brain a solution for me available ?

Thx a lot

dajm|||set based solutions are almost always better than cursor solutions

update the Commission table for each month/salesman where total commissions for the month are negative:update Commission
set commissions = 0
from Commission as table1
inner
join (
select themonth
, salesmanID
from Commission
group
by themonth
, salesmanID
having sum(commissions) < 0
) as table2
on table1.themonth = table2.themonth
and table1.salesmanID = table2.salesmanID(caution: untested, but it should work)

rudy
http://r937.com/|||try CASE along with COMPUTE statement.|||Super solution. Thx a lot...

dajm

Originally posted by r937
set based solutions are almost always better than cursor solutions

update the Commission table for each month/salesman where total commissions for the month are negative:update Commission
set commissions = 0
from Commission as table1
inner
join (
select themonth
, salesmanID
from Commission
group
by themonth
, salesmanID
having sum(commissions) < 0
) as table2
on table1.themonth = table2.themonth
and table1.salesmanID = table2.salesmanID(caution: untested, but it should work)

rudy
http://r937.com/

Query problem

Hello Friends

I want to convert my float field (named Qty) to a float value with precision 2.
e.g. my Qty field hold 15.10000000000000001 and I wish to convert it into 15.10
I'm using SQL server 2000.

How could I write the query for this conversion.

Plz help me

Thanks

Declare @.float float
SET @.float = 2515.11111000000001
-- the optional third parameter of convert if 1 or 0
-- when applied to smallmoney or money will have only 2 numbers on right side of decimal
-- you can also add commas i.e. 12000.393939
-- if 1 would be 12,000.39
-- if 0 would be 12000.39
SELECT CONVERT(varchar, CONVERT(smallmoney, @.float), 1)

|||you can also do it in the design itself and not worry abt converting everytime you use it...set the column type to decimal and set the precision to 2. that way you dont have to worry abt the conversion everytime and also not lose any values when you do a reverse conversion...

hth|||Acutally I want to store the original value in the table but when i need it somewhere else then it has to be in such required conversion.

I knew ur solution but due to my requirement I'm expecting the proper query for that.

If u have the solution then plz reply me soon.

Thanks|||you can say

SELECT CONVERT(varchar, CONVERT(smallmoney, <column name>), 1) as [<column name>] from the table.

this will display the data without effecting the data base.

Query multiple database tables

Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?

If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.

Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.