query help

Here is a simplified example of what I need to accomplish.

I have a table that keeps track of plastic balls in tubs. Based on a bit column the record is either defining balls added to a tub or taken away. When the record is defined as adding balls to a tub it will say how many and what color, but when the flag says they have been taken away from a tub I only know the number removed and not the color.

There are multiple tubs and multiple colors. So say tub1 has 20 balls in it (50% red, %25 green, and %25 blue). Also say tub2 has 10 balls in it (100% red). This is our starting point.

Now on day one, 5 balls from tub two are put into tub one. So we know that 5 balls of 100% red are put into tub one. This means that tub one now has 25 balls in it. By doing some weighted percentages, tub one now has these percentages: red = 60%, green = 20%, and blue = 20%.

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

The math for the new percentage is I believe = ((originalPercent * originalCount) + (addedPercent * addedAmount)) / newTotalBallCount

I need a query that will give me the percentages of the different colors in the tub for any given day. This is really a running percentage that has to take every transaction into account.

This is a complicated query fro me to figure out, but can someone point me in the right direction?

LLeuthard wrote:

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

too superfluous.

is this thing about permutation and combination/? how did u say taht 6r,2g,2b?

|||Are you saying it is not worth my time or that it is impossible? Is there a stored proc I could write that would do this easily?|||

I got .6Red .2Blue and .2Green by saying that:

5 balls of 100% red were moved to tub1. so tub1 originally has a estimate of .5*20 = 10Red balls.

Take the estimate of 10Red and add the estimate of 5Red and we get an estimate of 15Red out of 25 in tub1. This makes for .6Red at the end of day 1.

Do the same with the others.

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query example

Hi,

I have to select two different column values from two different tables based on one condition.

Like, I have two tables - orders and fulfillment. I have to select orders.order_id and fulfillment.fulfillment_id for product_id = 2 and date_added = '08/18/06'. The only common fields between the two tables are customer_id,order_id,product_id,date_added.

I tried doing this but I know that won't work because it is returning all the rows from orders and fulfillment.

select o.order_id ,f.fulfillment_id

from orders o,fulfillment f

where o.product_id = 2

and o.date_added = '08/18/06'

and f.product_id = 2

and f.date_added = '08/18/2006'

Can somebody please suggest me how to select records from these two tables based on thte above condition?

Thanks,

Reva,

The details about the relationships of the data between the two tables are a little vague, but let me give it a try:

SELECT
o.order_id,
f.fulfillment_ID
FROM orders AS o
JOIN fulfillment AS f
ON o.order_id = f.order_id
WHERE o.product_id = 2
AND o.date_added = '08/18/06'

You will note that I don't have criteria in the WHERE clause of the f.date_added and f.product_id fields. I am assuming that the date_added and product_id values for a fulfillment are the same as the date_added and product_id values for the related order. If that is the case, you only need to filter the values in one of the tables.

The previous query uses the JOIN and ON clauses to state the join criteria. This is the standars compliant syntax that makes JOINS more accurate and less likely to cause unexpected results.

The problem with your query is that you used the older "legacy" style join syntax of FROM orders,fulfillment. When you do that you MUST state the join criteria (o.order_id = f.order_id) for the rows to match. Otherwise you get what is called a "CROSS JOIN" where every row of the first table is joined to every row of the second table. That is why you are seeing all the extra rows you didn't want. The syntax I used above prevents that from happening by accident because the ON clause is REQUIRED unless CROSS JOIN is explicitly stated.

I hope that helps.

Query building based on user selections

I currently have a form that has different options in a list box such as:

Status Codes

Division Codes

Project Managers

Report Types

The user can select a value from any one of these and hit a search button to run a query. Currently I am running through some IF statements to evaluate which combination of values were selected to run the right SQL code. Example

A user wants to view reports that have a status code of "Active" and a division code of "001". So my if statement would be something like (not in perfect syntax, just trying to get the point across):

if strStats <>"" and strDivision <>"" and strProjMgr ="" and strRptType ="" thenselect ...from ...where status_cd = strStats and division_cd = strDivision

end if

So my question is this: Is there a way to build the query dynamically (1 time) without having to run through all the senarios? 

Yes. you could write a query as:

select ...
from ...
where status_cd = ISNULL(@.strStats,status_cd)

and division_cd = ISNULL(@.strDivision,division_cd )

and ...

pass the values for @.strStats and @.strDivision. You can pass the parameters in any combination.

