|
Table 1 (UNITS)
ScanID ScanCount OrderID SerialNo ScanStatus ProductionNo
1981 1294 2 147639NZ301774 SHIPPED 10007627289
There are 1300 SerialNo's for ProductionNo That will end with status 'SHIPPED'
Table 2 (Unit_Logs)
LogID SerialNo ProductionNo Old_Status New_Status
1 147639NZ301774 10007627289 PACKED SHIPPED
There will be 1300 records with status changes from PACKED to SHIPPED
Needed RESULT:
ProductionNo PACKED SHIPPED
10007627289 1300 1300
This is what I have tried:
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status
HAVING (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
I added 1 log entry to the table, but the query returns nothing.
Think might need sub query, but not sure where to go from here.
|
|
|
|
|
HAVING is only meant to be used with aggregate functions.
Try something similar to this:
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status
<edit>fixed layout</edit>"When did ignorance become a point of view" - Dilbert
modified on Wednesday, March 3, 2010 3:54 AM
|
|
|
|
|
SELECT UNITS.ProductionNo,
COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS
JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
