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 Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go

create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go

create index testDate_ndx
on dbo.crossoverTest (testDate)
go

insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator

go

update statistics dbo.crossoverTest
go

set showplan_text on
go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'

go

-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)

-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.

go

select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'

go

-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))

-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go

set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

QUERY Exceeding variable max length

Hi,

A query is exceeding the length of varchar and nvarchar variable.
Because I'm picking the data from each record from table and giving it
to the query.

suggest me some way to do it.

sample query:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS
Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS
Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS
Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot1 AS P
GROUP BY P.Year) AS P1
GO

--> even the P.QUARTER ... FIELD NAME IS BEING GENERATED
DYNAMICALLY.

MY QUERY IS EXCEEDING VARCHAR AND NVARCHAR LIMIT.

THANX IN ADV.One option is to split up the VARCHAR variable into multiple variables &
concatenate them in your EXEC like:
EXEC( @.v1 + @.v2 + ...)

Also, you can avoid all these hazzles, if you bring back the resultset to
the client application & do the pivoting on the front end.

--
- Anith
( Please reply to newsgroups only )

query error: invalid object name

I'm getting the following error when I try to add a new record to the db:
System.Data.SqlClient.SqlException: Invalid object name 'Pub_Points'.
My table is called Pub_Points. I've had an insert working before.
This is the SQL string that I'm trying to send:
INSERT INTO Pub_Points ('PPName','Encoder_URL','Connect_Type','Archive','Creation_Date') VALUES ('fu','barr','local ','19/26/2005 13:35:27')

Yet updating an existing record during the same run works fine:
UPDATE Pub_Points SET PPName='foo ', Encoder_URL='bar', Connect_Type='remote ', Archive='0' Where ID='114'
So it can't be a problem finding the table itself.
Only two things I can see that are different:
- the Insert procedure first creates and attaches the formatted date string (which you see already inserted in the Insert query)
- the database has an auto-generating key field 'ID' (which you see being referenced in the UPDATE query)
but I don't see how either of those things would give me this error.
Ideas?
Get rid of the quotes around your column names:
INSERT INTO Pub_Points (PPName,Encoder_URL,Connect_Type,Archive,Creation_Date) VALUES ('fu','barr','local ','19/26/2005 13:35:27')

Marcie

Query Concept

Hi,
I would like to use Northwind as an example.
I am trying to use the Orders table and why the following query only returns
one record for each customer if the outer query pass info into the inner
query since each customer has more than one records in the Orders table.
e.g. customerid "ALFKI" has six records in Orders table, but after the query
run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
into the inner query six times... it is supposed to show six identitcal
reocrds for "ALFKI", but not, why?
select a.* from orders a where a.orderdate = (select max(orderdate) from
orders b where a.customerid = b.customerid)
order by a.customerid
Thanks
EdmundIn principle the subquery is evaluated for each row but it will always
return the same value for any given customerid. If there is only one
value that corresponds to the maximum date for "ALKFI" then you will
only get one row returned for that customer.
Please show us exactly what result you want then maybe someone can help
with a solution.
David Portas
SQL Server MVP
--|||Actually, i am just wondering why I don't get six records back for customer
"ALFKI" but just one since I guess the outer query is passing into the inner
query for six times even I may get six identical records...
"David Portas" wrote:

> In principle the subquery is evaluated for each row but it will always
> return the same value for any given customerid. If there is only one
> value that corresponds to the maximum date for "ALKFI" then you will
> only get one row returned for that customer.
> Please show us exactly what result you want then maybe someone can help
> with a solution.
> --
> David Portas
> SQL Server MVP
> --
>|||Because the subquery will return only one record, which has the last
orderdate, for each CustomerID.
Perayu
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:D85ECABE-E0C5-431F-9948-B329086B1A31@.microsoft.com...
> Hi,
> I would like to use Northwind as an example.
> I am trying to use the Orders table and why the following query only
> returns
> one record for each customer if the outer query pass info into the inner
> query since each customer has more than one records in the Orders table.
> e.g. customerid "ALFKI" has six records in Orders table, but after the
> query
> run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
> into the inner query six times... it is supposed to show six identitcal
> reocrds for "ALFKI", but not, why?
> select a.* from orders a where a.orderdate = (select max(orderdate) from
> orders b where a.customerid = b.customerid)
> order by a.customerid
> Thanks
> Edmund
>
>
>
>|||Maybe the following example will make it clearer:
SELECT a.customerid, a.orderdate,
(SELECT MAX(orderdate)
FROM orders b
WHERE a.customerid = b.customerid) AS subquery_result
FROM orders a
WHERE a.customerid = 'ALFKI' ;
Here I've just moved the subquery into the select list so that you can
see the result it returns for each row - the same value each time. You
can also see that there is only ONE row that matches the value returned
by the subquery, therefore that's the only row that satisfies the
condition in your WHERE clause.
David Portas
SQL Server MVP
--|||Ed,

> Actually, i am just wondering why I don't get six records back for custome
r
> "ALFKI" but just one since I guess the outer query is passing into the inn
er
> query for six times even I may get six identical records...
From all alfki's orders, just one is the latest. Eventhough the outer query
is passing value six times, the value is the same (customerid) and the value
returned is always the same (max(orderdate)).
declare @.d datetime
select @.d = max(orderdate)
from orders
where customerid = 'alfki'
print @.d
select *
from orders
where customerid = 'alfki' and orderdate = @.d
go
AMB
"Ed" wrote:
> Actually, i am just wondering why I don't get six records back for custome
r
> "ALFKI" but just one since I guess the outer query is passing into the inn
er
> query for six times even I may get six identical records...
> "David Portas" wrote:
>