|||So this basically allows a condition to be null or a value can be passed?|||Yes.

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!

Query based parameter value changes back to default value

I have several parameters on my report, some have non-queried lists, and
some have queried lists. All of them have default values. When I ran
reports on the Report Manager, I changed the value of the parameters from
the drop-down list to produce different reports. My problem is whenever I
changed the value of a parameter(pA), the value of those queried-based
parameters that were listed after pA would switch back to their respective
default values. This didn't happen when I previewed reports on Visual
Studio .Net Development Environment. Is this a bug, limitation, or am I
missing something?
Any help would be appreciated.
Paul
--
Message posted via http://www.sqlmonster.comPaul,
It sounds like your default values are calculated values (begin with a '=').
The way that RS computes these is that whenever you change a parameter, it
recalculates all the calculated parameters after it. This behaviour is to
allow cascading parameters. As far as I know, there is no way to turn this
calculation off. However, I worked around it by creating another dataset
which had a column for each default value (I do a lot of default dates). You
can then set the default values for the parameters to the dataset value in
the parameters window. I have to use SQL to do the calculations, but for
most things, this should be an easy workaround. It gets rid of the changing
values and also the refresh that takes place when you change calculated
parameters
"Paul via SQLMonster.com" wrote:
> I have several parameters on my report, some have non-queried lists, and
> some have queried lists. All of them have default values. When I ran
> reports on the Report Manager, I changed the value of the parameters from
> the drop-down list to produce different reports. My problem is whenever I
> changed the value of a parameter(pA), the value of those queried-based
> parameters that were listed after pA would switch back to their respective
> default values. This didn't happen when I previewed reports on Visual
> Studio .Net Development Environment. Is this a bug, limitation, or am I
> missing something?
> Any help would be appreciated.
> Paul
> --
> Message posted via http://www.sqlmonster.com
>|||Hi David, that works great. Thank you very much!!
Paul
--
Message posted via http://www.sqlmonster.com

Query based parameter and web service call

has anyone seen this before?
I have a developer that has built a report that has a query based parameter.
Since they want to be able to run the report both interactively and through
a .net batch job. They are attempting to do a soap call to the report
passing a string value in as the query based parameters value. They are
getting a soap exception error .SoapException: Default value or value
provided for the report parameter 'PROD_DESC' is not a valid value. -->
Has anyone seen this before or can verify that you can pass string values
into query based parameters?
Thank YOu!!!We figured out the issue. The developer had a trailing space in his code so
the values in the string were not matching the values returned by the query.
"Mark" wrote:
> has anyone seen this before?
> I have a developer that has built a report that has a query based parameter.
> Since they want to be able to run the report both interactively and through
> a .net batch job. They are attempting to do a soap call to the report
> passing a string value in as the query based parameters value. They are
> getting a soap exception error .SoapException: Default value or value
> provided for the report parameter 'PROD_DESC' is not a valid value. -->
> Has anyone seen this before or can verify that you can pass string values
> into query based parameters?
> Thank YOu!!!

query based on the top MTD sales

hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...

Thank youhello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Each Fabric group has to be orderd by the Top MTD.

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...|||you need to post some transaction data and the expected output of the query for us to understand where exactly the problem is.|||Hi

I'm afraid your description is a little breathless and difficult to follow. I suspect this is why you have not yet had a response. Please could you write in natural English what purpose the query is to serve?

Ta|||Hello,
Attached is a data file in which I need a sort as I said below.

Thank you|||Query has to show data in alphabetical order for each group (Sales Group, Product Group). Then the Fabric Group has to be at the highest order first based on the MDT Sales and then the Fabric Group has to be grouped by description itself.
Then style number and a color code.

Thank you

