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 by year group by total students sort by total for each county

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

RobSomething like this, maybe:

SELECT county, YEAR(registration_date), SUM(num_students) AS tot_students
FROM Students
GROUP BY county, YEAR(registration_date)
ORDER BY county, tot_students

If you need more help please post DDL (CREATE TABLE statement) for the
table, including keys and constraints.

--
David Portas
----
Please reply only to the newsgroup
--|||the_ainbinders@.yahoo.com (Rob) wrote in message news:<14be79e4.0312010657.8573393@.posting.google.com>...
> I haven't a clue how to accomplish this.
> All the data is in one table. The data is stored by registration date
> and includes county and number of students brokne out by grade.
> Any help appreciated!
> Rob

Here's the CREATE TABLE

CREATE TABLE [dbo].[EdSales] (
[FName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOV] [datetime] NULL ,
[grade1] [numeric](10, 0) NULL ,
[grade2] [numeric](10, 0) NULL ,
[grade3] [numeric](10, 0) NULL ,
[grade4] [numeric](10, 0) NULL ,
[grade5] [numeric](10, 0) NULL ,
[grade6] [numeric](10, 0) NULL ,
[grade7] [numeric](10, 0) NULL ,
[grade8] [numeric](10, 0) NULL ,
[grade9] [numeric](10, 0) NULL ,
[grade10] [numeric](10, 0) NULL ,
[grade11] [numeric](10, 0) NULL ,
[grade12] [numeric](10, 0) NULL ,
[grade13] [numeric](10, 0) NULL ,
[grade14] [numeric](10, 0) NULL ,
[grade15] [numeric](10, 0) NULL ,
[grade16] [numeric](10, 0) NULL ,
[grade17] [numeric](10, 0) NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateEntered] [datetime] NULL ,
[result] [numeric](18, 0) NULL ,
[ConfirmNum] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[visible] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [PK_EdSales_1] PRIMARY KEY CLUSTERED
(
[ConfirmNum]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [DF_EdSales_visible] DEFAULT ('y') FOR [visible]
GO|||Your table design has some problems: Non-normalised repeating group of
Grades; No natural primary key; Too many NULLable columns (if every column
can be NULL then why would you have a row in the table anyway!). This query
would be a lot simpler if you fixed these things.

SELECT county, YEAR(dateentered) AS year_entered,
SUM(
COALESCE(grade1,0)+
COALESCE(grade2,0)+
COALESCE(grade3,0)+
COALESCE(grade4,0)+
COALESCE(grade5,0)+
COALESCE(grade6,0)+
COALESCE(grade7,0)+
COALESCE(grade8,0)+
COALESCE(grade9,0)+
COALESCE(grade10,0)+
COALESCE(grade11,0)+
COALESCE(grade12,0)+
COALESCE(grade13,0)+
COALESCE(grade14,0)+
COALESCE(grade15,0)+
COALESCE(grade16,0)+
COALESCE(grade17,0))
AS tot_students
FROM EdSales
GROUP BY county, YEAR(dateentered)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<_dGdnR8i6ZJR9VGiRVn-jg@.giganews.com>...
> Your table design has some problems: Non-normalised repeating group of
> Grades; No natural primary key; Too many NULLable columns (if every column
> can be NULL then why would you have a row in the table anyway!). This query
> would be a lot simpler if you fixed these things.
> SELECT county, YEAR(dateentered) AS year_entered,
> SUM(
> COALESCE(grade1,0)+
> COALESCE(grade2,0)+
> COALESCE(grade3,0)+
> COALESCE(grade4,0)+
> COALESCE(grade5,0)+
> COALESCE(grade6,0)+
> COALESCE(grade7,0)+
> COALESCE(grade8,0)+
> COALESCE(grade9,0)+
> COALESCE(grade10,0)+
> COALESCE(grade11,0)+
> COALESCE(grade12,0)+
> COALESCE(grade13,0)+
> COALESCE(grade14,0)+
> COALESCE(grade15,0)+
> COALESCE(grade16,0)+
> COALESCE(grade17,0))
> AS tot_students
> FROM EdSales
> GROUP BY county, YEAR(dateentered)

Well, I am a newbie at this sort of a thing. So, any pointers to
useful info on the diffiencies you mention are appreciated.

Query Assistance.

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.

Address ID Workstation Test-a 1 WS1 Test-b 2 WS1 Test-a 5 WS2 Test-d 3 WS2 Test-b 7 WS2

I am trying to write a query that will display this result into Excel.

Address Duplicate WS1 WS2 Test-a
Yes
1
5 Test-b
Yes
2
7 Test-d
No

Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

What are the possible values for the Workstation column? Is it always at most 2 workstations for any address?|||No, there are actually around 8-10 workstations.
|||

This should give you an idea about how to approach the solution.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Address varchar(20),
[ID] int,
Workstation varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'Test-a', 1, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 2, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-a', 5, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-d', 3, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 7, 'WS2' )

SELECT
Duplicate = CASE WHEN dt.ADDRESS IS NULL THEN 'No' ELSE 'Yes' END,
m.Address,
m.[ID],
m.Workstation
FROM @.MyTable m
LEFT JOIN (SELECT Address
FROM @.MyTable
GROUP BY
Address
HAVING count( Address ) >= 2
) dt
ON m.Address = dt.Address
ORDER BY
Address,
Workstation

Duplicate Address ID Workstation
-- -- --
Yes Test-a 1 WS1
Yes Test-a 5 WS2
Yes Test-b 2 WS1
Yes Test-b 7 WS2
No Test-d 3 WS2

|||I appreciate the information. I have been working with this to pull the data I need. I have modified the insert statement to pull the data from another table.

I am getting an error however:
Server: Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PointAddress'.

As I mentioned I am new to alot of this, how do I identify line 15? I have searched on the error itself and found references that I should be creating an alias for the "pointaddress" column.

I appreciate the help with this. Explanations will be helpful as well.

Code Snippet

SET NOCOUNT ON

DECLARE @.dup_trnd table
( RowID int IDENTITY,
PointAddress varchar(30),
TrendID int,
Workstation varchar(30)
)

INSERT dup_trnd
SELECT PointAddress,TrendID,Workstation from DuplicateTrends

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
m.PointAddress,
m.TrendId,
m.Workstation
FROM @.dup_trnd m
LEFT JOIN (SELECT PointAddress
FROM @.dup_trnd
GROUP BY
PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON m.PointAddress = dt.PointAddress
ORDER BY
PointAddress,
Workstation

|||

Try this:

Code Snippet

SET NOCOUNT ON

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
d.PointAddress,
d.TrendId,
d.Workstation
FROM Dup_Trnd d
LEFT JOIN (SELECT PointAddress
FROM Dup_Trnd
GROUP BY PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON d.PointAddress = dt.PointAddress
ORDER BY
d.PointAddress,
d.Workstation

|||Thank you for the assistance. This is mostly what I need and can work on it from here.