Help with Query

Thanks in advance for your help!!!!

I have equipment that I need to track performace after scheduled maintenance (PM) is completed. The information is entered by Equipment number for line and shift. There is a column that contians a numeric value for when a PM is completed (0 = no pm, 1 = pm). All PM is completed on 1st shift. I need to get performance for second shift when a PM is completed on first shift. For example.

Select Production/Potential as Performace, LineNum, Shift, EntryDate
Form tblDailyProduction
Where Shift = 2 AND [a PM has occured on 1st shift]
Group by LineNum, Shift, EntryDate

I just haven't been able to pull out shift 2 on the days that PM is = 1.

I tried to use
Select Production/Potential as Performace, LineNum, Shift, EntryDate
Form tblDailyProduction
Where EntryDate = (Select EntryDate From tblDailyProduction Where PM = 1)
but this did not work as the subquery returned multiple records error.

Here are the table columns:

Name Type
ProdID int
EntryDate Date/Time
LineNum int
Shift int
Dept Text
EquipType Text
ProductType Text
Potential Number (Long)
Production Number (Long)
PM Int
DateCode Text

Thanks,
LeeSelect tDP1.Production/tDP1.Potential as Performace
, tDP1.LineNum
, tDP1.Shift
, tDP1.EntryDate
Form tblDailyProduction tDP1
join tblDailyProduction tDP1 on tDP1.ProdID = tDP2.ProdID
and tDP1.LineNum = tDP2.LineNum
and tDP1.EntryDate = tDP2.EntryDate
Where tDP1.Shift = 2
and tDP2.Shift = 1
and tDP2.PM = 1

my assumptions are:
ProdID = EquipmentID
EntryDate does NOT have a time component.

post back if this is not true|||Yes, EntryDate does not have a time component.
No, ProdID is not the same as equipment number. It is an id for the record. LineNum is the identifier for each piece of equipment. Do I need to set LineNum = LineNum instead of ProdID = ProdID?

Thanks so much for the quick response,
Lee|||caught another mistake...

Select tDP1.Production/tDP1.Potential as Performace
, tDP1.LineNum
, tDP1.Shift
, tDP1.EntryDate
Form tblDailyProduction tDP1
join tblDailyProduction tDP2 on tDP1.LineNum = tDP2.LineNum
and tDP1.EntryDate = tDP2.EntryDate
Where tDP1.Shift = 2
and tDP2.Shift = 1
and tDP2.PM = 1

clear as mud?|||Yes, I made the change and it gave the results (once I change Form to From).

Now that you solved this problem, could I bother you to tell me exactly what is happening so that I can take this "knowledge" that you have so kindly shared and use it in other times of opportunity?

thanks again!!!!!!!!!!!!!
Lee|||Ah, set theory, it's wonderful stuff...

tDP1 gives you a list of all equipment that was used on shift 2.
tDP2 gives you a list of all equipment that had maintenance pulled on the first shift.

Take the intersection of the two lists, via Equipment ID and activity date, to get your answer.

Now don't ask me why I can't spell "FROM" but remember this kind of stuff after being out of school for 20 years!|||THANKS FOR THE POWER!!!

Help with Query

Hello and Thanks in advance,

I am trying to get the percentage that each row contributes to the total rows for a given time frame on a given line (Select ScrapCat, ScrapLbs/Sum(ScrapLbs ... Group by Category, LineNum) as percentage). We have 11 categories for each line for each day. The percentage for each category would be the sum of all rows for that category for that line that time frame divided the sum of all rows for all categories for that line and time frame.

A return would look like this
Category......ProLine.......ScrapLbs.....Sum( ScrapLbs)...Percentage...
CateA----1-----.25----2.0-----.125--
CateB----1-----.35----2.0-----.175--
CateC----1-----.5----2.0-----.25--
etc
CateA----2-----.25----1.0-----.25--
CateB----2-----.50----1.0-----.5--
etc
Table looks like this

ProDate ..............smalldatetime
ProLine ...............int
Category.............char
ScrapLbs ............float
ProShift ..............int

is this possible?
Thanks,
LeeCan you post the DDL and some sample base data