Sales Group Product Group FabricGroup STYLE NUM Retail Price WTD_Unit WTD_Sale WTD Avg Sales MTD_Unit MTD_Sale MTD_Avg_Sales YTD_Units YTD_Sales YTD_Avg_Sales
ADULT ACTIVE SUPPLEX 1561 $30.00 25 $716.40 $28.66 101 $2,975.40 $29.46 626 $17,896.50 $28.59
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 24 $1,287.60 $53.65
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 100 $5,755.44 $57.55
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 38 $2,125.20 $55.93
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 126 $7,229.39 $57.38
ADULT ACTIVE SUPPLEX 1562 $31.50 11 $343.35 $31.21 65 $1,997.73 $30.73 585 $17,091.59 $29.22
ADULT ACTIVE SUPPLEX 1561 $30.00 8 $223.20 $27.90 35 $1,024.80 $29.28 154 $4,335.60 $28.15
ADULT ACTIVE COTN LYCRA 8150 $21.00 8 $168.00 $21.00 49 $1,019.34 $20.80 404 $8,098.44 $20.05
ADULT ACTIVE YOGA CVC 5720 $33.00 11 $327.69 $29.79 31 $935.55 $30.18 220 $6,721.77 $30.55
ADULT ACTIVE SUPPLEX 5145 $39.00 6 $221.91 $36.99 23 $873.99 $38.00 212 $7,781.67 $36.71
ADULT ACTIVE SUPPLEX 7360 $23.00 14 $318.78 $22.77 39 $868.48 $22.27 178 $3,822.37 $21.47
ADULT ACTIVE SUPPLEX 1561 $30.00 6 $179.40 $29.90 29 $859.20 $29.63 35 $1,037.40 $29.64
ADULT ACTIVE TRIGROUP 1847 $48.00 2 $95.04 $47.52 17 $797.76 $46.93 25 $1,168.32 $46.73
ADULT ACTIVE YOGA CVC 8298 $42.00 3 $115.50 $38.50 19 $761.46 $40.08 177 $6,994.68 $39.52
ADULT ACTIVE SUPPLEX 7111 $36.00 7 $232.92 $33.27 22 $752.76 $34.22 98 $3,303.36 $33.71
ADULT ACTIVE TRIGROUP 1672 $48.00 0 $0.00 $0.00 18 $715.82 $39.77 74 $2,806.50 $37.93
ADULT ACTIVE TRIGROUP 1671 $44.00 2 $69.98 $34.99 19 $660.61 $34.77 82 $2,680.23 $32.69
ADULT ACTIVE STRETCH COTN 8202 $17.00 13 $209.95 $16.15 39 $635.97 $16.31 103 $1,633.70 $15.86
ADULT ACTIVE COTN LYCRA 8379 $36.00 2 $71.28 $35.64 19 $635.76 $33.46 175 $5,721.48 $32.69
ADULT ACTIVE YOGA CVC 5369 $36.00 5 $177.12 $35.42 18 $626.40 $34.80 310 $10,296.72 $33.22
ADULT ACTIVE COTN LYCRA 8166 $30.00 0 $0.00 $0.00 22 $625.20 $28.42 99 $2,781.60 $28.10
ADULT ACTIVE TRIGROUP 1845 $75.00 0 $0.00 $0.00 8 $597.00 $74.63 19 $1,401.00 $73.74
ADULT ACTIVE SUPPLEX 1561 $30.00 3 $90.00 $30.00 20 $596.40 $29.82 28 $817.20 $29.19
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 3 $35.49 $11.83
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 10 $193.60 $19.36
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 21 $293.65 $13.98
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 4 $35.42 $8.86
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 13 $188.79 $14.52
ADULT DANCE SATIN TOUCH 2809 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 6 $207.36 $34.56
ADULT DANCE SATIN TOUCH 2812 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 14 $483.48 $34.53
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 17 $244.90 $14.41
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 18 $152.02 $8.45
ADULT DANCE BODY SCULPT 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 9 $300.30 $33.37
ADULT DANCE BODY SCULPT COLLECTION (2006) 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 11 $359.45 $32.68|||Dupe post
http://www.dbforums.com/showthread.php?t=1217893

I still don't get it. There is a sticky at the top of this forum. It asks you to:
Post table DDL
Sample data in DML format
Expected results

It also explains how to do any of these if you don't know how. Please could you do this?|||FYI (not the OP) - this is also live in SQL Server:
http://www.dbforums.com/showthread.php?t=1217908

:D|||SalesGroup Text 255
ProductGroup Text 255
FabricGroup Text 255
TYPEYR Text 5
STYLE_NUM Text 50
STYLE_DESC Text 255
COLORCODE Text 3
COLORD Text 255
Retail Price Currency 8
WTD_Units_05_25_2006 Double 8
WTD_Sales_05_25_2006 Currency 8
WTD_Avg_Sales_05_25_2006 Currency 8
MTD_Units_May 2006 Double 8
MTD_Sales_May 2006 Currency 8
MTD_Avg_Sales_May 2006 Currency 8
YTD_Units_2006 Double 8
YTD_Sales_2006 Currency 8
YTD_Avg_Sales_2006 Currency 8

