SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query

Author

Topic

Harsh
Starting Member

5 Posts

Posted - 2005-12-05 : 10:18:56

Hi,

Im having problem with designing a query. The Tables are
1. C_O_M 2. G_M.

The table fiedls for c_O_M are :
PO_NO
Customer_ID
Date
Material_No
Quantity

The table fiels for G_M are :
Grn_No
Date
Material-No
Quantity

I would like to have sum(C_O_M.Quantity) and sum(G_M.Quantity)
I join it on material_no. In the existing Query I get result wrong because of permutations. The reason for this is it adds the rows of G_M and C_O_M in permutations. Any help would be appreciated.

Thanks,
Harsh

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-05 : 10:59:41

Harsh,

Read the hint link in my sig below and post what it ask for in the link...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Harsh
Starting Member

5 Posts

Posted - 2005-12-05 : 11:06:30

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 00:46:17

Post additional info as suggested

Madhivanan

Failing to plan is Planning to fail

Harsh
Starting Member

5 Posts

Posted - 2005-12-06 : 08:30:41

Thanks Madhivanan,

This is a bit complex query. I would like to use this query in crystal report. Two tables are joined in such a way that it should give the correct sum. The sum is for particular date for COM & GM.

The table fields for c_O_M are :
PO_NO
Customer_ID
Date
Material_No
Quantity

The table fiels for G_M are :
Grn_No
Date
Material-No
Quantity

above two tables will be joined on Material_No. The query should generate following result

COM.Date COM.Customer_Id COM.Material_No COM.Quantity GM.Date GM.Quantity

01/10/2005 1 10 100 NULL NULL
NULL NULL 10 NULL/0 01/10/2005 500
NULL NULL 10 NULL/0 02/10/2005 1500
10/10/2005 1 10 500 NULL NULL

I have tried this with two sub qureries for COM and GM and "Union all" them. I am close to it but I am doubtful whether it will run bugfree in crystal report. I have tried it but I dont know if I paste the query in crystal report, will it automatically take care of the relationships. Otherwise I have to process the query in visual basic code. Append the temp table and accessing it from the crystal report.That I don't want to do.But there must be a way for this.

Thanks,
Harsh.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 08:57:04

Cant you use this?

Select C.Date C.Customer_Id C.Material_No C.Quantity G.Date G.Quantity from
COM C inner join GM G on C.Material_no=G.Material_no

Madhivanan

Failing to plan is Planning to fail

Harsh
Starting Member

5 Posts

Posted - 2005-12-09 : 10:04:06

Thanks Madhivanan,
But this query will result in duplicate records of COM as GM has more recordsets for particular material_no. If I want to sum(C.Quantity) and sum(G.Quantity)in same query, it will give wrong results.Which is sum of dupicate records also.

Thanks,
Harsh.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-12-09 : 11:08:55

This should work for you.

[CODE]SELECT q1.Material_No,
q1.TotalQuantity AS COM_Quantity,
q2.TotalQuantity AS GM_Quantity,
FROM (
SELECT c.Material_No,
SUM(C.Quantity) AS TotalQuantity
FROM dbo.C_O_M
AS c
GROUP BY c.Material_No
)
AS q1
JOIN (
SELECT g.[Material-No],
SUM(g.Quantity) AS TotalQuantity
FROM dbo.G_M
AS g
GROUP BY g.[Material-No]
)
AS q2
ON q1.[Material_No]
= q2.[Material-No];[/CODE]

Harsh
Starting Member

5 Posts

Posted - 2005-12-10 : 10:04:39

Thanks,

I will try this and let you know.

Harsh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources