|
Hi,
Thanks for the replies.
This query only seems to make a 1 to 1 join.
So if there is only one log entry it returns both SHIPPED and PACKED as 1 instead of 1294.
ProductionNo SHIPPED PACKED
10007627289 1 1
if i remove:
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
ProductionNo SHIPPED PACKED
10007627289 1294 1294
I tried this mess. Will try a few more options with this.
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED
FROM UNITS
WHERE UNITS.Scanstatus='SHIPPED' AND
(SELECT COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNIT_Logs WHERE UNIT_Logs.Old_Status='PACKED')
GROUP BY UNITS.ProductionNo
|
|
|
|
|
Try this one.
SELECT u.ProductionNo,
u.SHIPPED,
l.PACKED
FROM
(
SELECT ProductionNo,
COUNT(SerialNo) AS SHIPPED
FROM UNITS
WHERE ScanStatus = 'SHIPPED'
GROUP BY ProductionNo
) u
LEFT OUTER JOIN
(
SELECT ProductionNo,
COUNT(SerialNo) AS PACKED
FROM UNIT_Logs
WHERE Old_Status = 'PACKED'
GROUP BY ProductionNo
) l
ON l.ProductionNo = u.ProductionNo
[Fixed typo]modified on Wednesday, March 3, 2010 7:56 AM
|
|
|
|
|
Wow. did not even know you could do this.
I get this error though:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'l'.
I am going to break it down into components to make sure the parts work and then try and connect it back together again.
Thanks
|
|
|
|
|
Sorry found the problem. , before the FROM in the first bracket.
It works perfectly. Thanks for the help.
|
|
|
|
|
|
I am creating a dynamic pivot-table with web - sql-server 2008
When the pivot-table is created i want to rename columnname.
I am execute a stored procedure, sp_rename..... from the web-page.
Web-page - select variablesnames
Web-page - variablesname --> short columnname
SQl-server - create pivot table (dynamic sql, short columnnames - 4000 char limit)
Web-page/Sql-server - rename short columnnames to variablesnames
The problem is the permission to execute sp_rename .
How/where can I adjust permission for the the "webuser" in sql-server ?
Thanks in advance
Clas
|
|
|
|
|
| I am assuming that you need this table only for display on your form, in which case why don't you rename the columns in the UI! Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have 2 tables in my data base..
1. User_Group table
2. Function_Group table
Example:
Function_Group table :
Groupid Applicationid Functionid
LCUser1 LCDocMgmt LC01
LCUser2 LCDocMgmt LC02
LCUser1 LCDocMgmt LC03
LCUser3 LCDocMgmt LC04
LCUser3 LCDocMgmt LC05
LCUser2 LCDocMgmt LC06
LCUser2 LCDocMgmt LC08
LCUser1 LCDocMgmt LC09
User_Group table:
Userid Groupid
900410 LCUser1
900846 LCUser1
900411 LCUser3
Now i use the following query to retrieve the Groupid for a particular Userid :
Select ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'
The result that i get on executing the above query is:
groupid
LCUser1
LCUser1
LCUser1
Actually i Dont require repetition of records. If there is 2 distinct records i just want the 2 distinct records without repetition.. can any one plz help me..
Thanx in advance....
Regards,
Tash
|
|
|
|
|
<<tash18>> wrote: distinct records
You answered your own question.
Select DISTINCT ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'
|
|
|
|
|
Thanx alot... That was really a Dumb question from my side i knew that... Anyways a million thanx..
Regards,
Tash
|
|
|
|
|
No problem. I also don't think it was particularly dumb compared to the general par.
|
|
|
|
|
If I do something like:
Update Field1=something1, Field2=something2
Where Field3=123;
I always index Field3, but I'm not sure if I need to index Field1 and Field2 too?
Thanks!
|
|
|
|
|
AFAIK you're query is not selecting based on field1 or field2, so it would not benefit from indexing those fields. Future queries obviously may or may not benefit from indexing.
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Thank you. Just double checking.
|
|
|
|
|
The other argument is that if you indexed field1 & field2, you would require more data writes. Both the main table data would be updated and the index data would be updated. Even worse, because you update data that is indexed you could cause all kinds of index reorganization when the value of field1 changes from A to Z.
My 2 cents.
|
|
|
|
|
That makes sense. Good info, ty.
|
|
|
|
|
Hello,
I need to learn about SSIS (Microsoft SQL Server Integration Services) interface with .NET and stored procedures. I am looking for a book to learn from but also use as a reference book. I have Knight's 24-Hour Trainer but it is not exactly what I want.
Thanks
djj
|
|
|
|
|
Hello,
i need help for a stored procedure that allows me to concat string fields from a table column when they have the same field names.
i did it when the fields were int and it worked perfectly
code snippet.
SELECT X.FieldName,SUM(CAST(Y.Value AS float))
FROM L INNER JOIN
C ON L.Id = C.Id INNER JOIN
B ON L.Id = B.LocalityId INNER JOIN
FormInstances ON B.FormInstanceId = FormInstances.Id INNER JOIN
Y ON FormInstances.Id = Y.FormInstanceId INNER JOIN
X ON Y.FieldId = X.Id
WHERE (C.AId = @aid) AND (FormInstances.FormId = 6)
GROUP BY X.FieldName
this got me the sum of each field in Y based on X.fieldname.
now i want to do the same when the values in Y are not texts and not numbers. i tried conctenation(+) but was not successful with.
thanks in advance.
|
|
|
|
|
I do not understand what you want. However keep in mind that if you concatenate a null value the result will be null.
'John' + NULL + 'Doe' = NULL
Try using the ISNULL function.
Also you cannot mix types
'John' + 1.0
will give an error. You need to make all compatible types.
Good Luck
djj
|
|
|
|
|
Thanks so much for the reply.
1.all the data types are verified to be strings(text)
2. i want to concatenate the strings by grouping them on a fieldid.
when i pick an id i want to concat all values corresponding to the id.
(this is the structure.
i have table A which contains list of towns.
table B contains list of regions.
table C contains list of attributes of the towns
table D contains the values of the attributes
the task is to provide a summary for towns under a region. grouped
under the various attributes.
for Region A i have Attribute A: then the concatenated values for
all attributes A for each town.
thanks once again.
|
|
|
|
|
Do you really want to concatenate columns or just create a table?
-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
--or
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places.
I hope this helps,
djj
|
|
|
|
|
Hi! Is there an ORM that provides streaming support? What I mean by streaming is that the users can see the rows/objects being loaded one-by-one/partially (Think of SQL Management Studio when the query result is thousands of record). I can't implement paging since our data changes very fast.
Background:
- DB contains >250 million of data (but I will limit the result set to 10K max)
- Query can take minutes even hours
- Can't change DB architecture
Thanks
|
|
|
|
|
pebrian27 wrote: but I will limit the result set to 10K max
Why, how useful is 10k records in a UI, why not limit to 100 or 50 or something the user can work with.
pebrian27 wrote: Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
If the query takes hours the user has got bored and gone home. Change you design to data dump, output the data via a job/SSIS package and make it available in a shared folder. We extended SSRS to do just this sort of work, anything more than 1k rows is a lousy design for a UI and a report.
[edit] Checked your profile, web developer, so use an FTP/download scenario[/edit]Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Our users are also technical people and they need ~10k records.
Also, the users get to create the queries (ad-hoc queries). My current ORM is Nhibernate so they use HQL which is very similar to SQL.
Mycroft Holmes wrote: pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
Oops... Queries will only take hours if I don't do the 10k limit. Currently, querying 500 records takes 2 mins. Thats why I want streaming support so that if they get bored, they can press cancel but still get partial result.
Our systems will be upgraded after 8 months.
|
|
|
|
|
Nhibernate isn't the cause of the slow query and there are no joins in the queries
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