SalesGroup (In Alphabetical Order - Adult, Girls, Plus, Mens, Irrs),
for each Sales gr ther are the
ProductGroup (Alphabetical Order - Active, Dace, Legwr, Prvt),
For each Sales Gr and a Prod Group there are the
FabricGroups (has to apear as the highest total first for each FabricGroup based on the MTD Sales (Suplex, Cotton, Cotton LCRY)
and then it has to group the FabricGroup together. (If I have 10 entries for the Supplex and it is at the highest total, then the highest total has to be first and the rest apper after)
StyleID (1,2,3,4,5,6,7..),
Color (01,02,03,04,05),
RetailAmount,
WeeklySales,
MonthlySales
YTDSales

Thank you so much.|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done here

that the merged threads may be more confusing than separate threads is not our problem but the original poster's|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done hereLol - ya :D The pointers were more to flag up the posts to the Orange users than to create some complex web of a thread.

Although I do like to alternate my responses from one thread to another when I do get involved. That's just mischief though.

query based on a field in the same row correction

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
A0_01 1,37
(18-ItemPersonPersonal)+ItemPinax_AMM
A0_02 1,4
(18-ItemPersonPersonal)+ItemPinax_AMM
I need to calculate the value of ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
ItemPersonAFM ItemPersonCode ItemPersonPersonal
041 A0_01 10
041 A0_02 12
the answer must be for the first row (18-10)+1,37
for the second row (18-12)+1,4
I use this code
DECLARE @.sql VARCHAR(655)
SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
where ItemPersonAFM='041'
EXEC(@.sql)
and I get 1,37 and 1,4. How can I have the right answers?Helen
DDL Means table definition and sample data means insert commadns that you
use. so that we can simulate here
Regards
R.D
"Helen" wrote:

>
>
> I have two tables ItemsPinax , ItemPerson. The first table has these fie
lds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
> A0_01 1,37
> (18-ItemPersonPersonal)+ItemPinax_AMM
> A0_02 1,4
> (18-ItemPersonPersonal)+ItemPinax_AMM
> I need to calculate the value of ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
> ItemPersonAFM ItemPersonCode ItemPersonPersonal
> 041 A0_01 10
> 041 A0_02 12
> the answer must be for the first row (18-10)+1,37
> for the second row (18-12)+1,4
> I use this code
> DECLARE @.sql VARCHAR(655)
> SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> where ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||Helen
CREATE TABLE TAB1 (COL1 INT) IS DDL
INSERT INTO TAB1 VALUES(1) is sample data
Regards
R.D
"R.D" wrote:
> Helen
> DDL Means table definition and sample data means insert commadns that you
> use. so that we can simulate here
> Regards
> R.D
> "Helen" wrote:
>|||CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
decimal ,ItemPinax_FUNCTION varchar(100)
INSERT INTO ItemsPinax
VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
varchar(6), ItemPersonPersonal int)
INSERT INTO ItemPerson VALUES(041, A0_01, 10)|||> > the answer must be for the first row (18-10)+1,37
what is 1,37 and 1,4 ?
I dont really understand what exactly you want calculated feild or
substracted from function definition.
it is already 6 pm here, we will tomorrow
Regards
R.D
"Helen" wrote:
> CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
> decimal ,ItemPinax_FUNCTION varchar(100)
> INSERT INTO ItemsPinax
> VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
> CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
> varchar(6), ItemPersonPersonal int)
> INSERT INTO ItemPerson VALUES(041, A0_01, 10)
>
>|||the answer must be for the first row (18-10)+1.37
1.37 AND 1.4 IS THE VALUES FOR ItemPinax_AMM WHICH I USE FOR THE CALCULATION
"R.D" wrote:
> what is 1,37 and 1,4 ?
> I dont really understand what exactly you want calculated feild or
> substracted from function definition.
> it is already 6 pm here, we will tomorrow
> Regards
> R.D
> "Helen" wrote:
>

query based on a field in the same row

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
A0_01 1,37
(18-ItemPersonPersonal)*ItemPinax_AMM
A0_02 1,4
(18-ItemPersonPersonal)*ItemPinax_AMM
I need to calculate the value of ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
ItemPersonAFM ItemPersonCode ItemPersonPersonal
041 A0_01 10
041 A0_02 12
the answer must be for the first row (18-10)*1,37
for the second row (18-12)*1,4
I use this code
DECLARE @.sql VARCHAR(655)
SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode where
ItemPersonAFM='041'
EXEC(@.sql)
and I get 1,37 and 1,4. How can I have the right answers?pl.post ddl
"Helen" wrote:

