|
I am attempting to get a result set from a query and it is not how I expected. (Go figure!)
Tables Structure:
Production: Date, WOID (FK)
WorkOrder: WOID (PK), ProdID (FK)
Product: ProdID (PK), PCatID (FK)
Category: PCatID (PK), Category
FormTrans: Date, WOID (FK), Kgs
I want to return results in the following Table structure:
Date, CategoryA, CategoryC, CategoryC,..., CategoryN, Date Kgs, Ave, Running Total
I have tried the following:
DECLARE @Start DateTime,
@End DateTime
SET @Start = '01/01/2007'
SET @End = '12/31/2007'
Select Distinct(pr.[Date],
Case When pc.Category IN ('Mini','Maxi','OSCS')
Then SUM(ft.Kgs)
Else 0
End As 'DUCT',
Case When pc.Category IN ('DSPETG','DSPVC')
Then SUM(ft.Kgs)
ELSE 0
END AS 'DS",
SUM(ft.Kgs) As 'Kgs' -- Date Kgs
-- Average for distinct date required here?
-- Running Total for Dates here?
FROM Production pr
JOIN WorkOrder wo
ON pr.WOID = wo.WOID
JOIN Product p
ON wo.ProdID = p.ProdID
JOIN ProdCategory pc
ON p.PCatID = pc.PCatID
JOIN Formula f
ON wo.FormulaID = f.FormulaID
JOIN FormTrans ft
ON ft.FormulaID = f.FormulaID
WHERE pr.[Date] BETWEEN @Start AND @End
GROUP BY pr.[Date],pc.Category
ORDER BY pr.Date
1, When I run this I am getting data but a single line for each category calculation per date and not a single date row for each date with multiple results on the same row.
2, How to implement the average, and running total columns?
I hope I have explained myself clearly.  
Any help is most appreciated.
Andy.
I don't speak Idiot - please talk slowly and clearly
modified on Monday, February 25, 2008 1:29 AM
|
|
|
|
|
I don't have enough information to help you - I would need to understand the relationships between tables - however one hint I will give is that I notice that there are no right/left outer joins.
I have rarely seen queries with so many inner joins and none of them being left/right outer joins.
By using inner joins you are restricting your data set - are you sure you want to do this?
One suggestion:
Rewrite the query one join at a time - this way you can check at each join if the results are what you expect.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thanks Guy.
I will play around with the Join clauses - I am not too flash on Outer Joins (as you could probable tell)
By the way, All tables are related via Keys.
Ie: WorkOrder PK is WOID, Category PK is PCatID etc...
Where these are found in other tables, they are foreign keys.
Cheers
Andy
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Hi there,
I got two tables, lets say X and Y.
Table X has a column ID, and table Y has a column ID_TO_X and VALUE.
I have to make a select that returns the ID of each row in table X and another column named HOMOGENIC, that returns "YES" if for each ID_TO_X = ID has the same VALUE or "NO" if not.
for example
TABLE X TABLE Y QUERY
ID ID_TO_X VALUE ID HOMOGENIC
1 1 10 1 YES
2 1 10 2 NO
3 2 20 3 YES
2 30
3 40
I'm using oracle 9
Can someone help me??
Thanks a lot,
Caio
|
|
|
|
|
TABLE X
ID
1
2
3
2
3
TABLE Y
ID_TO_X | VALUE
1 | 10
1 | 10
2 | 20
2 | 30
3 | 40
QUERY
ID | HOMOGENIC
1 | YES
2 | NO
3 | YES
|
|
|
|
|
i need to declare variables dynamic , then this my code for make that .but i can't used
why????????????????????
Declare @TableName Char(100)
Set @TableName = 'Ar_PromotionDetails' --{Columns}PromotionId
Declare Cur_Sys_DataAdapterTable Cursor
For
Select Cln.name As [Columns] , Type.Name Types , Cln.Length ,
Cln.xprec , Cln.xscale
From sysobjects Obj
inner join syscolumns Cln
On Obj.Id = Cln.id
Inner join systypes Type
On Type.xtype = Cln.xtype
Where obj.id = object_Id(@TableName)
Declare @ColumnName char(120) , @TypeName char(120)
Declare @Length SmallInt
Declare @xprec Tinyint , @xscale Tinyint
Open Cur_Sys_DataAdapterTable
Fetch Next From Cur_Sys_DataAdapterTable
Into @ColumnName , @TypeName , @Length ,@xprec , @xscale
Declare @DeclareVariable NChar(300)
Set @DeclareVariable =N''
While @@fetch_status = 0
begin
if ( LOWER(Rtrim(@TypeName)) in ( 'datetime' , 'bit' , 'int' , 'bigint',
'smallint' , 'tinyint' , 'bigint' ,
'smalldatetime' , 'float') )
Set @DeclareVariable = Rtrim(@ColumnName) + space(5) + Rtrim(@TypeName)
else
if ( LOWER (Rtrim(@TypeName)) in ('numeric' , 'decimal' ) )
Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName) +
N'('+ Rtrim(@xprec)+ N',' + Rtrim(@xscale) +N')'
else
Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName)+N'(' + Rtrim(@Length) + N')'
Set @DeclareVariable = N'Declare @@' + @DeclareVariable + Char(13)
Exec sp_Executesql @DeclareVariable
print 'load ok...'
Fetch Next From Cur_Sys_DataAdapterTable
Into @ColumnName , @TypeName , @Length , @xprec , @xscale
End
Close Cur_Sys_DataAdapterTable
deallocate Cur_Sys_DataAdapterTable
Select @@PromotionId --when i use this command make error (Server: Msg 137, Level 15, State 2, Line 62
Must declare the variable '@@PromotionId'.)
how i can resolve this problem?
123
|
|
|
|
|
I Need to list all employees and their date of hire (HireDate) in the
format "ALLEN was hired on the 20TH of FEBRUARY."
Emp Table
EmpNo Ename Job Mgr HireDate
7369 SMITH CLERK 7902 12/17/1980
7499 ALLEN SALESMAN 7698 2/20/1981
7521 WARD SALESMAN 7698 2/22/1981
7566 JONES MANAGER 7839 4/2/1981
Month table
Month MonthName
1 January
2 February
3 March
4 April
ordinals table
DayNumber DayOrdinal
1 1st
2 2nd
3 3rd
4 4th
|
|
|
|
|
Have you tried to solve the problem yourself?
Or are you looking for someone to do your homework for you?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Looks pretty straightforward to me. What have you tried ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You are going to need to use some of the functions in Access to pull different parts from your query result. What have you tried so far?
"That's the problem with a spell checker. It only helps with bad spelling, not stupidity." - Rob Graham
|
|
|
|
|
Can someone please help show me how to combine 2 unrelated tables? for example
I have:
TABLE A with values
DESC
DM
ED
and TABLE B with values
DATE
01/07/2008
01/14/2008
02/18/2008
I want TABLE C to look like this
DESC -- DATE
DM -- 01/07/2008
DM -- 01/14/2008
DM -- 02/18/2008
ED -- 01/07/2008
ED -- 01/14/2008
ED -- 02/18/2008
Thanks..
|
|
|
|
|
I tried this and it worked..
Select desc, date
from TableA, TableB
|
|
|
|
|
SELECT * FROM tableA
CROSS JOIN tableb
|
|
|
|
|
I used the crystal report wizard to connect a Ms SQL Server. During the procedure the wizard requested for the username and password to the database.
However, when i run the application and want to pull up a report the page request for the database password.
What am i doing wrong?
JESA
|
|
|
|
|
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world.
My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country.
In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
|
|
|
|
|
What do you mean by 'lowest element' ? I'd look at a few sites like Amazon, those guys have solved the issue of a generic address, I am willing to bet.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design.
When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq.
In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq.
Perhaps I'm just getting too picky!
|
|
|
|
|
Not quite mate 
Postcodes are seperate to locales (thornton). If you grab the data from auspost you'll find that while similar, postcodes can span multiple locales and locales can span multiple postcodes.
|
|
|
|
|
Thanks - appreciate the info.
I had a look at the data and there are indeed many instances where there is the same locality name spanning different postcodes, however, when you look at the reasons for their existance, then each one is actually a different physical location. In most cases this seems to be semantic although technically a different location. Examples are mainly for a large organisation to sort mail eg to differentiate say general mail, competition entry mail and perhaps parcels which are delivered to (most likely) the same building.
I couldn't see any instance of a locale (if you include the delivery office and category + state) spanning multiple postcodes, although I have to admit I only scanned the data. I'll run some queries on it later and post the results, although I guess you have already done this at some point 
|
|
|
|
|
My answer to your question is that there is no really elegant schema as by creating database fields one generalizes and there will be specific instances that do not fit into that generalization.
Here is a schema I use and has worked well for years:
Name,
Address1,
Address2,
Address3,
Region,
ZIP,
Country
Because the schema is a generalization the work has to be done at the reporting level i.e. moving data up one line if a column contains a null value.
Also the data is only as good as the people typing it in - so if someone types a country into the region column there is not much you can do.
You always pass failure on the way to success.
|
|
|
|
|
Hello all,
I am a SQL newbie, so it's not obvious to me what I am doing wrong. Any help is very much appreciated. I am working in a stored procedure, and I want to run a T-SQL UPDATE statement if a certain field comes back as being NULL after doing a SELECT, or a different UPDATE otherwise.
What I have so far is:
ALTER PROCEDURE myProc
-- ...
32 IF @add_new_field = '1' --@add_new_field is a BIT parameter
33 BEGIN
34 IF (SELECT man.[notes] FROM [manhours] man WHERE man.[log_number] LIKE @log_number) IS NULL
35 UPDATE [manhours] SET [notes] = @notes WHERE [log_number] LIKE @log_number
36 ELSE
37 UPDATE [manhours] SET [notes] = [notes] || ' ' || @notes WHERE [log_number] LIKE @log_number
38 END
where the numbers on the left are line numbers. When I hit 'Execute' the following error occurs:
Msg 170, Level 15, State 1, Procedure uspSaveNotes, Line 34
Line 34: Incorrect syntax near '|'
I am a SQL newbie, so it's not obvious to me what I am doing wrong. Any help is very much appreciated.
Sincerely Yours,
Brian Hart
Department of Physics and Astronomy
University of California, Irvine
|
|
|
|
|
Brian Hart wrote: UPDATE [manhours] SET [notes] = [notes] || ' ' || @notes WHERE [log_number] LIKE @log_number
This syntax isn't valid in T-SQL. The OR operator in T-SQL is OR. Also, T-SQL doesn't have short-circuit logical operators like C-based languages.
I'm not sure what you are trying to do with this line of code. What are you trying to set [notes] to in your UPDATE statement?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
If you are trying to concatenate strings, you can use the + operator.
|
|
|
|
|
Hi! everyone.
I have created on stored procedure "sp_SaveListingImages".
This procedure is use for inserting data into the database table based on value passed by user from .asp page.
From my asp page I pass all value for this procedure.
But when the procedure is execute don't know why but it get null value for some field!!
and when I try to print that value in my form it shows me value, but null in procedure.
How should I solve this problem?
Please Help me!
Thank You.
|
|
|
|
|
Please post the code that causes the problem, how can we tell the problem without looking into the code?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