Like CREATE TABLE myTable99 (Col1 int, ..ect

And sample data that would put the data in to the table, like

INSERT INTO myTable99 (Col1, col2, ect)
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada

That way we can execute the code, set up a test bed and figure it out...

but this kinda throws me right away..

ScrapLbs.....Sum(ScrapLbs)...

How can you have the sum of something, and 1 occurance of something on the same row?|||Sorry my boss shifted my focus!

Hopefully this is what you need.

What I need to do is sum all the scrap for each line for the date range (Sum( lbs) as LineTotal group by line then sum(Category) as EachCategory group by line and Category then divide EachCategory by LineTotal

EachCategory/LineTotal = EachCategory is what percent of Total Line Scrap

Create Table tblScrap
{
thaDate smalldatetime
Category varchar 15
lbs float
LineNum int
Shift int
}
Insert tblScrap Values ( ' 10/29/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2004',DA , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/29/2005',DT , 0.5, 1 ,1)
Insert tblScrap Values ( ' 10/29/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2007',Longs , 3.4, 1 ,1)
Insert tblScrap Values ( ' 10/29/2008',Bent , 1.48, 1 ,1)
Insert tblScrap Values ( ' 10/29/2009',NTA , 4.44, 1 ,1)
Insert tblScrap Values ( ' 10/29/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2011',Caps , 2.36, 1 ,1)
Insert tblScrap Values ( ' 10/29/2012',Paper , 5.26, 1 ,1)
Insert tblScrap Values ( ' 10/29/2013',NAPS , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/28/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2004',DA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2005',DT , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2007',Longs , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2008',Bent , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2009',NTA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2011',Caps , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2012',Paper , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2013',NAPS , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2004',DA , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2005',DT , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2007',Longs , 7.16, 1 ,1)
Insert tblScrap Values ( ' 10/27/2008',Bent , 1.84, 1 ,1)
Insert tblScrap Values ( ' 10/27/2009',NTA , 2.24, 1 ,1)
Insert tblScrap Values ( ' 10/27/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2011',Caps , 3.92, 1 ,1)
Insert tblScrap Values ( ' 10/27/2012',Paper , 7.86, 1 ,1)
Insert tblScrap Values ( ' 10/27/2013',NAPS , 1.76, 1 ,1)|||I'm not sure I fully understood what you are looking for.
This query groups by date and category with the percentage for each category in relation to the total of the day.

SELECT thaDate,
Category,
SUM(lbs) ScrapLbs,
(SELECT NULLIF(SUM(lbs), 0) FROM tblScrap WHERE thaDate = TS.thaDate) SumScrapLbs,
SUM(lbs) / (SELECT NULLIF(SUM(lbs), 0) WHERE thaDate = TS.thaDate) Percentage
FROM tblScrap TS
GROUP BY thaDate, Category

Hope this helps.

Cheers,
Robert|||clinel,

Still not sure what you mean. sum(Category)? Category is a character field. Also, what date ranges?
This should get you started:

select LineCatTotals.LineNum, LineCatTotals.Category, LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select LineNum, sum(lbs) Linelbs from tblScrap group by LineNum) LineTotals
inner join (select LineNum, Category, sum(lbs) LineCatlbs from tblScrap group by LineNum, Category) LineCatTotals
on LineTotals.LineNum = LineCatTotals.LineNum

Add groupings by date or daterange if you want them.

blindman|||I'm sorry,
Category is the label for each category of scrap. I want to sum the lbs of scrap or each category would be a better term. Then I want to sum all lbs of scrap by line to get a line total and then divide the category total (for that line) by the line total to get the percent that each category contributes to the line total. Whew!

As far as date range goes, I will give the user the ability to give a beginning and ending date and I want to find the percentage for that date range.

My bad on the sum of category; I see now how I took a confusing thing and made it even more so.

Thanks for both the patience and help,
Lee|||Thanks to all,
It appears that what Blindman had sent me is what I needed. I was actually able to figure out where to set the critera for the date range. Now I just need to figure out what is going on because I have several reports that I think that this type query will fit the need.

Thanks again,
Lee|||If you'd like, post your final query and we can make sure you implemented the date-range criteria in the most efficient manner.

blindman|||As I am new to this and have had no formal and very little time to read very much, this is how I handled what you gave me.

I created a stored procedure (so I could set the critera for date range easily) and a I am allowing the user to set the begin and end and call it from an asp.

Here is how I handled the date range.

@.begdate smalldatetime, @.enddate smalldatetime
AS

select LineCatTotals.ProLine,
LineCatTotals.ScrapCat,
LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select ProLine, sum(Scraplbs) Linelbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine) LineTotals
inner join (select ProLine, ScrapCat, sum(Scraplbs) LineCatlbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine, ScrapCat) LineCatTotals
on LineTotals.ProLine = LineCatTotals.ProLine Order by LineCatTotals.ProLine
GO

Now that you are looking over this, is it possible to select a total from another table and divide the Line total (scraplbs) by the production total from another table (Select Sum(Production) From tblProduction Where EntryDate Between @.begdate AND @.enddate) LineCatTotals.LineCatlbs/Sum(Production) ? Both tables could be linked on ProLine.|||Looks good to me.

Yes, you can add more subqueries to do additional calculations. It is generally more efficient to run your process as a single query, but if the query gets too confusing then consider breaking it up into separate statements that load temporary tables or table variables with summarized data. Then finish with a query that links these temporary tables to get the answer you need.

blindman

Help With Query

I am new at this so your patience and help is appreciate in advance. I have
5
tables that are updated with call center stats. The common field across all
tables is the associate. Some of the tables are updated daily while others
may be updated once a w.
I want to sum all the stats by associate from these tables and using weighte
d
formulas report on their performance. I need to be able to provide the user
with reporting for a date range that will be entered through a Web page
(begin date / end date).
How do I go about doing this? Link all the tables by associate, do the sums
and then the formulas within the same query or run separate queries for each
table and then link results via associate? My main problem is being able to
pass the date range. I've done this in ACCESS with a form as the central
input for the date range, but don't know how in MS-SQL.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1If you post DDL we can give more specific advice. Please see this link for
details...
http://www.aspfaq.com/etiquette.asp?id=5006
As for the general process for doing this...
Start by joining the necessary tables together and making sure you are
retrieving the rows you want.
The add in the date criteria. Again, make sure these are the rows you want.
Then add in the summaries. Make sure these are the results you want.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6202cfc0a9dd4@.uwe...
> I am new at this so your patience and help is appreciate in advance. I
have 5
> tables that are updated with call center stats. The common field across
all
> tables is the associate. Some of the tables are updated daily while others
> may be updated once a w.
> I want to sum all the stats by associate from these tables and using
weighted
> formulas report on their performance. I need to be able to provide the
user
> with reporting for a date range that will be entered through a Web page
> (begin date / end date).
> How do I go about doing this? Link all the tables by associate, do the
sums
> and then the formulas within the same query or run separate queries for
each
> table and then link results via associate? My main problem is being able
to
> pass the date range. I've done this in ACCESS with a form as the central
> input for the date range, but don't know how in MS-SQL.
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1