> I have two tables ItemsPinax , ItemPerson. The first table has these fiel
ds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
> A0_01 1,37
> (18-ItemPersonPersonal)*ItemPinax_AMM
> A0_02 1,4
> (18-ItemPersonPersonal)*ItemPinax_AMM
> I need to calculate the value of ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
> ItemPersonAFM ItemPersonCode ItemPersonPersonal
> 041 A0_01 10
> 041 A0_02 12
> the answer must be for the first row (18-10)*1,37
> for the second row (18-12)*1,4
> I use this code
> DECLARE @.sql VARCHAR(655)
> SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode whe
re
> ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||sorry, I don't understand. What to do?
"R.D" wrote:
> pl.post ddl
> "Helen" wrote:
>

Query assistance or advice

Hi,
I have a table which I copy nightly via DTS. I would like to copy only the
data which was changed instead based on the MODIFIED date. It will have to
insert any new rows created or update any row
which already exists. This is a sample
TABLE1
ID PRODID NAME QTY MODIFIED
1 123 TEST 1 2006-02-09
2 235 TEST2 2 2006-02-09
3 234 TEST3 5 2006-02-09
TABLE2 (MIRROR)
ID PRODID NAME QTY MODIFIED
1 123 TEST 5 2006-02-07
In this case when I run the query it will update TABLE2 by
updating the qty for id 1 to 1
insert id 2 and 3
Any ideas?
ThanksWhy don't you use triggers? There are several good examples in Books Online.
ML
http://milambda.blogspot.com/|||Doesn't DTS have tools for this? How are you using DTS? I think it has
tools to let you do this directly into table2, checking to see if it needs
to be done.
If you pumping the data right into a temporary table and then running a
query? If so then just write a query like :
insert into table2 (columnList)
select (columnList)
from table1changes as table1
where not exists (select 1
from table2
where table1.id = table2.id)
update table2
set table2.(each column) = table1.(each column)
from table2
join table1changes as table1
on table2.id = table1.id --this must be unique or you
will get (predictably) wierd results
and table1.modified <> table2.modified
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||I am importing from a legacy database. I am currently transferring the entir
e
table nightly but it's hugh so we added a modifieddate column to the table s
o
now I want to check for any records added and updated for a specific date
then check my table in sql server, if records does not exists then insert
them if they do exists then update.
Thanks
"Chris" wrote:

> Hi,
> I have a table which I copy nightly via DTS. I would like to copy only the
> data which was changed instead based on the MODIFIED date. It will have to
> insert any new rows created or update any row
> which already exists. This is a sample
> TABLE1
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 1 2006-02-09
> 2 235 TEST2 2 2006-02-09
> 3 234 TEST3 5 2006-02-09
> TABLE2 (MIRROR)
> ID PRODID NAME QTY MODIFIED
> 1 123 TEST 5 2006-02-07
>
> In this case when I run the query it will update TABLE2 by
> updating the qty for id 1 to 1
> insert id 2 and 3
> Any ideas?
> Thanks|||For DTS I am using a transform data task form one data source to the other.
What tools are you talking about?
"Louis Davidson" wrote:

> Doesn't DTS have tools for this? How are you using DTS? I think it has
> tools to let you do this directly into table2, checking to see if it needs
> to be done.
> If you pumping the data right into a temporary table and then running a
> query? If so then just write a query like :
> insert into table2 (columnList)
> select (columnList)
> from table1changes as table1
> where not exists (select 1
> from table2
> where table1.id = table2.id)
> update table2
> set table2.(each column) = table1.(each column)
> from table2
> join table1changes as table1
> on table2.id = table1.id --this must be unique or you
> will get (predictably) wierd results
> and table1.modified <> table2.modified
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A8518925-0EB0-4D7D-8BD6-2C4FFBE686E4@.microsoft.com...
>
>|||I don't know. I am a query/design guy. DTS is a tool I have heard about
and read about but never put into practice. I would think that the
transform task might be able to do a query to check for row existance (maybe
someone else will know?)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F2580A5F-1EEB-4120-82C3-0CE0FF6FEA9C@.microsoft.com...
> For DTS I am using a transform data task form one data source to the
> other.
> What tools are you talking about?
> "Louis Davidson" wrote:
>